Dbtpl
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server
Install / Use
/learn @xo/DbtplREADME
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.
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 | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | | Primary Keys | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | | Foreign Keys | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | | Indexes | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | | Stored Procs | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | | Functions | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | | ENUM types | :white_check_mark: | :white_check_mark: | | | | | Custom types | :white_check_mark: | | | | |
Installing
dbtpl can be installed via Release, via Homebrew, via AUR, via
Scoop or via Go:
Installing via Release
- Download a release for your platform
- 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/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
