About gendal

gendal is a command-line tool to generate Go code based on a database schema or a custom query. It is based on a fork of the xo repository.

gendal 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, gendal can generate types for tables, enums, stored procedures, and custom SQL queries for PostgreSQL, MySQL, Oracle, Microsoft SQL Server, CockroachDB, and SQLite3 databases.

NOTE: While the code generated by gendal is production quality, it is not the goal, nor the intention for gendal to be a "silver bullet," nor to completely eliminate the manual authoring of SQL / Go code.

Database Feature Support

The following is a matrix of the feature support for each database:

| | PostgreSQL | MySQL | Oracle | Microsoft SQL Server| SQLite | CockroachDB | | ------------ |:----------------:|:----------------:|:----------------:|:-------------------:|:----------------:|:----------------:| | Models |:white_check_mark:|: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:|:white_check_mark:| | Foreign Keys |:white_check_mark:|: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:|:white_check_mark:| | Stored Procs |:white_check_mark:|:white_check_mark:| | | | | | ENUM types |:white_check_mark:|:white_check_mark:| | | | | | Custom types |:white_check_mark:| | | | | |

Installation

Install goimports dependency (if not already installed):

$ go get -u golang.org/x/tools/cmd/goimports

Then, install in the usual Go way:

$ go get -u github.com/turnkey-commerce/gendal

# install with oracle support (see notes below)
$ go get -tags oracle -u github.com/turnkey-commerce/gendal

NOTE: Go 1.11+ is needed for installing gendal from source, as it makes use of the trim template syntax in Go templates, which is not compatible with previous versions of Go. However, code generated by gendal should compile with Go 1.3+.

Quickstart

The following is a quick overview of using gendal on the command-line:

# change to project directory
$ cd $GOPATH/src/path/to/project

# make an output directory
$ mkdir -p models

# generate code for a postgres schema
$ gendal pgsql://user:pass@host/dbname -o models

# generate code for a Microsoft SQL schema using a custom template directory (see notes below)
$ mkdir -p mssqlmodels
$ gendal mysql://user:pass@host/dbname -o mssqlmodels --template-path /path/to/custom/templates

# generate code for a custom SQL query for postgres
$ gendal pgsql://user:pass@host/dbname -N -M -B -T AuthorResult -o models/ << 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
$ go build ./models/
$ go build ./mssqlmodels/

# do standard go install
$ go install ./models/
$ go install ./mssqlmodels/

Note: For query generation, the same parameter can be used more than once by referencing it without any type information. For example, the parameter %%authorID int%% can be referenced further in the query with %%authorID%%.

Command Line Options

Following are gendal's command-line arguments and options. These options can also be set using a configuration file.

$ gendal --help
usage: gendal [--verbose] [--schema SCHEMA] [--out OUT] [--append] [--suffix SUFFIX] [--single-file] [--package PACKAGE] [--custom-type-package CUSTOM-TYPE-PACKAGE] [--int32-type INT32-TYPE] [--uint32-type UINT32-TYPE] [--ignore-fields IGNORE-FIELDS] [--ignore-tables IGNORE-TABLES] [--fk-mode FK-MODE] [--use-index-names] [--use-reversed-enum-const-names] [--query-mode] [--query QUERY] [--query-type QUERY-TYPE] [--query-func QUERY-FUNC] [--query-only-one] [--query-trim] [--query-strip] [--query-interpolate] [--query-type-comment QUERY-TYPE-COMMENT] [--query-func-comment QUERY-FUNC-COMMENT] [--query-delimiter QUERY-DELIMITER] [--query-fields QUERY-FIELDS] [--escape-all] [--escape-schema] [--escape-table] [--escape-column] [--enable-postgres-oids] [--name-conflict-suffix NAME-CONFLICT-SUFFIX] [--template-path TEMPLATE-PATH] [--sqlx] DSN

positional arguments:
  dsn                    data source name

options:
  --verbose, -v          toggle verbose
  --schema SCHEMA, -s SCHEMA
                         schema name to generate Go types for
  --out OUT, -o OUT      output path or file name
  --append, -a           append to existing files
  --suffix SUFFIX, -f SUFFIX
                         output file suffix [default: .gendal.go]
  --single-file          toggle single file output
  --package PACKAGE, -p PACKAGE
                         package name used in generated Go code
  --custom-type-package CUSTOM-TYPE-PACKAGE, -C CUSTOM-TYPE-PACKAGE
                         Go package name to use for custom or unknown types
  --int32-type INT32-TYPE, -i INT32-TYPE
                         Go type to assign to integers [default: int]
  --uint32-type UINT32-TYPE, -u UINT32-TYPE
                         Go type to assign to unsigned integers [default: uint]
  --ignore-fields IGNORE-FIELDS
                         fields to exclude from the generated Go code types
  --ignore-tables IGNORE-TABLES
                         tables to exclude from the generated Go code types
  --fk-mode FK-MODE, -k FK-MODE
                         sets mode for naming foreign key funcs in generated Go code [values: <smart|parent|field|key>] [default: smart]
  --use-index-names, -j
                         use index names as defined in schema for generated Go code
  --use-reversed-enum-const-names, -R
                         use reversed enum names for generated consts in Go code
  --query-mode, -N       enable query mode
  --query QUERY, -Q QUERY
                         query to generate Go type and func from
  --query-type QUERY-TYPE, -T QUERY-TYPE
                         query's generated Go type
  --query-func QUERY-FUNC, -F QUERY-FUNC
                         query's generated Go func name
  --query-only-one, -1   toggle query's generated Go func to return only one result
  --query-trim, -M       toggle trimming of query whitespace in generated Go code
  --query-strip, -B      toggle stripping type casts from query in generated Go code
  --query-interpolate, -I
                         toggle query interpolation in generated Go code
  --query-type-comment QUERY-TYPE-COMMENT
                         comment for query's generated Go type
  --query-func-comment QUERY-FUNC-COMMENT
                         comment for query's generated Go func
  --query-delimiter QUERY-DELIMITER, -D QUERY-DELIMITER
                         delimiter for query's embedded Go parameters [default: %%]
  --query-fields QUERY-FIELDS, -Z QUERY-FIELDS
                         comma separated list of field names to scan query's results to the query's associated Go type
  --escape-all, -X       escape all names in SQL queries
  --escape-schema, -z    escape schema name in SQL queries
  --escape-table, -y     escape table names in SQL queries
  --escape-column, -x    escape column names in SQL queries
  --enable-postgres-oids
                         enable postgres oids
  --enable-postgres-json
                         Change generated code for column type Json/Jsonb types to ColumnName type and call predefined marshallers/unmarshallers.
  --name-conflict-suffix NAME-CONFLICT-SUFFIX, -w NAME-CONFLICT-SUFFIX
                         suffix to append when a name conflicts with a Go variable [default: Val]
  --template-path TEMPLATE-PATH
                         user supplied template path
  --sqlx                 adds foreign key relationship structs and query functions to generated types to use with sqlx library
  --pg-type PG-TYPE      Use types from the pgtype module. This gives better compatibility for the pgx driver for postgres. [values: <std|pointer|pgtype|pgtype-full>] [default: std]
  --nullable-proc-params Toggles nullable types for stored procedure parameters.
  --help, -h             display this help and exit
  --version              display version and exit

Using a Configuration File

A more consistent and repeatable way to run gendal throughout the life of a project is to use a configuration file. A base configuration file called gendal.toml is provided which uses the TOML language for providing configurable access to all of the command line options.

Follow these steps to use the configuration file:

Copy the gendal.toml file from the gendal repository to the root directory of your project.
Edit the DSN setting to point to the database for your project.
Edit other settings according to the the needs of your project.
Run gendal without needing to pass any command line flags.

$ gendal

If any command line options are passed then they will override the setting provided in the configuration file.

About Base Templates

gendal provides a set of generic "base" templates for each of the supported databases, but it is understood these templates are not suitable for every o

Gendal

Install / Use

README