GORM

GORM connects to DB9 using the pgx v5 driver via gorm.io/driver/postgres. Struct-based models, AutoMigrate, the query builder, and transactions work out of the box.

DB9’s E2E smoke tests validate CRUD, transactions, savepoints, joins, subqueries, window functions, DDL, and vector operations through GORM.

Prerequisites

• A DB9 database (create one)
• Go 1.22+
• GORM 1.25+

Create a DB9 Database

Terminal

db9 create --name gorm-app

db9 create prints the connection string, with the admin password, once — copy it from the Connection String: line. It has this shape:

Output

postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.d0000.db9.io:5432/postgres

YOUR_TENANT_ID is the tenant ID prefix in the generated connection string. Copy the username from the Connection String: line printed by db9 create or from db9 db connect. The examples below use pg.db9.io:5433, which reaches the same server; either host and port works. db9 db status gorm-app shows the connection string without the password; if you lose it, db9 db reset-password gorm-app sets a new password and prints a new connection string.

Project Setup

Terminal

mkdir gorm-db9 && cd gorm-db9
go mod init gorm-db9
go get gorm.io/gorm gorm.io/driver/postgres

Connection

main.go

package main

import (
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
    "log"
)

func main() {
    dsn := "postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.db9.io:5433/postgres?sslmode=require"

    db, err := gorm.Open(
        postgres.New(postgres.Config{DSN: dsn}),
        &gorm.Config{
            Logger: logger.Default.LogMode(logger.Silent),
        },
    )
    if err != nil {
        log.Fatalf("failed to connect: %v", err)
    }

    // Verify connection
    sqlDB, _ := db.DB()
    if err := sqlDB.Ping(); err != nil {
        log.Fatalf("ping failed: %v", err)
    }
    log.Println("Connected to DB9")
}

Define Models

Use Go structs with GORM tags:

models.go

package main

import "time"

type User struct {
    ID        uint           `gorm:"primaryKey"`
    Email     string         `gorm:"not null;uniqueIndex;size:255"`
    Name      string         `gorm:"not null;size:100"`
    Age       int            `gorm:"default:0"`
    IsActive  bool           `gorm:"default:true"`
    Bio       *string        `gorm:"type:text"`
    Metadata  map[string]any `gorm:"serializer:json"`
    CreatedAt time.Time
    UpdatedAt time.Time
    Posts     []Post         `gorm:"foreignKey:AuthorID"`
}

type Post struct {
    ID        uint      `gorm:"primaryKey"`
    Title     string    `gorm:"not null;size:500"`
    Content   string    `gorm:"type:text;not null"`
    Published bool      `gorm:"default:false"`
    AuthorID  uint      `gorm:"not null"`
    Author    User      `gorm:"foreignKey:AuthorID"`
    CreatedAt time.Time
}

GORM features used:

• primaryKey — auto-increment primary key
• uniqueIndex — creates a unique index
• serializer:json — automatically encodes/decodes map[string]any as JSON. AutoMigrate creates the column as TEXT; write gorm:"type:jsonb;serializer:json" to get a JSONB column you can query with ->> and @>
• CreatedAt / UpdatedAt — auto-managed by GORM

Create Tables

Use AutoMigrate to create tables from your model definitions:

Go

if err := db.AutoMigrate(&User{}, &Post{}); err != nil {
    log.Fatalf("AutoMigrate: %v", err)
}

For production, use raw SQL for more control over schema changes:

Go

db.Exec(`ALTER TABLE users ADD COLUMN IF NOT EXISTS phone TEXT`)
db.Exec(`CREATE INDEX IF NOT EXISTS idx_users_email ON users (email)`)

CRUD Operations

Go

// Create
user := User{
    Email:    "alice@example.com",
    Name:     "Alice",
    Age:      30,
    Metadata: map[string]any{"role": "admin"},
}
if err := db.Create(&user).Error; err != nil {
    log.Fatalf("create: %v", err)
}
// user.ID is now populated

// Read by primary key
var found User
db.First(&found, user.ID)

// Read with condition
var alice User
db.Where("email = ?", "alice@example.com").First(&alice)

// Read with multiple conditions
var active []User
db.Where("age >= ? AND is_active = ?", 25, true).
    Order("name ASC").
    Limit(10).
    Find(&active)

// Update
db.Model(&user).Update("name", "Alice Updated")

// Update multiple fields
db.Model(&user).Updates(map[string]any{
    "name": "Alice V2",
    "age":  31,
})

// Delete
db.Delete(&user)

Transactions

Transaction block (auto-commit/rollback)

Go

err := db.Transaction(func(tx *gorm.DB) error {
    user := User{Email: "txn@example.com", Name: "Txn User", Age: 25}
    if err := tx.Create(&user).Error; err != nil {
        return err
    }

    post := Post{Title: "Atomic Post", Content: "Created in a transaction.", AuthorID: user.ID}
    if err := tx.Create(&post).Error; err != nil {
        return err
    }

    return nil // auto-commits
    // return errors.New("...") would auto-rollback
})

Manual control

Go

tx := db.Begin()

if err := tx.Create(&User{Email: "manual@example.com", Name: "Manual"}).Error; err != nil {
    tx.Rollback()
    log.Fatal(err)
}

tx.Commit()

Nested transactions (savepoints)

GORM automatically uses savepoints for nested Transaction calls:

Go

db.Transaction(func(tx *gorm.DB) error {
    tx.Create(&User{Email: "outer@example.com", Name: "Outer"})

    // Creates a savepoint internally
    tx.Transaction(func(nested *gorm.DB) error {
        nested.Exec("UPDATE users SET age = age + 1 WHERE email = 'outer@example.com'")
        return nil
    })

    return nil
})

Isolation levels

Go

tx := db.Begin()
tx.Exec("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
// ... queries run at the requested isolation level
tx.Commit()

Supported: READ COMMITTED, REPEATABLE READ. SERIALIZABLE is not implemented — it does not error, it is silently downgraded to REPEATABLE READ with a server WARNING. Set REPEATABLE READ explicitly so the intent is visible in your code.

Raw SQL

Use db.Raw for SELECT queries and db.Exec for statements:

Go

// Raw SELECT
var count int64
db.Raw("SELECT COUNT(*) FROM users WHERE age > ?", 25).Scan(&count)

// Scan into struct
var results []struct {
    Name  string `gorm:"column:name"`
    Total int64  `gorm:"column:total"`
}
db.Raw(`
    SELECT name, COUNT(*) AS total
    FROM users
    GROUP BY name
    HAVING COUNT(*) >= 1
`).Scan(&results)

// Window functions
var ranked []struct {
    ID int   `gorm:"column:id"`
    RN int64 `gorm:"column:rn"`
}
db.Raw(`
    SELECT id, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY id) AS rn
    FROM posts
`).Scan(&ranked)

// Joins
var ids []int
db.Raw(`
    SELECT u.id FROM users u
    INNER JOIN posts p ON p.author_id = u.id
`).Scan(&ids)

// Subquery
db.Raw(`
    SELECT id FROM users
    WHERE id IN (SELECT author_id FROM posts WHERE published = true)
`).Scan(&ids)

Vector Search

GORM does not have a native vector column type. Use raw SQL for vector operations:

Go

// Create vector table and index
db.Exec(`
    CREATE EXTENSION IF NOT EXISTS vector;
    CREATE TABLE IF NOT EXISTS embeddings (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255),
        embedding vector(3) NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_embeddings
        ON embeddings USING hnsw (embedding vector_l2_ops);
`)

// Insert vectors
db.Exec(`INSERT INTO embeddings (name, embedding) VALUES (?, ?)`, "doc-1", "[1.0, 2.0, 3.0]")

// k-NN search by L2 distance
var ids []int
db.Raw(`
    SELECT id FROM embeddings
    WHERE embedding <-> '[1.0, 0.0, 0.0]' < 1.0
    ORDER BY embedding <-> '[1.0, 0.0, 0.0]'
`).Scan(&ids)

// Cosine distance search
var results []struct {
    Name     string  `gorm:"column:name"`
    Distance float64 `gorm:"column:distance"`
}
db.Raw(`
    SELECT name, cosine_distance(embedding, '[1.0, 1.0, 1.0]') AS distance
    FROM embeddings
    ORDER BY distance ASC
    LIMIT 5
`).Scan(&results)

DB9 supports HNSW indexes with vector_l2_ops, vector_cosine_ops, and vector_ip_ops; IVFFlat is not available at all. A CREATE INDEX ... USING hnsw builds a real index over both the wire protocol and the HTTP SQL API. The planner only uses it when the query’s distance metric matches the index’s opclass, the probe is a literal or a bound parameter, a LIMIT is present, and the query has no WHERE clause — the examples above build a vector_l2_ops index and then query with cosine_distance or without a LIMIT, so they run as exact scans. See When the index is used. If your code uses CREATE INDEX ... USING ivfflat, change it to USING hnsw.

Schema Changes

For production, manage migrations with raw SQL:

Go

// Add a column
db.Exec("ALTER TABLE users ADD COLUMN IF NOT EXISTS phone TEXT")

// Create an index
db.Exec("CREATE INDEX IF NOT EXISTS idx_users_age ON users (age)")

// Drop a column
db.Exec("ALTER TABLE users DROP COLUMN IF EXISTS phone")

AutoMigrate works against DB9, including the alteration path: re-running it over an existing schema preserves rows, and adding a field to a struct adds the column. Raw db.Exec() DDL remains useful when you want the exact statement under review.

Production Notes

• Driver: GORM uses gorm.io/driver/postgres which wraps pgx v5. No need to install lib/pq.
• Connection pooling: Access the underlying *sql.DB via db.DB() to configure pool settings (SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime).
• JSON columns: Use gorm:"serializer:json" for map[string]any fields. GORM automatically handles JSON encoding and decoding. The column is TEXT unless the tag also sets type:jsonb.
• Timestamp precision: DB9 stores timestamps with microsecond precision, matching PostgreSQL.
• Connection string: Use the format postgresql://<tenant_id>.admin:<password>@pg.db9.io:5433/postgres?sslmode=require, using the tenant ID prefix from the generated connection string.

Troubleshooting

AutoMigrate

AutoMigrate works against DB9 — initial table creation, re-running over an existing schema (rows are preserved), and adding a column for a new struct field. The Migrator() introspection API works too: HasTable, HasColumn, GetTables and ColumnTypes.

GORM is unaffected by the DB9 catalog gap that breaks drizzle-kit push — it does not read the pg_stat_* views. If you want DDL under review rather than inferred, use db.Exec().

Connection refused

Verify the host (pg.db9.io), port (5433), and complete username from the generated connection string. DB9 routes pgwire connections by the tenant ID prefix in that username.

SSL errors

Use sslmode=require in production. DB9 supports TLS but does not enforce it, which is why sslmode=disable also connects — convenient locally, and the reason to pin require everywhere else.

Timestamp drift

DB9 stores timestamps with microsecond precision, matching PostgreSQL. Go’s time.Time carries nanoseconds, so a value still loses its sub-microsecond digits on a round-trip — compare with a tolerance rather than for exact equality:

Go

delta := createdAt.Sub(fromDB.CreatedAt).Abs()
if delta > time.Microsecond {
    // unexpected drift
}

Verified Compatibility

Tested with GORM 1.25+ and pgx v5 against DB9. E2E smoke tests cover:

Category	Status
Connection and pooling	Pass
CRUD (Create, First, Where, Update, Delete)	Pass
Transactions and savepoints	Pass
AutoMigrate	Pass
JSON serialization (serializer:json)	Pass
Joins (INNER, LEFT)	Pass
GROUP BY / HAVING	Pass
Subqueries and window functions	Pass
Vector operations and HNSW indexing	Pass
DDL (ALTER TABLE, CREATE INDEX)	Pass

Next Pages

• SQLAlchemy — Python ORM alternative
• Connect — connection strings and authentication
• Vector Extension — HNSW indexes and distance operators
• Compatibility Matrix — full PostgreSQL compatibility surface
• Prisma — Node.js ORM alternative