dbtpl

# 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.

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](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: ``` … ``` ## Command Line Options The following are `dbtpl`'s command-line commands, arguments, and options: ``` … ``` ## 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). 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: ```sh $ 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