golang基础-Postgresql-ORM框架github.com/go-pg/pg学习一(连接数据库、创建、删除表)

下载安装

通过go get -u github.com/go-pg/pg进行安装，如果安装过程中出现如下的错误

package golang.org/x/crypto/pbkdf2: unrecognized import path “golang.org/x/crypto/pbkdf2” (https fetch: Get
https://golang.org/x/crypto/pbkdf2?go-get=1: dial tcp 216.239.37.1:443: i/o timeout)

需要安装依赖，通过git clone https://github.com/golang/crypto进行安装即可

示例

如果对PostgreSQL不熟悉的话，可以参考我的这篇文章
golang基础-Postgresql使用、Go中使用PostgreSQL（github.com/lib/pq学习）

连接数据库

以下的demo参考，pg的GitHub，这里我只是代码的搬运工，并在某些例子上做整理、修改

https://github.com/go-pg/pg

学习orm框架首先要下载安装、前面已经提过，接下来我们就连接数据库，连接数据库
首先我在PostgreSQL数据库中已经创建好了go名字的数据库

go=# \c go
You are now connected to database "go" as user "zhiliao".
go=# 

我们先看下面的例子看是如何连接数据库的

const (
    host     = "localhost"
    port     = 5432
    user     = "wyfzhiliao"
    password = "wyfzhiliao"
    dbname   = "go"
)

func connet() *pg.DB{
    db:=pg.Connect(&pg.Options{
        User:user,
        Password:password,
        Database:dbname,
    })

    var n int
    _,err:=db.QueryOne(pg.Scan(&n),"SELECT 1")
    if err != nil{
        panic(err)
    }
    return db
}

例子很简单，就是需要opt *Options连接参数，用户、密码、数据库名字，返回&DB结构体指针，最后利用SELECT 1语句判断连接是否成功即可
然后运行就会输出如下结果：

db DB<Addr="localhost:5432">

创建数据库表

接下来就创建数据库表，这里需要orm的思路，所以先构造2个struct

type User struct {
    Id     int64
    Name   string
    Emails []string
}

func (u User) String() string {
    return fmt.Sprintf("User<%d %s %v>", u.Id, u.Name, u.Emails)
}
/**
故事
 */
type Story struct {
    Id       int64
    Title    string
    AuthorId int64
    Author   *User
}

func (s Story) String() string {
    return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}

然后就通过pg的映射关系为model创建数据库中的表

func CreateTabel(db *pg.DB)  error{

    fmt.Println(db)

    for _,model:= range []interface{}{&User{}, &Story{}}{
        err:=db.CreateTable(model,&orm.CreateTableOptions{
            IfNotExists:true,
            FKConstraints:true,
        })
        if err!= nil{
            return err
        }
    }
    return nil
}

代码就是循环[]interface{}{&User{}, &Story{}}，依次对model进行映射创建对应数据库表，这里我就选取了一个参数IfNotExists\FKConstraints，当然还有其他的Temp\Varchar等
测试效果如下：

go=# \d
No relations found.
go=# \d
                List of relations
 Schema |      Name      |   Type   |   Owner    
--------+----------------+----------+------------
 public | stories        | table    | wyfzhiliao
 public | stories_id_seq | sequence | wyfzhiliao
 public | users          | table    | wyfzhiliao
 public | users_id_seq   | sequence | wyfzhiliao
(4 rows)

go=# 

这样数据库中就生成了对应model的2张表
如下是2张表的结构：

go=# \d users;
                         Table "public.users"
 Column |  Type  |                     Modifiers                      
--------+--------+----------------------------------------------------
 id     | bigint | not null default nextval('users_id_seq'::regclass)
 name   | text   | 
 emails | jsonb  | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "stories" CONSTRAINT "stories_author_id_fkey" FOREIGN KEY (author_id) REFERENCES users(id)

go=# \d stories;
                          Table "public.stories"
  Column   |  Type  |                      Modifiers                       
-----------+--------+------------------------------------------------------
 id        | bigint | not null default nextval('stories_id_seq'::regclass)
 title     | text   | 
 author_id | bigint | 
Indexes:
    "stories_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "stories_author_id_fkey" FOREIGN KEY (author_id) REFERENCES users(id)

go=# 

删除表

我们还可以进行表的删除操作

func DeleTable(db *pg.DB) error{

    err:=db.DropTable(&User{},&orm.DropTableOptions{
        IfExists:true,
        Cascade :true,
    })

    return err
}

然后在查询数据库的变化，就发现只剩下story表

go=# \d
                List of relations
 Schema |      Name      |   Type   |   Owner    
--------+----------------+----------+------------
 public | stories        | table    | wyfzhiliao
 public | stories_id_seq | sequence | wyfzhiliao
(2 rows)

go=# 

另外注意下删除表的一些参数

type DropTableOptions struct {
    IfExists bool
    Cascade  bool
}

以上就是使用pg的一些初始化的工作，后续我会继续完善golang中的orm框架pg的一些增删改查操作

完整代码

接下来我列出来该demo的完整代码

package main

import (
    "fmt"
    "github.com/go-pg/pg"
    "github.com/go-pg/pg/orm"
    _ "github.com/lib/pq"
)

const (
    host     = "localhost"
    port     = 5432
    user     = "wyfzhiliao"
    password = "wyfzhiliao"
    dbname   = "go"
)

func connet() *pg.DB{
    db:=pg.Connect(&pg.Options{
        User:user,
        Password:password,
        Database:dbname,
    })

    var n int
    _,err:=db.QueryOne(pg.Scan(&n),"SELECT 1")
    if err != nil{
        panic(err)
    }
    return db
}

/**
用户
 */
type User struct {
    Id     int64
    Name   string
    Emails []string
}

func (u User) String() string {
    return fmt.Sprintf("User<%d %s %v>", u.Id, u.Name, u.Emails)
}
/**
故事
 */
type Story struct {
    Id       int64
    Title    string
    AuthorId int64
    Author   *User
}

func (s Story) String() string {
    return fmt.Sprintf("Story<%d %s %s>", s.Id, s.Title, s.Author)
}

func CreateTabel(db *pg.DB)  error{

    fmt.Println(db)

    for _,model:= range []interface{}{&User{}, &Story{}}{
        err:=db.CreateTable(model,&orm.CreateTableOptions{
            IfNotExists:true,
            FKConstraints:true,
        })
        if err!= nil{
            return err
        }
    }
    return nil
}


func DeleTable(db *pg.DB) error{

    err:=db.DropTable(&User{},&orm.DropTableOptions{
        IfExists:true,
        Cascade :true,
    })

    return err
}

func main()  {
    db:=connet()
    fmt.Println("db",db)
    err:=CreateTabel(db)


    if err!=nil{
        panic(err)
    }
    err = DeleTable(db)

    if err!=nil{
        panic(err)
    }

    err=db.Close()
    if err != nil{
        panic(err)
    }
}