PostgreSQL

The PostgreSQL driver is Grove's primary driver, built on pgx for high-performance connection pooling and native protocol support.

Installation

go get github.com/xraph/grove
go get github.com/jackc/pgx/v5

Connection

import (
    "github.com/xraph/grove"
    "github.com/xraph/grove/driver"
    "github.com/xraph/grove/drivers/pgdriver"
)

// 1. Create and open the driver
pgdb := pgdriver.New()
pgdb.Open(ctx, "postgres://user:pass@localhost:5432/mydb?sslmode=disable", driver.WithPoolSize(20))

// 2. Pass the connected driver to Grove
db, err := grove.Open(pgdb)
if err != nil {
    log.Fatal(err)
}
defer db.Close()

Native Syntax

The PostgreSQL driver supports the full range of PostgreSQL-specific syntax:

Placeholders

PostgreSQL uses $1, $2, etc. for parameterized queries:

db.NewSelect(&users).
    Where("email ILIKE $1", "%@example.com").
    Where("created_at > $2", time.Now().AddDate(0, -1, 0)).
    Scan(ctx)

DISTINCT ON

db.NewSelect(&users).
    DistinctOn("department").
    OrderExpr("department, created_at DESC").
    Scan(ctx)

JSONB Operators

db.NewSelect(&users).
    Where("metadata->>'role' = $1", "admin").
    Where("metadata @> $1::jsonb", `{"active": true}`).
    Scan(ctx)

db.NewSelect(&account).
    Where("id = $1", accountID).
    ForUpdate().
    Scan(ctx)

ON CONFLICT (Upsert)

db.NewInsert(&user).
    OnConflict("(email) DO UPDATE").
    Set("name = EXCLUDED.name").
    Set("updated_at = NOW()").
    Returning("*").
    Exec(ctx)

RETURNING

var insertedUser User
db.NewInsert(&user).
    Returning("*").
    Scan(ctx, &insertedUser)

LISTEN / NOTIFY

pgdb := pgdriver.Unwrap(db)

// Create a listener and register a callback
listener := pgdb.NewListener()
listener.OnNotification("events", func(n *pgdriver.Notification) {
    fmt.Printf("channel=%s payload=%s\n", n.Channel, n.Payload)
})

// Start listening and subscribe to the channel
listener.Start(ctx)
listener.Listen(ctx, "events")

// ... later, when done:
listener.Close()

Custom Types

Type	Go Type	PostgreSQL Type
`pgdriver.JSONMap`	`map[string]any`	`jsonb`
`pgdriver.StringArray`	`[]string`	`text[]`
`pgdriver.Int64Array`	`[]int64`	`bigint[]`

Connection Pool Options

pgdb := pgdriver.New()
pgdb.Open(ctx, dsn,
    driver.WithPoolSize(20),
    driver.WithQueryTimeout(30 * time.Second),
)
db, err := grove.Open(pgdb)

Compatible Databases

CockroachDB

CockroachDB is PostgreSQL wire-compatible. The pgdriver works with CockroachDB out of the box with a few caveats:

pgdb := pgdriver.New()
err := pgdb.Open(ctx, "postgresql://root@localhost:26257/mydb?sslmode=disable",
    driver.WithPoolSize(20),
)

Known differences:

LISTEN/NOTIFY is not supported -- CockroachDB does not implement PostgreSQL's pub/sub system
DISTINCT ON works but may behave differently with distributed query execution
SERIAL columns should be replaced with UUID or INT DEFAULT unique_rowid() for distributed ID generation
Advisory locks (pg_advisory_lock) are not supported -- use CockroachDB's built-in locking

Neon

Neon is serverless PostgreSQL. The pgdriver works directly with Neon:

pgdb := pgdriver.New()
err := pgdb.Open(ctx, "postgresql://user:pass@ep-example-123.us-east-2.aws.neon.tech/mydb?sslmode=require",
    driver.WithPoolSize(5), // Keep pool size small for serverless
)

Tips for Neon:

Use sslmode=require in the DSN
Keep connection pool size small (5-10) since Neon uses a connection pooler (pgbouncer)
Cold starts may add latency on the first connection -- consider using Neon's connection pooler endpoint
All PostgreSQL features (JSONB, LISTEN/NOTIFY, CTEs, etc.) are fully supported

Supabase

Supabase provides hosted PostgreSQL. The pgdriver connects directly:

pgdb := pgdriver.New()
err := pgdb.Open(ctx, "postgresql://postgres.[project-ref]:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres",
    driver.WithPoolSize(10),
)

Tips for Supabase:

Use the pooler connection string (port 6543) for application connections
Use the direct connection string (port 5432) for migrations
All PostgreSQL features are supported, including RLS (Row Level Security)

PostgreSQL

On this page