Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server
dbtpl
dbtpl is a command-line tool to inspect and generate templated code based on a database schema or a custom database query.
In addition to being able to generate standardized "model" code for a database, dbtpl is also capable of creating schema creation scripts for a database, generating JSON/YAML definitions, and Graphviz diagrams for schemas.
Installing | Building | Using | Releases
[![Releases][release-status]][Releases] [![Discord Discussion][discord-status]][discord]
[releases]: https://github.com/xo/dbtpl/releases "Releases" [release-status]: https://img.shields.io/github/v/release/xo/dbtpl?display_name=tag&sort=semver "Latest Release" [discord]: https://discord.gg/WDWAgXwJqN "Discord Discussion" [discord-status]: https://img.shields.io/discord/829150509658013727.svg?label=Discord&logo=Discord&colorB=7289da&style=flat-square "Discord Discussion"
Supported languages
At the moment, dbtpl only supports Go. Support for other languages is possible, but not currently planned.
How it works
In schema mode, dbtpl connects to your database and generates code using Go templates. dbtpl works by using database metadata and SQL introspection queries to discover the types and relationships contained within a schema, and applying a standard set of base (or customized) Go templates against the discovered relationships.
Currently, dbtpl can generate types for tables, enums, stored procedures, and custom SQL queries for PostgreSQL, MySQL, Oracle, Microsoft SQL Server, and SQLite3 databases.
Note: While the code generated by dbtpl is production quality, it is not
the goal, nor the intention for dbtpl to be a "silver bullet," nor to
completely eliminate the manual authoring of SQL / Go code.
In query mode, dbtpl parses your query to generate code from Go templates. It finds related tables in your database to ensure type safety.
Database Feature Support
The following is a matrix of the feature support for each database:
| | PostgreSQL | MySQL | Oracle | Microsoft SQL Server | SQLite | | ------------ | :----------------: | :----------------: | :----------------: | :------------------: | :----------------: | | Models | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | | Primary Keys | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | | Foreign Keys | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | | Indexes | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | | Stored Procs | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | | Functions | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | :whitecheckmark: | | ENUM types | :whitecheckmark: | :whitecheckmark: | | | | | Custom types | :whitecheckmark: | | | | |
Installing
dbtpl can be installed [via Release][], [via Homebrew][], [via AUR][], [via Scoop][] or [via Go][]:
[via Release]: #installing-via-release [via Homebrew]: #installing-via-homebrew-macos-and-linux [via AUR]: #installing-via-aur-arch-linux [via Scoop]: #installing-via-scoop-windows [via Go]: #installing-via-go
Installing via Release
- [Download a release for your platform][releases]
- Extract the
dbtplordbtpl.exefile from the.tar.bz2or.zipfile - Move the extracted executable to somewhere on your
$PATH(Linux/macOS) or
%PATH% (Windows)
Installing via Homebrew (macOS and Linux)
Install dbtpl from the [xo/xo tap][xo-tap] in the usual way with the [brew command][homebrew]:
# install
$ brew install xo/xo/dbtplInstalling via AUR (Arch Linux)
Install dbtpl from the [Arch Linux AUR][aur] in the usual way with the [yay command][yay]:
# install
$ yay -S dbtplAlternately, build and [install using makepkg][arch-makepkg]:
# clone package repo and make/install package
$ git clone https://aur.archlinux.org/dbtpl.git && cd dbtpl
$ makepkg -si
==> Making package: dbtpl 0.4.4-1 (Sat 11 Nov 2023 02:28:28 PM WIB)
==> Checking runtime dependencies...
==> Checking buildtime dependencies...
==> Retrieving sources...
...Installing via Scoop (Windows)
Install dbtpl using Scoop:
# Optional: Needed to run a remote script the first time
> Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
install scoop if not already installed
> irm get.scoop.sh | iex
install dbtpl with scoop
> scoop install dbtplInstalling via Go
Install dbtpl in the usual Go fashion:
# install latest dbtpl version
$ go install github.com/xo/dbtpl@latestQuickstart
The following is a quick overview of using dbtpl on the command-line:
# Make an output directory for generated code.
$ mkdir -p models
Generate code from your Postgres schema. (Default output folder is models)
$ dbtpl schema postgres://user:pass@host/dbname
Generate code from a Microsoft SQL schema using a custom template directory (see notes below)
$ mkdir -p mssqlmodels
$ dbtpl schema mssql://user:pass@host/dbname -o mssqlmodels --src custom/templates
Generate code from a custom SQL query for Postgres
$ dbtpl query postgres://user:pass@host/dbname -M -B -2 -T AuthorResult << ENDSQL
SELECT
a.name::varchar AS name,
b.type::integer AS my_type
FROM authors a
INNER JOIN authortypes b ON a.id = b.author_id
WHERE
a.id = %%authorID int%%
LIMIT %%limit int%%
ENDSQL
Build generated code - verify it compiles
$ go build ./models/
$ go build ./mssqlmodels/Command Line Options
The following are dbtpl's command-line commands, arguments, and options:
$ dbtpl --help-long
usage: dbtpl [<flags>] <command> [<args> ...]
Flags: --help Show context-sensitive help (also try --help-long and --help-man). -v, --verbose enable verbose output --version display version and exit
Commands: help [<command>...] Show help.
query [<flags>] <DSN> Generate code for a database custom query from a template.
-s, --schema=<name> database schema name -t, --template=go template type (createdb, dot, go, json, yaml; default: go) -f, --suffix=<ext> file extension suffix for generated files (otherwise set by template type) -o, --out=models out path (default: models) -a, --append enable append mode -S, --single=<file> enable single file output -D, --debug debug generated code (writes generated code to disk without post processing) -Q, --query="" custom database query (uses stdin if not provided) -T, --type=<name> type name --type-comment="" type comment -F, --func=<name> func name --func-comment="" func comment -M, --trim enable trimming whitespace -B, --strip enable stripping type casts -1, --one enable returning single (only one) result -l, --flat enable returning unstructured values -X, --exec enable exec (no introspection performed) -I, --interpolate enable interpolation of embedded params -L, --delimiter=%% delimiter used for embedded params (default: %%) -Z, --fields=<field> override field names for results -U, --allow-nulls allow result fields with NULL values -d, --src=<path> template source directory -2, --go-not-first disable package comment (ie, not first generated file) --go-int32=int int32 type (default: int) --go-uint32=uint uint32 type (default: uint) --go-pkg=<name> package name --go-tag="" ... build tags --go-import="" ... package imports --go-uuid=<pkg> uuid type package --go-custom=<name> package name for custom types --go-conflict=Val name conflict suffix (default: Val) --go-initialism=<val> ... add initialism (i.e ID, API, URI) --go-esc=none ... escape fields (none, schema, table, column, all; default: none) -g, --go-field-tag=<tag> field tag --go-context=only context mode (disable, both, only; default: only) --go-inject="" insert code into generated file headers --go-inject-file=<file> insert code into generated file headers from a file --go-legacy enables legacy v1 template funcs --go-enum-table-prefix enables table name prefix to enums --json-indent=" " indent spacing --json-ugly disable indentation
schema [<flags>] <DSN> Generate code for a database schema from a template.
-s, --schema=<name> database schema name -t, --template=go template type (createdb, dot, go, json, yaml; default: go) -f, --suffix=<ext> file extension suffix for generated files (otherwise set by template type) -o, --out=models out path (default: models) -a, --append enable append mode -S, --single=<file> enable single file output -D, --debug debug generated code (writes generated code to disk without post processing) -k, --fk-mode=smart foreign key resolution mode (smart, parent, field, key; default: smart) -i, --include=<glob> ... include types (<type>) -e, --exclude=<glob> ... exclude types/fields (<type>[.<field>]) -j, --use-index-names use index names as defined in schema for generated code -d, --src=<path> template source directory --createdb-fmt=<path> fmt command (default: /home/ken/.npm-global/bin/sql-formatter) --createdb-fmt-opts=<opts> ... fmt options (default: -u, -l={{ . }}, -i=2, --lines-between-queries=2) --createdb-constraint enable constraint name in output (postgres, mysql, sqlite3) --createdb-escape=none escape mode (none, types, all; default: none) --createdb-engine="" mysql table engine (default: InnoDB) --createdb-trim-comment trim leading comment from views and procs (--no-createdb-trim-comment) --dot-defaults="" ... default statements (default: node [shape=none, margin=0]) --dot-bold bold header row --dot-color="" header color (default: lightblue) --dot-row="" row value template (default: {{ .Name }}: {{ .Type.Type }}) --dot-direction enable edge directions -2, --go-not-first disable package comment (ie, not first generated file) --go-int32=int int32 type (default: int) --go-uint32=uint uint32 type (default: uint) --go-pkg=<name> package name --go-tag="" ... build tags --go-import="" ... package imports --go-uuid=<pkg> uuid type package --go-custom=<name> package name for custom types --go-conflict=Val name conflict suffix (default: Val) --go-initialism=<val> ... add initialism (i.e ID, API, URI) --go-esc=none ... escape fields (none, schema, table, column, all; default: none) -g, --go-field-tag=<tag> field tag --go-context=only context mode (disable, both, only; default: only) --go-inject="" insert code into generated file headers --go-inject-file=<file> insert code into generated file headers from a file --go-legacy enables legacy v1 template funcs --go-enum-table-prefix enables table name prefix to enums --json-indent=" " indent spacing --json-ugly disable indentation --postgres-oids enable postgres OIDs
dump [<flags>] <out> Dump internal templates to path.
-t, --template=go template type (createdb, dot, go, json, yaml; default: go) -f, --suffix=<ext> file extension suffix for generated files (otherwise set by template type)
About Base Templates
dbtpl provides a set of generic "base" templates for each of the supported databases, but it is understood these templates are not suitable for every organization or every schema out there. As such, you can author your own custom templates, or modify the base templates available in the dbtpl source tree, and use those with dbtpl by a passing a directory path via the --src flag.
For non-trivial schemas, custom templates are the most practical, common, and best way to use dbtpl (see below quickstart and related example).
Custom Template Quickstart
The following is a quick overview of copying the base templates contained in the dbtpl project's templates/ directory, editing to suit, and using with dbtpl:
# Create a working directory
$ mkdir -p my-tpl
Dump an embedded template to disk
$ dbtpl dump -t createdb my-tpl
edit base template files
$ vi my-tpl/*.go.tpl
see command line options for the template
$ dbtpl schema --src my-tpl --help
generate a schema using the custom template
$ dbtpl schema --src my-tpl -o models postgres://user:pass@host/dbSee the Custom Template example below for more information on adapting the base templates in the dbtpl source tree for use within your own project.
Storing Project Templates
Ideally, custom templates for your project/schema should be stored alongside your project. and generated as part of an automated build pipeline using go generate:
# Add to custom dbtpl command to go generate:
$ tee -a gen.go << END
package mypackage
//go:generate dbtpl postgres://user:pass@host/db -o models --src templates END
Run go generate
$ go generate
Add custom templates and gen.go to project
$ git add templates gen.go && git commit -m 'Adding custom dbtpl templates for models'Note: via the--template/-tparameter ofdbtpl dumpyou can generate
other templates withdbtpl. The default template is thegotemplate.
Template Language/Syntax
dbtpl templates are standard Go text templates. Please see the documentation for Go's standard text/template package for information concerning the syntax, logic, and variable use within Go templates.
Template Context and File Layout
The contexts (ie, the . identifier in templates) made available to custom templates can be found in templates/types.go (see below table for more information on which file uses which type).
Each language, has its own set of templates for $TYPE and are available in the templates/.
| Template File | Description | | -------------------- | -------------------------------------------------------------------------------------- | | *.go | Template logic | | hdr.dbtpl.*.tpl | File header template. Executed with content for each generated file. | | db.dbtpl.*.tpl | Package level template with base types and interface data. Generated once per package. | | query.dbtpl.*.tpl | Template for custom query execution. | | schema.dbtpl.*.tpl | Template for custom query's generated type. |
\* - is the template type, for example go, json, yaml, etc.
Examples
Example: End-to-End
Please see the booktest example for a full end-to-end example for each supported database, showcasing how to use a database schema with dbtpl, and the resulting code generated by dbtpl.
Additionally, please see the northwind and django for a demonstration of running dbtpl against larger schema and against databases from other frameworks. Please note that these examples are works in progress, and may not work properly in all scenarios.
Example: Ignoring Fields
Sometimes you may wish to have the database manage the values of columns instead of having them managed by code generated by dbtpl. As such, when you need dbtpl to ignore fields for a database schema, you can use the -e or --exclude flag. For example, a common use case is to define a table with createdat and/or modifiedat timestamps fields, where the database is responsible for setting column values on INSERT and UPDATE, respectively.
Consider the following PostgreSQL schema where a users table has a createdat and modifiedat field, where created_at has a default value of now() and where modified_at is updated by a trigger on UPDATE:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name text NOT NULL DEFAULT '' UNIQUE,
created_at timestamptz default now(),
modified_at timestamptz default now()
);
CREATEOR REPLACE FUNCTION updatemodifiedcolumn() RETURNS TRIGGER AS $$ BEGIN NEW.modified_at= now(); RETURN NEW; END; $$language 'plpgsql';
CREATE TRIGGER updateusersmodtime BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE updatemodifiedcolumn();
We can ensure that these columns are managed by PostgreSQL and not by the application logic but by dbtpl by passing the --exclude or -e flag:
# Ignore special fields
$ dbtpl schema postgres://user:pass@host/db -e users.createdat -e users.modifiedat
or, To ignore these fields in all tables
$ dbtpl schema postgres://user:pass@host/db -e .createdat -e .modifiedatExample: Custom Template -- adding a GetMostRecent lookup for all tables (Go)
Often, a schema has a common layout/pattern, such as every table having a createdat and modifiedat field (as in the PostgreSQL schema in the previous example). It is then a common use-case to have a GetMostRecent lookup for each table type, retrieving the most recently modified rows for each table (up to some limit, N).
To accomplish this with dbtpl, we will need to create our own set of custom templates, and then add a GetMostRecent lookup to the .type.go.tpl template.
First, we dump the base dbtpl Go template:
$ mkdir -p my-tpl
$ dbtpl dump my-tpl
We can now modify the templates to suit our specific schema, adding lookups, helpers, or anything else necessary for our schema.
To add a GetMostRecent lookup, we edit our copy of the typedef.dbtpl.go.tpl template:
$ vi templates/gotpl/schema/typedef.dbtpl.go.tplAnd add the following templated GetMostRecent func at the end of the file:
// GetMostRecent{{ $type.Name }} returns n most recent rows from '{{ $table }}',
// ordered by "created_at" in descending order.
func GetMostRecent{{ $type.Name }}(ctx context.Context, db DB, n int) ([]*{{ $type.Name }}, error) {
const sqlstr = SELECT +
{{ $type.Fields "createdat" "modifiedat" }} +
FROM {{ $table }} +
ORDER BY created_at DESC LIMIT $1
rows, err := db.QueryContext(ctx, sqlstr, n) if err != nil { return nil, logerror(err) } defer rows.Close()
// load results var res []*{{ $type.Name }} for rows.Next() { {{ $short }} := {{ $type.Name }}{ {{- if $type.PrimaryKey }} _exists: true, {{ end -}} } // scan if err := rows.Scan({{ fieldnames $type.Fields (print "&" $short) }}); err != nil { return nil, logerror(err) } res = append(res, &{{ $short }}) } return res, nil }
We can then use the templates in conjunction with dbtpl to generate our "model" code:
$ dbtpl schema postgres://user:pass@localhost/dbname --src templates/There will now be a GetMostRecentUsers func defined in models/user.dbtpl.go, which can be used as follows:
db, err := dburl.Open("postgres://user:pass@localhost/dbname")
if err != nil { / ... / }
// retrieve 15 most recent items mostRecentUsers, err := models.GetMostRecentUsers(context.Background(), db, 15) if err != nil { / ... / } for _, user := range users { log.Printf("got user: %+v", user) }
Using SQL Drivers
Please note that the base dbtpl templates do not import any SQL drivers. It is left for the user of dbtpl's generated code to import the actual drivers. For reference, these are the expected drivers to use with the code generated by dbtpl:
| Database (driver) | Package | | ---------------------------- | -------------------------------------------------------------------------- | | PostgreSQL (postgres) | github.com/lib/pq | | SQLite3 (sqlite3) | github.com/mattn/go-sqlite3 | | MySQL (mysql) | github.com/go-sql-driver/mysql | | Microsoft SQL Server (mssql) | github.com/microsoft/go-mssqldb | | Oracle (ora) | github.com/sijms/go-ora/v2 |
Additionally, please see below for usage notes on specific SQL database drivers.
MySQL (mysql)
If your schema or custom query contains table or column names that need to be escaped using any of the --escape-* options, you must pass the sql_mode=ansi option to the MySQL driver:
$ dbtpl --escape-all 'mysql://user:pass@host/?parseTime=true&sql_mode=ansi' -o modelsAnd when opening a database connection:
db, err := dburl.Open("mysql://user:pass@host/?parseTime=true&sql_mode=ansi")Additionally, when working with date/time column types in MySQL, one should pass the parseTime=true option to the MySQL driver:
$ dbtpl schema 'mysql://user:pass@host/dbname?parseTime=true' -o modelsAnd when opening a database connection:
db, err := dburl.Open("mysql://user:pass@host/dbname?parseTime=true")SQLite3 (sqlite3)
While not required, one should specify the loc=auto option when using dbtpl with a SQLite3 database:
$ dbtpl schema 'file:mydatabase.sqlite3?loc=auto' -o modelsAnd when opening a database connection:
db, err := dburl.Open("file:mydatabase.sqlite3?loc=auto")About Primary Keys
For row inserts dbtpl determines whether the primary key is automatically generated by the DB or must be provided by the application for the table row being inserted. For example a table that has a primary key that is also a foreign key to another table, or a table that has multiple primary keys in a many-to-many link table, it is desired that the application provide the primary key(s) for the insert rather than the DB.
dbtpl will query the schema to determine if the database provides an automatic primary key and if the table does not provide one then it will require that the application provide the primary key for the object passed to the Insert method. Below is information on how the logic works for each database type to determine if the DB automatically provides the PK.
PostgreSQL Auto PK Logic
- Checks for a sequence that is owned by the table in question.
MySQL Auto PK Logic
- Checks for an autoincrement row in the information_schema for the table in
SQLite Auto PK Logic
- Checks the SQL that is used to generate the table contains
- Checks that the table was created with the primary key type of INTEGER.
CREATE TABLE site_contacts (
contact_id INT NOT NULL,
site_id INT NOT NULL,
PRIMARY KEY(contact_id,siteid),
FOREIGN KEY(contactid) REFERENCES contacts (contactid),
FOREIGN KEY(siteid) REFERENCES sites (siteid)
)SQL Server Auto PK Logic
- Checks for an identity associated with one of the columns for the table in
Oracle Auto PK Logic
ALWAYS GENERATED types will be parsed as Auto PK types for Oracle.
About dbtpl: Design, Origin, Philosophy, and History
dbtpl can likely get you 99% "of the way there" on medium or large database schemas and 100% of the way there for small or trivial database schemas. In short, dbtpl is a great launching point for developing standardized packages for standard database abstractions/relationships, and dbtpl's most common use-case is indeed in a code generation pipeline, ala stringer.
Design
dbtpl is NOT designed to be an ORM or to generate an ORM. Instead, dbtpl is designed to vastly reduce the overhead/redundancy of (re-)writing types and funcs for common database queries/relationships -- it is not meant to be a "silver bullet".
History
dbtpl was originally developed while migrating a large application written in PHP to Go. The schema in use in the original app, while well-designed, had become inconsistent over multiple iterations/generations, mainly due to different naming styles adopted by various developers/database admins over the preceding years. Additionally, some components had been written in different languages (Ruby, Java) and had also accumulated significant drift from the original application and accompanying schema. Simultaneously, a large amount of growth meant that the PHP/Ruby code could no longer efficiently serve the traffic volumes.
In late 2014/early 2015, a decision was made to unify and strip out certain backend services and to fully isolate the API from the original application, allowing the various components to instead speak to a common API layer instead of directly to the database, and to build that service layer in Go.
However, unraveling the old PHP/Ruby/Java code became a large headache, as the code, the database, and the API, all had significant drift -- thus, underlying function names, fields, and API methods no longer coincided with the actual database schema, and were named differently in each language. As such, after a round of standardizing names, dropping cruft, and adding a few relationship changes to the schema, the various codebases were fixed to match the schema changes. After that was determined to be a success, the next target was to rewrite the backend services in Go.
In order to keep a similar and consistent workflow for the developers, the previous code generator (written in PHP and Twig templates) was modified to generate Go code. Additionally, at this time, but tangential to the story, the API definitions were ported from JSON to Protobuf to make use of its code generation abilities as well.
dbtpl is the open source version of that code generation tool, and is the fruits of those development efforts. It is hoped that others will be able to use and expand dbtpl to support other databases -- SQL or otherwise -- and that dbtpl can become a common tool in any Go developer's toolbox.
In May of 2025, the project was renamed from xo to dbtpl to more readily convey the tool's purpose.
Goals
Part of dbtpl's goals is to avoid writing an ORM, or an ORM-like in Go, and to instead generate static, type-safe, fast, and idiomatic Go code across languages and databases.
Additionally, the dbtpl developers are of the opinion that relational databases should have proper, well-designed relationships and all the related definitions should reside within the database schema itself: ie, a "self-documenting" schema. dbtpl is an end to that pursuit.
Related Projects
- dburl - a Go package providing a standard, URL
- usql - a universal command-line interface for
Other Projects
The following projects work with similar concepts as dbtpl:
