xo

About xo

This large README is shown as a plain-text preview. Read the full README at the source

# 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](https://graphviz.org) diagrams
for schemas.

<p align="center">
  <a href="#installing" title="Installing">Installing</a> |
  <a href="#building" title="Building">Building</a> |
  <a href="#using" title="Using">Using</a> |
  <a href="https://github.com/xo/dbtpl/releases" title="Releases">Releases</a>
</p>

[![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](https://golang.org). 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](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][]:

[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

1. [Download a release for your platform][releases]
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]:

```sh
# 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]:

```sh
# install
$ yay -S dbtpl
```

Alternately, build and [install using `makepkg`][arch-makepkg]:

```sh
# 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](https://scoop.sh):

```powershell
# 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:

```sh
# 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:

```sh
# 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:

```sh
$ 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](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/`](templates) directory, editing to suit, and
using with `dbtpl`:

```sh
# 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/db
```

See 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`:

```sh
# 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`/`-t` parameter of `dbtpl dump` you can generate
> other templates with `dbtpl`. The default template is the `go` template.

### Template Language/Syntax

`dbtpl` templates are standard Go text templates. Please see the [documentation
for Go's standard `text/template` package](https://pkg.go.dev/text/template)
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](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/](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](_examples/booktest) 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`](_examples/northwind) and
[`django`](_examples/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
`created_at` and/or `modified_at` 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
`created_at` and `modified_at` field, where `created_at` has a default value of
`now()` and where `modified_at` is updated by a trigger on `UPDATE`:

```postgresql
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 update_modified_column() RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_at= now();
RETURN NEW;
END;
$$language 'plpgsql';

CREATE TRIGGER update_users_modtime BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
```

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:

```sh
# Ignore special fields
$ dbtpl schema postgres://user:pass@host/db -e users.created_at -e users.modified_at
# or, To ignore these fields in all tables
$ dbtpl schema postgres://user:pass@host/db -e *.created_at -e *.modified_at
```

### Example: Custom Template -- adding a `GetMostRecent` lookup for all tables (Go)

Often, a schema has a common layout/pattern, such as every table having a
`created_at` and `modified_at` 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).

Frequently Asked Questions