PostgreSQL(也称postgres)是一款强大的开源对象关系数据库系统(ORDBMS), 历经30年以上的打磨, 具有高可靠性, 强健壮性, 高性能等优点. 详见官网.

本文主要使用的github.com/lib/pq包, 它是一款为Go语言database/sql包(sql包是go定义的一套围绕SQL或类似SQL数据库的通用接口, 需要结合具体数据库的驱动一起使用)定制, 纯Go开发的PostgreSQL驱动.

什么是不写结构体?

本文中可以理解为, 查询数据库配置直接返回键值对类型, 或者查询数据返回多行数据, 不需要针对列值声明字段, 分别采用Map和Map数组来接收单行, 和多行数据. 详细原理请参考之前的博文:Golang连接MySQL执行查询并解析-告别结构体

接下来, 咱们一起来实现PostgreSQL Golang版本的增删改查(CRUD)与单行,多行字段快速扫描解析吧!

驱动安装

执行以下命令安装postgresql驱动

  1. go get github.com/lib/pq 

pq包支持的功能

  • SSL
  • 作为驱动程序, 与database/sql结合处理连接相关操作
  • 扫描时间time.Time类型, 如 timestamp[tz], time[tz], date
  • 扫描二进制blobs对象(Blob是内存中的数据缓冲, 用来匹配strign类型的ID和字节切片), 如: bytea
  • PostgreSQL的hstore数据类型支持
  • 支持COPY FROM
  • pq.ParseURL方法用来将urls转化为sql.Open的连接字符串
  • 支持许多libpq库兼容的环境变量
  • 支持Unix socket
  • 支持通知Notifications, 如:LISTEN/NOTIFY
  • 支持pgpass
  • GSS (Kerberos) 认证

连接字符串参数

pq包与libpq类似(libpq是用C编写的底层接口, 为其他高级语言比如C++,Perl,Python,Tcl和ECPG等提供底层PostgreSQL支持). 建立连接时需要提供连接参数, 一部分支持libpq的参数也支持pq, 额外的, pq允许在连接字符串中指定运行时参数(如:search_path或work_mem), libpq则不能在连接字符串中指定运行时参数, 只能在option参数中指定.

pq包为了兼容libpq包, 下面的连接参数都支持

  1. * dbname - 需要连接的数据库名 
  2. * user - 需要使用的用户名 
  3. * password - 该用户的密码 
  4. * host - 需要连接的postgresql主机, unix域名套接字以/开始, 默认是localhost 
  5. * port - postgresql绑定的端口 (默认5432) 
  6. * sslmode - 是否使用SSL (默认是启用(require), libpq包默认不启用SSL) 
  7. * fallback_application_name - 失败时,可以提供一个应用程序名来跟踪. 
  8. * connect_timeout - 连接最大等待秒数, 0或者不指定, 表示不确定时间的等待 
  9. * sslcert - 证书文件位置, 文件中必须包含PEM编码的数据 
  10. * sslkey - 密钥文件位置, 文件中必须包含PEM编码的数据 
  11. * sslrootcert - 根证书文件位置, 文件中必须包含PEM编码的数据 

sslmode 支持一下模式

  1. * disable - 禁用SSL 
  2. * require - 总是使用SSL(跳过验证) 
  3. * verify-ca - 总是使用SSL (验证服务器提供的证书是由可信的CA签署的) 
  4. * verify-full - 总是使用SSL(验证服务器提供的证书是由受信任的CA签署的，并验证服务器主机名是否与证书中的主机名匹配) 

更多连接字符串参数请参考官方文档

对包含空格的参数, 需要使用单引号, 如:

  1. "user=pqgotest password='with spaces'" 

使用反斜杠进行转义, 如:

  1. "user=space\ man password='it\'s valid'" 

注意: 如果要设置client_encoding连接参数(用于设置连接的编码), 必须设置为"UTF8", 才能与Postgres匹配, 设置为其他值将会报错.

除了上面的参数, 在连接字符串中也可以通过后台设置运行时参数, 详细运行时参数, 请参考runtime-config

支持libpq的大部分环境变量也支持pq包, 详细环境变量请参考libpq-envars. 如果没有设置环境变量且连接字符串也没有提供该参数, 程序会panic崩溃退出, 字符串参数优先级高于环境变量.

完整"增删改查"示例代码

  1. package main 
  2.  
  3. import ( 
  4.   "database/sql" 
  5.   "encoding/json" 
  6.   "fmt" 
  7.   _ "github.com/lib/pq" 
  8.   "log" 
  9. ) 
  10.  
  11. const ( 
  12.  
  13.   // Initialize connection constants. 
  14.   HOST     = "172.16.xx.xx" 
  15.   PORT     = 31976 
  16.   DATABASE = "postgres" 
  17.   USER     = "postgres" 
  18.   PASSWORD = "xxx" 
  19. ) 
  20.  
  21. func checkError(err error) { 
  22.   if err != nil { 
  23.     panic(err) 
  24.   } 
  25. } 
  26.  
  27. type Db struct { 
  28.   db *sql.DB 
  29. } 
  30.  
  31. // 创建表 
  32. func (this *Db) CreateTable() { 
  33.   // 以水果库存清单表inventory为例 
  34.   // Drop previous table of same name if one exists.  如果之前存在清单表, 则删除该表 
  35.   _, err := this.db.Exec("DROP TABLE IF EXISTS inventory;") 
  36.   checkError(err) 
  37.   fmt.Println("Finished dropping table (if existed)") 
  38.  
  39.   // Create table. 创建表, 指定id, name, quantity(数量)字段, 其中id为主键 
  40.   _, err = this.db.Exec("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);") 
  41.   checkError(err) 
  42.   fmt.Println("Finished creating table") 
  43. } 
  44.  
  45. // 删除表 
  46. func (this *Db) DropTable() { 
  47.   // 以水果库存清单表inventory为例 
  48.   // Drop previous table of same name if one exists.  如果之前存在清单表, 则删除该表 
  49.   _, err := this.db.Exec("DROP TABLE IF EXISTS inventory;") 
  50.   checkError(err) 
  51.   fmt.Println("Finished dropping table (if existed)") 
  52. } 
  53.  
  54. // 增加数据 
  55. func (this *Db) Insert() { 
  56.   // Insert some data into table. 插入3条水果数据 
  57.   sql_statement := "INSERT INTO inventory (name, quantity) VALUES ($1, $2);" 
  58.   _, err := this.db.Exec(sql_statement, "banana", 150) 
  59.   checkError(err) 
  60.   _, err = this.db.Exec(sql_statement, "orange", 154) 
  61.   checkError(err) 
  62.   _, err = this.db.Exec(sql_statement, "apple", 100) 
  63.   checkError(err) 
  64.   fmt.Println("Inserted 3 rows of data") 
  65. } 
  66.  
  67. // 读数据/查数据 
  68. func (this *Db) Read() { 
  69.   //读取数据 
  70.   // Read rows from table. 
  71.   var id int 
  72.   var name string 
  73.   var quantity int 
  74.  
  75.   sql_statement := "SELECT * from inventory;" 
  76.   rows, err := this.db.Query(sql_statement) 
  77.   checkError(err) 
  78.   defer rows.Close() 
  79.  
  80.   for rows.Next() { 
  81.     switch err := rows.Scan(&id, &name, &quantity); err { 
  82.     case sql.ErrNoRows: 
  83.       fmt.Println("No rows were returned") 
  84.     case nil: 
  85.       fmt.Printf("Data row = (%d, %s, %d)\n", id, name, quantity) 
  86.     default: 
  87.       checkError(err) 
  88.     } 
  89.   } 
  90. } 
  91.  
  92.  
  93. // 更新数据 
  94. func (this *Db) Update() { 
  95.   // Modify some data in table. 
  96.   sql_statement := "UPDATE inventory SET quantity = $2 WHERE name = $1;" 
  97.   _, err := this.db.Exec(sql_statement, "banana", 200) 
  98.   checkError(err) 
  99.   fmt.Println("Updated 1 row of data") 
  100. } 
  101.  
  102. // 删除数据 
  103. func (this *Db) Delete() { 
  104.   // Delete some data from table. 
  105.   sql_statement := "DELETE FROM inventory WHERE name = $1;" 
  106.   _, err := this.db.Exec(sql_statement, "orange") 
  107.   checkError(err) 
  108.   fmt.Println("Deleted 1 row of data") 
  109. } 
  110.  
  111. // 数据序列化为Json字符串, 便于人工查看 
  112. func Data2Json(anyData interface{}) string { 
  113.   JsonByte, err := json.Marshal(anyData) 
  114.   if err != nil { 
  115.     log.Printf("数据序列化为json出错:\n%s\n", err.Error()) 
  116.     return "" 
  117.   } 
  118.   return string(JsonByte) 
  119. } 
  120.  
  121.  
  122. //多行数据解析 
  123. func QueryAndParseRows(Db *sql.DB, queryStr string) []map[string]string { 
  124.   rows, err := Db.Query(queryStr) 
  125.   defer rows.Close() 
  126.   if err != nil { 
  127.     log.Printf("查询出错:\nSQL:\n%s, 错误详情\n", queryStr, err.Error()) 
  128.     return nil 
  129.   } 
  130.   cols, _ := rows.Columns() //列名 
  131.   if len(cols) > 0 { 
  132.     var ret []map[string]string //定义返回的映射切片变量ret 
  133.     for rows.Next() { 
  134.       buff := make([]interface{}, len(cols)) 
  135.       data := make([][]byte, len(cols)) //数据库中的NULL值可以扫描到字节中 
  136.       for i, _ := range buff { 
  137.         buff[i] = &data[i] 
  138.       } 
  139.       rows.Scan(buff...) //扫描到buff接口中，实际是字符串类型data中 
  140.  
  141.       //将每一行数据存放到数组中 
  142.       dataKv := make(map[string]string, len(cols)) 
  143.       for k, col := range data { //k是index，col是对应的值 
  144.         //fmt.Printf("%30s:\t%s\n", cols[k], col) 
  145.         dataKv[cols[k]] = string(col) 
  146.       } 
  147.       ret = append(ret, dataKv) 
  148.     } 
  149.     log.Printf("返回多元素数组:\n%s", Data2Json(ret)) 
  150.     return ret 
  151.   } else { 
  152.     return nil 
  153.   } 
  154. } 
  155.  
  156. //单行数据解析 查询数据库，解析查询结果，支持动态行数解析 
  157. func QueryAndParse(Db *sql.DB, queryStr string) map[string]string { 
  158.   rows, err := Db.Query(queryStr) 
  159.   defer rows.Close() 
  160.  
  161.   if err != nil { 
  162.     log.Printf("查询出错:\nSQL:\n%s, 错误详情\n", queryStr, err.Error()) 
  163.     return nil 
  164.   } 
  165.   //rows, _ := Db.Query("SHOW VARIABLES LIKE '%data%'") 
  166.  
  167.   cols, _ := rows.Columns() 
  168.   if len(cols) > 0 { 
  169.     buff := make([]interface{}, len(cols)) // 临时slice 
  170.     data := make([][]byte, len(cols))      // 存数据slice 
  171.     dataKv := make(map[string]string, len(cols)) 
  172.     for i, _ := range buff { 
  173.       buff[i] = &data[i] 
  174.     } 
  175.  
  176.     for rows.Next() { 
  177.       rows.Scan(buff...) // ...是必须的 
  178.     } 
  179.  
  180.     for k, col := range data { 
  181.       dataKv[cols[k]] = string(col) 
  182.       //fmt.Printf("%30s:\t%s\n", cols[k], col) 
  183.     } 
  184.     log.Printf("返回单行数据Map:\n%s", Data2Json(dataKv)) 
  185.     return dataKv 
  186.   } else { 
  187.     return nil 
  188.   } 
  189. } 
  190.  
  191.  
  192. func main() { 
  193.   // Initialize connection string. 初始化连接字符串, 参数包含主机,端口,用户名,密码,数据库名,SSL模式(禁用),超时时间 
  194.   var connectionString string = fmt.Sprintf("host=%s  port=%d user=%s password=%s dbname=%s sslmode=disable connect_timeout=3", HOST, PORT, USER, PASSWORD, DATABASE) 
  195.  
  196.   // Initialize connection object. 初始化连接对象, 驱动名为postgres 
  197.   db, err := sql.Open("postgres", connectionString) 
  198.   defer db.Close() 
  199.   checkError(err) 
  200.   postgresDb := Db{ 
  201.     db: db, 
  202.   } 
  203.   err = postgresDb.db.Ping() //连通性检查 
  204.   checkError(err) 
  205.   fmt.Println("Successfully created connection to database") 
  206.  
  207.   postgresDb.CreateTable()         //创建表 
  208.   postgresDb.Insert()              //插入数据 
  209.   postgresDb.Read()                //查询数据 
  210.   QueryAndParseRows(postgresDb.db, "SELECT * from inventory;") //直接查询和解析多行数据 
  211.   QueryAndParse(postgresDb.db, "SHOW DateStyle;") //直接查询和解析单行数据 
  212.   postgresDb.Update()              //修改/更新数据 
  213.   postgresDb.Read() 
  214.   postgresDb.Delete()              //删除数据 
  215.   postgresDb.Read() 
  216.   postgresDb.DropTable() 
  217. } 

执行 go run main.go运行结果如下:

  1. Successfully created connection to database 
  2. Finished dropping table (if existed) 
  3. Finished creating table 
  4. Inserted 3 rows of data 
  5. Data row = (1, banana, 150) 
  6. Data row = (2, orange, 154) 
  7. Data row = (3, apple, 100) 
  8. 2020/12/15 22:13:33 返回多元素数组: 
  9. [{"id":"1","name":"banana","quantity":"150"},{"id":"2","name":"orange","quantity":"154"},{"id":"3","name":"apple","quantity":"100"}] 
  10. 2020/12/15 22:13:33 返回单行数据Map: 
  11. {"DateStyle":"ISO, MDY"} 
  12. Updated 1 row of data 
  13. Data row = (2, orange, 154) 
  14. Data row = (3, apple, 100) 
  15. Data row = (1, banana, 200) 
  16. Deleted 1 row of data 
  17. Data row = (3, apple, 100) 
  18. Data row = (1, banana, 200) 
  19. Finished dropping table (if existed) 

总结

本文对pq驱动包以及连接字符串参数进行了介绍

示例代码分别将连接/创建表格/增加行数据/更新行数据/删除行数据封装为不同的方法, 便于灵活使用

查询单行或多行数据时, 可以直接使用封装好的方法, 直接传入Db指针和查询语句即可

参考文档

https://docs.microsoft.com/en-us/azure/postgresql/connect-go

https://pkg.go.dev/github.com/lib/pq

https://www.postgresql.org/

https://www.postgresql.org/docs/current/libpq.html