The Point

dbtpl (part of the xo ecosystem) connects to a database, introspects the schema, and generates type-safe structs and query methods through Go templates. Hand-written DB structs easily drift from the schema; dbtpl makes the schema the single source of truth.

Explanation

How it works

Database (PostgreSQL / MySQL / SQLite...)
    ↓ introspect schema (tables, columns, types, indexes, FK...)
dbtpl
    ↓ apply Go templates
Generated Go code (structs, query methods)

No need to maintain DB structs by hand – when the schema changes, re-run dbtpl to sync.

Two modes

Schema mode: generate from the entire DB schema

1dbtpl schema postgres://user:pass@host/dbname -o ./models

Generates a Go struct for each table, including methods related to primary keys, foreign keys, and indexes.

Query mode: generate type-safe result structs from custom SQL

1dbtpl query postgres://user:pass@host/dbname << ENDSQL
2SELECT a.name::varchar AS name, b.type::integer AS my_type
3FROM authors a
4JOIN authortypes b ON a.id = b.author_id
5WHERE a.id = %%authorID int%%
6ENDSQL

%%param type%% is dbtpl’s query parameter syntax, which generates a corresponding function signature.

What gets generated

• Go structs matching each table (field types aligned with DB schema)
• CRUD query methods (Insert, Update, Delete, Get by PK)
• Enum types
• Struct tags (db:"column_name" etc.)

Template customization

dbtpl’s generation logic uses Go text/template. You can dump the built-in templates and modify them:

1dbtpl dump --src base -o ./custom-templates
2# edit the .tpl files in custom-templates/
3dbtpl schema postgres://... --src ./custom-templates

Knowledge Sugar

Why not just hand-write structs?

dbtpl vs sqlc

Both generate Go code from a database, but they differ in approach:

• sqlc: SQL-query-centric – write SQL first, then generate the corresponding functions
• dbtpl: schema-centric – introspects the entire DB, with more template flexibility