PostgreSQL Support in Go
Internal
Overview
PostgreSQL can be accessed from Go either with the database/sql
library, which is part of the Standard Library, or with a specialist library like jackc/pgx
.
database/sql
provides a uniform SQL API but is quite generic since it has to be able to work uniformly with many different database platforms. It has to be configured with a database driver, like lib/pq
or the driver that comes with jackc/pgx
.
jackc/pgx
is a PostgreSQL specialist library that has its own API, and its own driver (which can be used with database/sql
).
This article shows how to connect to the database and then perform DDL and DML operations with both database/sql
(configured with a jackc/pgx
driver) and jackc/pgx
library.
Connect to Database
With database/sql API and a pgx Driver
database/sql
must be configured with a PostgreSQL driver via a blank import. This examples uses the jackc/pgx
driver.
import (
"database/sql"
"fmt"
_ "github.com/jackc/pgx/v5/stdlib"
)
role := "postgres"
password := "somepasswd"
host := "localhost"
port := 5432
database := "postgres"
connectionString := fmt.Sprintf("postgres://%s:%s@%s:%d/%s", role, password, host, port, database)
dbHandle, err := sql.Open("pgx", connectionString)
if err != nil {
panic(err)
}
defer func() {
if err := dbHandle.Close(); err != nil {
fmt.Printf("failed to close the connection\n")
}
}()
if err := dbHandle.Ping(); err != nil {
fmt.Printf("failed to ping the database: %v\n", err)
} else {
fmt.Printf("database OK\n")
}
sql.Open("pgx", ...)
returns a database handle, and it must be called with a driver name recognized by the driver library initialized with the blank import. In this case is "pgx". The implementation maintains a pool of zero or more underlying connections, and it is safe for concurrent use by multiple goroutines.
The example shows how to automatically close the connection on exit, and also how to ping the database to ensure the connection works.
With database/sql API and a lib/pq Driver
The code is quasi-identical, with three differences:
- the name of the blank import used to initialize the driver
- the name of the driver
sql.Open()
is invoked with ("postgres" instead of "pgx") - details of the connection string: the "lib/pg" driver attempts to connect with SSL enabled and if the database is not connected for SSL, it fails.
import (
...
_ "github.com/lib/pq" // different driver
)
...
connectionString := fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=disable", role, password, host, port, database) // different connection string parameters
dbHandle, err := sql.Open("postgres", connectionString) // different driver name
...
With jackc/pgx API
The API is a bit different when using the specialized library (pgx.Connect()
instead of the sql.Open()
, etc.), but the overall structure of the database connection sequence is identical:
import (
"context"
"fmt"
"github.com/jackc/pgx/v5"
)
role := "postgres"
password := "somepasswd"
host := "localhost"
port := 5432
database := "postgres"
connectionString := fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=disable", role, password, host, port, database)
ctx := context.Background()
connectionHandle, err := pgx.Connect(ctx, connectionString)
if err != nil {
panic(err)
}
defer func() {
if err := connectionHandle.Close(ctx); err != nil {
fmt.Printf("failed to close the connection\n")
}
}()
if err := connectionHandle.Ping(ctx); err != nil {
fmt.Printf("failed to ping the database: %v\n", err)
} else {
fmt.Printf("database OK\n")
}
DDL
Create a schema, create a table within the schema, drop the table and the schema.
With database/sql
_, err := dbHandle.Exec("CREATE SCHEMA IF NOT EXISTS someschema")
if err != nil { ... }
_, err = dbHandle.Exec("CREATE TABLE IF NOT EXISTS someschema.sometable (id integer PRIMARY KEY, name text)")
if err != nil { ... }
_, err = dbHandle.Exec("DROP TABLE someschema.sometable")
if err != nil { ... }
_, err = dbHandle.Exec("DROP SCHEMA someschema CASCADE")
if err != nil { ... }
With jackc/pgx
The API is quasi-indentical, the Exec()
function requires an additional context.Context
:
ctx := context.Background()
_, err := connectionHandle.Exec(ctx, "CREATE SCHEMA IF NOT EXISTS someschema")
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "CREATE TABLE IF NOT EXISTS someschema.sometable (id integer PRIMARY KEY, name text)")
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "DROP TABLE someschema.sometable")
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "DROP SCHEMA someschema CASCADE")
if err != nil { ... }
DML
Insert a row in a table, then update that row, then delete it:
With database/sql
_, err := dbHandle.Exec("INSERT INTO someschema.sometable (id, name) VALUES ($1, $2)", 1, "Alice")
if err != nil { ... }
_, err = dbHandle.Exec("UPDATE someschema.sometable SET name = $1 WHERE id = $2", "Bob", 1)
if err != nil { ... }
_, err = dbHandle.Exec("DELETE FROM someschema.sometable WHERE id = $1", 1)
if err != nil { ... }
Behavior on nil Positional Parameters
If a nil
value is provided for a positional parameter, NULL
will be inserted in the corresponding column, provided that NULL
values are allowed for that column.
With jackc/pgx
The API is quasi-indentical, the Exec()
function requires an additional context.Context
:
ctx := context.Background()
_, err := connectionHandle.Exec(ctx, "INSERT INTO someschema.sometable (id, name) VALUES ($1, $2)", 1, "Alice")
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "UPDATE someschema.sometable SET name = $1 WHERE id = $2", "Bob", 1)
if err != nil { ... }
_, err = connectionHandle.Exec(ctx, "DELETE FROM someschema.sometable WHERE id = $1", 1)
if err != nil { ... }
The API has the same behavior on nil
as in case of the database/sql
API.
Queries
With database/sql
If one single row is expected as result of the query, then QueryRow()
/QueryRowContext()
can be used. Otherwise Query()
/QueryContext()
can be used to retrieve multiple rows:
Single Row
row := dbHandle.QueryRow("SELECT id, name, city FROM someschema.sometable WHERE id = $1", 1)
var (
id int
name string
city sql.NullString
)
err := row.Scan(&id, &name, &city)
if err != nil { ... }
Multiple Rows
rows, err := dbHandle.Query("SELECT id, name, city FROM someschema.sometable WHERE city IS NOT NULL AND id <> $1", 1)
if err != nil { ... }
defer func() {
if err = rows.Close(); err != nil { ... }
}()
for rows.Next() {
var (
id int
name string
city sql.NullString
)
err = rows.Scan(&id, &name, &city)
if err != nil { ... }
...
}
Handling NULL Results
If a column value can be NULL
, providing the pointer to a string
to Scan()
can lead to:
Scan error on column index 2, name "city": converting NULL to string is unsupported
With jackc/pgx
TODO when needed