PostgreSQL

Use github.com/netlifeguru/db-postgres when your application connects to PostgreSQL or a PostgreSQL-compatible database.

The PostgreSQL driver implements the shared db.Conn interface, so once the connection is created, the rest of your application can use the common db APIs such as List, Get, Value, Maps, Insert, Update, and Delete.

Install

Install the PostgreSQL driver:

go get github.com/netlifeguru/db-postgres

Installing the driver also installs:

github.com/netlifeguru/db
github.com/netlifeguru/mapper

Import

import (
	"github.com/netlifeguru/db"
	"github.com/netlifeguru/db-postgres"
)

Connection Example

This example creates a PostgreSQL connection pool from environment variables.

package main

import (
	"os"
	"strconv"
	"time"

	"github.com/netlifeguru/db"
	"github.com/netlifeguru/db-postgres"
)

func connectDB() (db.Conn, error) {
	conn := postgres.New()

	cfg := db.Config{
		Identifier: "default",

		Host:     os.Getenv("DB_HOST"),
		Database: os.Getenv("DB_NAME"),
		Username: os.Getenv("DB_USER"),
		Password: os.Getenv("DB_PASSWORD"),

		MaxConns:          50,
		MinConns:          5,
		MaxConnIdleTime:   10 * time.Minute,
		MaxConnLifetime:   2 * time.Hour,
		HealthCheckPeriod: 30 * time.Second,
		ConnectTimeout:    10 * time.Second,

		SSLMode:  "disable",
		TimeZone: "UTC",
	}

	if port := os.Getenv("DB_PORT"); port != "" {
		n, err := strconv.Atoi(port)
		if err != nil {
			return nil, err
		}

		cfg.Port = n
	}

	if err := conn.CreatePool(cfg); err != nil {
		return nil, err
	}

	return conn.Fork(), nil
}

Environment Variables

The example above expects these environment variables:

DB_HOST=127.0.0.1
DB_PORT=5432
DB_NAME=app
DB_USER=postgres
DB_PASSWORD=secret

DB_PORT is optional in the example. If it is not provided, the driver uses its default PostgreSQL port.

PostgreSQL Defaults

If values are not provided, the PostgreSQL driver applies sensible defaults internally.

Placeholder Style

PostgreSQL uses numbered placeholders.

users, err := db.List[User](ctx, conn, `
	SELECT * FROM users
	WHERE active = $1
	ORDER BY created_at DESC
`, true)

The shared db helpers pass the arguments to the driver in order.

Insert Behavior

PostgreSQL commonly uses RETURNING when you need a generated ID or another value from an insert.

const insertUserQuery = `
	INSERT INTO users (name, email, active)
	VALUES ($1, $2, $3)
	RETURNING id
`

id, found, err := db.Value[int64](ctx, conn, insertUserQuery, name, email, active)
if err != nil {
	return err
}

if !found {
	return errors.New("insert did not return id")
}

fmt.Println(id)

You can also use db.Insert when you only need the execution result.

result, err := db.Insert(ctx, conn, `
	INSERT INTO audit_logs (message)
	VALUES ($1)
`, message)

if err != nil {
	return err
}

fmt.Println(result.RowsAffected())

Transactions

The PostgreSQL driver supports transactions through the shared transaction API.

Transaction usage is documented in the Transactions guide.

SQL Files

When using SQL model files, PostgreSQL uses:

model.psql

The shared db.LoadModel helper can load PostgreSQL SQL sections from this file and store them in db.DialectSQL values.

SQL files are documented in the SQL Files guide.

CockroachDB Note

PostgreSQL-compatible systems can often use the PostgreSQL driver, depending on compatibility and connection settings.

For CockroachDB-style connections, SSL requirements may differ from local PostgreSQL development setups.

Next Step

After creating a PostgreSQL connection, continue with the shared querying guides:

• List, Get, Value, and Maps
• Query Objects
• Dialect SQL
• Mutations