PostgreSQL Support in Go: Difference between revisions
Line 17: | Line 17: | ||
=Connect to Database= | =Connect to Database= | ||
==With <tt>database/sql</tt> and a <tt>jackc/pgx</tt> Driver== | ==With <tt>database/sql</tt> and a <tt>jackc/pgx</tt> Driver== | ||
<code>database/sql</code> must be configured with a PostgreSQL driver via a [[Go_Packages#Blank_Imports|blank import]]. This examples uses the <code>jackc/pgx</code> driver. | |||
<syntaxhighlight lang='go'> | <syntaxhighlight lang='go'> | ||
import ( | |||
"database/sql" | |||
"fmt" | |||
_ "github.com/jackc/pgx/v5/stdlib" | |||
) | |||
role := "postgres" | |||
password := "somepasswd" | |||
host := "localhost" | |||
database := "postgres" | |||
connectionString := fmt.Sprintf("postgres://%s:%s@%s:5432/%s", role, password, host, 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") | |||
</syntaxhighlight> | </syntaxhighlight> | ||
Revision as of 22:10, 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"
database := "postgres"
connectionString := fmt.Sprintf("postgres://%s:%s@%s:5432/%s", role, password, host, 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")
With database/sql and a lib/pq Driver
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 {
...
}