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

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:

Installing

dbtpl can be installed via Release, via Homebrew, via AUR, via Scoop or via Go:

Installing via Release

1. Download a release for your platform
2. Extract the dbtpl or dbtpl.exe file from the .tar.bz2 or .zip file
3. 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/dbtpl

Installing via AUR (Arch Linux)

Install dbtpl from the [Arch Linux AUR][aur] in the usual way with the [yay command][yay]:

# install
$ yay -S dbtpl

Alternately, 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 dbtpl

Installing via Go

Install dbtpl in the usual Go fashion:

# install latest dbtpl version
$ go install github.com/xo/dbtpl@latest

Quickstart

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 gene