PostgreSQL Support in Go: Difference between revisions
Line 51: | Line 51: | ||
==With <tt>database/sql</tt> and a <tt>lib/pq</tt> Driver== | ==With <tt>database/sql</tt> and a <tt>lib/pq</tt> Driver== | ||
The code is quasi-identical, the only difference being the blank import used to initialize the driver, and the driver name <code>sql.Open()</code> is invoked with: | |||
<syntaxhighlight lang='go'> | |||
</syntaxhighlight> | |||
==With <tt>jackc/pgx</tt>== | ==With <tt>jackc/pgx</tt>== |
Revision as of 22:14, 30 May 2024
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 and a jackc/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\n")
}
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 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 and a lib/pq Driver
The code is quasi-identical, the only difference being the blank import used to initialize the driver, and the driver name sql.Open()
is invoked with:
With jackc/pgx
Create a Schema
With database/sql
With jackc/pgx
Create a Table
With database/sql
With jackc/pgx
Update the Table Schema
With database/sql
With jackc/pgx
Insert a Row
With database/sql
With jackc/pgx
Update a Row
With database/sql
With jackc/pgx
Query
With database/sql
With jackc/pgx
Delete a Row
With database/sql
With jackc/pgx
Delete a Table
With database/sql
With jackc/pgx
Database Operations
Open a Connection
import (
"database/sql"
_ "github.com/lib/pq"
)
...
role := "ovidiu"
password := "something"
databaseHost := "localhost"
databaseName := "testdb"
connectionString := fmt.Sprintf("postgres://%s:%s@%s/%s?sslmode=disable", role, password, databaseHost, databaseName)
db, err := sql.Open("postgres", connectionString)
if err != nil {
...
}
defer func() {
if err := db.Close(); err != nil {
...
}
}()
err = db.Ping()
if err != nil {
...
}
"postgres://<user>:<password>@<host>/<database-name>?...."
is called connection string.
sql.Open()
returns a database handle. The implementation maintains a pool of zero or more underlying connections, and it is safe for concurrent use by multiple goroutines.
Create a Schema
sql := `CREATE SCHEMA IF NOT EXISTS someschema`
_, err = db.Exec(sql)
if err != nil {
...
}
Create a Table
sql := `CREATE TABLE IF NOT EXISTS TEST ("ID" int, "NAME" varchar(10))`
_, err = db.Exec(sql)
if err != nil {
...
}
Delete a Table
sql := `DROP TABLE TEST`
_, err = db.Exec(sql)
if err != nil {
...
}
Insert a Row
s := `INSERT INTO TEST ("ID", "NAME") VALUES (10, 'Bob')`
_, err = db.Exec(s)
if err != nil {
...
}
Alternative:
var ctx context.Context ...
var id title description director string
s := `INSERT INTO movies ("id", "title", "description", "director") VALUES ($1, $2, $3, $4)`
_, err := r.db.ExecContext(ctx, s, id, title, description, director)
if err != nil {
...
}
Update a Row
sql := `UPDATE TEST SET "ID"=$1 WHERE "NAME"=$2`
_, err = db.Exec(sql, 20, "Bob")
if err != nil {
...
}
Delete a Row
sql := `DELETE FROM TEST WHERE "ID"=$1`
_, err = db.Exec(sql, 20)
if err != nil {
...
}
Query
s := `SELECT "ID", "NAME" FROM TEST`
rows, err := db.Query(s)
if err != nil {
...
}
defer rows.Close()
for rows.Next() {
var id int
var name string
err = rows.Scan(&id, &name)
if err != nil {
...
}
}
Other query methods:
s := `SELECT "value", "user_id" FROM ratings WHERE "record_id"=$1 AND "record_type"=$2`
rows, e := r.db.QueryContext(ctx, s, recordID, recordType)
defer func() {
if e := rows.Close(); e != nil {
...
}
}()
if e != nil {
...
}
for rows.Next() {
var rating int
var userID string
if e := rows.Scan(&rating, &userID); e != nil {
...
}
// do something with each result rating, userID
}
QueryRowContex()
is expected to return at most a row:
var title, description, director string
q := `SELECT "title", "description", "director" FROM movies WHERE "id"=$1`
row := r.db.QueryRowContext(ctx, q, id)
if err := row.Scan(&title, &description, &director); err != nil {
...
}