PostgreSQL Support in Go: Difference between revisions
Line 7: | Line 7: | ||
=Overview= | =Overview= | ||
PostgreSQL can be accessed from Go either with the <code>[[Go_Package_database#database/sql|database/sql]]</code> library, which is part of the Standard Library and provides a uniform SQL API, or with a specialist library like <code>[[Jackc/pgx#Overview|jackc/pgx]]</code>, that | PostgreSQL can be accessed from Go either with the <code>[[Go_Package_database#database/sql|database/sql]]</code> library, which is part of the Standard Library and provides a uniform SQL API, or with a specialist library like <code>[[Jackc/pgx#Overview|jackc/pgx]]</code>, that comes with its own database driver and API. <code></code> is quite generic since it has to be able to work uniformly with many different database platforms, and it has to be configured with a database driver, like <code>[https://github.com/lib/pq lib/pq]</code> or the driver that comes with <code>[[Jackc/pgx#Overview|jackc/pgx]]</code>. | ||
=Libraries= | =Libraries= |
Revision as of 21:44, 30 May 2024
Internal
Overview
PostgreSQL can be accessed from Go either with the database/sql
library, which is part of the Standard Library and provides a uniform SQL API, or with a specialist library like jackc/pgx
, that comes with its own database driver and API. is quite generic since it has to be able to work uniformly with many different database platforms, and it has to be configured with a database driver, like
lib/pq
or the driver that comes with jackc/pgx
.
Libraries
PostgreSQL can be accessed from Go either via the Standard Library database/sql
with a driver like lib/pq
, or with a specialist driver like 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 {
...
}