dbx

name	dbx
description	Use when working with DBX database schema definitions, generating Go database bindings, creating models, or writing CRUD operations. DBX is a code generation tool that creates Go code for Postgres, CockroachDB, Spanner, and SQLite databases.
argument-hint	[create|read|update|delete|model|schema|help]

DBX Database Code Generator

DBX generates database schemas and Go code for interacting with databases. It supports Postgres (pgx), CockroachDB (pgxcockroach), Spanner, and SQLite3 dialects.

Installation

go install storj.io/dbx@latest

CLI Commands

# Generate Go code
dbx golang [-p package] [-d dialect] [-t templates] [--userdata] [-i include.dbx] schema.dbx outdir

# Generate SQL schema
dbx schema [-d dialect] [-i include.dbx] schema.dbx outdir

# Format dbx file (reads stdin, writes stdout)
dbx format < input.dbx > output.dbx

Dialect Options

• pgx (default) - PostgreSQL via pgx driver
• pgxcockroach - CockroachDB via pgx driver
• spanner - Google Cloud Spanner via go-sql-spanner
• sqlite3 - SQLite3

Go Generate Directive

//go:generate dbx golang -d pgx -d pgxcockroach -d spanner schema.dbx .
//go:generate dbx schema -d pgx -d pgxcockroach -d spanner schema.dbx .

DBX File Syntax

DBX uses a tuple/list grammar. Lists are parentheses-enclosed comma-separated tuples. Newlines auto-insert commas.

Model Definition

model <name> (
    // Optional: custom table name
    table <table_name>

    // Required: primary key (single or composite)
    key <field_names>

    // Optional: unique constraints
    unique <field_names>

    // Optional: indexes
    index (
        name <index_name>
        fields <field_names>
        unique              // optional
        storing <fields>    // optional: include columns (covering index)
        where <condition>   // optional: partial index
    )

    // Fields
    field <name> <type> ( attributes )

    // Foreign key relations
    field <name> <model>.<field> <relation> ( attributes )
)

Field Types

Type	Go Type	Description
serial	int	Auto-incrementing integer
serial64	int64	Auto-incrementing 64-bit integer
int	int	32-bit integer
int64	int64	64-bit integer
uint	uint	Unsigned 32-bit integer
uint64	uint64	Unsigned 64-bit integer
bool	bool	Boolean
text	string	Text/varchar
timestamp	time.Time	Timestamp with timezone
utimestamp	time.Time	Timestamp without timezone (UTC)
float	float32	32-bit float
float64	float64	64-bit float
blob	[]byte	Binary data
json	[]byte	JSON data (uses JSON/JSONB SQL type)
date	time.Time	Date only

Field Attributes

Attribute	Description
column <name>	Custom column name
nullable	Field can be NULL
updatable	Field can be updated
autoinsert	Auto-populate on insert (timestamps get current time)
autoupdate	Auto-populate on update (timestamps get current time)
length <n>	Max text length
default <value>	Default value (numbers, strings, "epoch" for timestamps, "{}" for JSON)

Foreign Key Relations

Relation	Behavior on Delete
cascade	Delete this row when related row is deleted
restrict	Prevent deletion of related row
setnull	Set field to NULL (requires nullable)

CRUD Operations

Create

create <model> (
    raw        // expose all fields, including autoinsert ones
    noreturn   // don't return the created row
    replace    // upsert behavior (INSERT OR REPLACE)
    suffix <parts>  // custom method name suffix
)

Read

read <views> (
    select <model_or_fields>
    where <expr> <op> <expr>
    join <model.field> = <model.field>
    orderby <asc|desc> <model.field>
    groupby <model.field>
    suffix <parts>
)

Views (for all reads):

• count - returns count of results
• has - returns boolean if results exist
• first - returns first result or nil
• scalar - returns single result, nil, or error if multiple
• one - returns single result or error

Views (for non-distinct reads only):

• all - returns all results as slice
• limitoffset - paginated results with limit/offset
• paged - forward cursor pagination

Where Expressions:

where model.field = ?              // placeholder parameter
where model.field = null           // NULL check
where model.field = "literal"      // string literal
where model.field < 30             // numeric literal
where model.field = true           // boolean literal
where model.field = other.field    // field comparison
where lower(model.field) = ?       // function call (lower supported)
where ( a.x = ?, a.y = ?, a.z = ? ) // OR grouping

Operators: =, !=, <, <=, >, >=

Update

update <model> (
    where <model.field> <op> <expr>
    join <model.field> = <model.field>
    noreturn   // don't return the updated row
    suffix <parts>
)

Updates only fields marked updatable. Requires unique identification via where/join.

Delete

delete <model> (
    where <model.field> <op> <expr>
    join <model.field> = <model.field>
    suffix <parts>
)

Common Patterns

Basic User Model

model user (
    key    pk
    unique id
    unique email

    field pk         serial64
    field created_at timestamp ( autoinsert )
    field updated_at timestamp ( autoinsert, autoupdate )
    field id         text
    field email      text
    field name       text      ( updatable )
)

create user ( )
read one ( select user, where user.pk = ? )
read one ( select user, where user.id = ? )
update user ( where user.pk = ? )
delete user ( where user.pk = ? )

Many-to-Many Relationship

model user (
    key pk
    field pk serial64
)

model role (
    key pk
    field pk   serial64
    field name text
)

model user_role (
    key   user_pk role_pk
    field user_pk user.pk cascade
    field role_pk role.pk cascade
)

create user_role ( )

read all (
    select role
    join role.pk = user_role.role_pk
    join user_role.user_pk = user.pk
    where user.pk = ?
)

Nullable Foreign Key with Setnull

model post (
    key pk
    field pk        serial64
    field author_pk user.pk setnull ( nullable )
    field content   text
)

Composite Primary Key

model event_log (
    key timestamp source_id

    field timestamp  utimestamp
    field source_id  blob
    field event_type uint
    field payload    json
)

create event_log ( noreturn )

read paged (
    select event_log
    where event_log.source_id = ?
)

Partial Index

model task (
    key pk
    index (
        fields status
        where task.status != "completed"
    )

    field pk     serial64
    field status text ( updatable )
)

Key-Value Store with Replace

model kv (
    key key
    field key text
    field val text
)

create kv ( replace, noreturn )
read one ( select kv, where kv.key = ? )

Generated Code Usage

// Open database
db, err := mypackage.Open("pgx", "postgres://...")

// Create
user, err := db.Create_User(ctx,
    mypackage.User_Id("uuid-here"),
    mypackage.User_Email("[e-mail]"),
    mypackage.User_Name("John Doe"))

// Read
user, err := db.Get_User_By_Pk(ctx,
    mypackage.User_Pk(123))

// Update
user, err := db.Update_User_By_Pk(ctx,
    mypackage.User_Pk(123),
    mypackage.User_Update_Fields{
        Name: mypackage.User_Name("Jane Doe"),
    })

// Delete
deleted, err := db.Delete_User_By_Pk(ctx,
    mypackage.User_Pk(123))

// Transaction
tx, err := db.Open(ctx)
defer tx.Rollback()
// ... operations on tx ...
err = tx.Commit()

Customization Hooks

// Wrap all errors
mypackage.WrapErr = func(err *mypackage.Error) error {
    return fmt.Errorf("db error: %w", err)
}

// Log all SQL
mypackage.Logger = func(format string, args ...any) {
    log.Printf(format, args...)
}

// Mock time in tests
db.Hooks.Now = func() time.Time {
    return fixedTime
}

Common Errors

• "no field X defined on model Y" - Check field name spelling in key/unique/where clauses
• "no updatable fields" - Add updatable attribute to fields you want to update
• "must specify some field references" - key clause requires field names
• Cyclic foreign key - Model A references B which references A; restructure models

Tips

1. Always define key for every model
2. Use serial64 for primary keys unless you have a specific reason
3. Mark timestamp fields with autoinsert and/or autoupdate
4. Only mark fields updatable that should be modifiable after creation
5. Use noreturn on creates when you don't need the result (batch inserts)
6. Use replace for upsert/idempotent operations
7. Combine nullable with setnull foreign keys for optional relationships
8. Use suffix to disambiguate methods with the same signature