xo

README

About xo

Project xo provides a cli tool to generate Go types and funcs based on a database schema or a custom query. xo is designed to vastly reduce the overhead/redundancy of writing (from scratch) types and funcs for common database tasks in Go.

Currently, xo can generate types for tables, enums, stored procedures, and custom SQL queries for PostgreSQL, MySQL, Oracle, Microsoft SQL Server, and SQLite databases.

Additionally, support for other database abstractions (ie, views, many-to-many relationships, etc) are in varying states of completion, and will be added as soon as they are in a usable state.

Please note that xo is NOT an ORM, nor does xo generate an ORM. Instead, xo generates Go code by using database metadata to query the types and relationships within the database, and then generates representative Go types and funcs for well-defined database relationships using raw queries.

Database Feature Support

The following is a matrix of the supported features for each database:

	PostgreSQL	MySQL	Oracle	Microsoft SQL Server	SQLite
Models	✅	✅	✅	✅	✅
Primary Keys	✅	✅	✅	✅	✅
Foreign Keys	✅	✅	✅	✅	✅
Indexes	✅	✅	✅	✅	✅
Stored Procs	✅	✅
ENUM types	✅	✅
Custom types	✅

Installation

Install goimports dependency (if not already installed):

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

Then, install in the usual way:

go get -u github.com/knq/xo

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

Quickstart

The following is a quick working example of how to use the xo:

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

# make an output directory
$ mkdir models

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

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

# generate code for a custom postgres query
$ xo 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

# or a standard go install
$ go install ./models

Command Line Options

The following are xo's arguments and options:

usage: xo [--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] [--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] 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: .xo.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
  --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
  --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
  --help, -h             display this help and exit

Example: End-to-End

Please see the booktest examples directory for full examples for each supported database on how a schema can be used with xo, and the resulting types/funcs generated for each supported database.

Example: Ignoring Fields

Sometimes you may wish to have the database manage the values of columns instead of having them managed in the generated Go code. If you are generating the Go code from an existing database, you can use the --ignore-fields flag to specify that the fields should not be managed by xo.

For instance, consider this PostgreSQL schema which defines a function and trigger to set the modified_at column automatically on UPDATE (as well as defaulting the created_at column to now() when the row is first created):

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name text NOT NULL DEFAULT '' UNIQUE,
  created_at timestamptz default now(),
  modified_at timestamptz default now(),
);

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modfified_at = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

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

We could ensure that these columns are managed by PostgreSQL and not by the generated Go code using --ignore-fields:

$ xo pgsql://user:pass@host/db -o models --ignore-fields created_at modified_at

Example: Adding Methods With Custom Templates

The Go code rendered by xo is customizable using the --template-path flag. Notice that in this source repo, templates/ points towards a default selection of templates which you might be able to use to customize the templated code to your needs. You can vendor these templates into your own project to do so. e.g.:

$ mkdir templates
$ cp -r "$GOPATH/go/src/github.com/knq/xo/templates" .
$ xo pgsql://user:pass@host/db -o models --template-path templates

The outermost template file for a generated struct is in templates/$DBTYPE.type.go.tpl. This is an excellent place to add a new method for all generated structs to have. For instance, if we have a timestamp field created_at (such as the one managed by Postgres above) we can ensure that our generated structs have a query which returns the N (or less) most recent entries in a table by adding this template:

// MostRecent will return N rows from the table sorted by recency ('created_at'
// field).
func {{ .Name }}MostRecent (db XODB, n int) ([]*{{ .Name}}, error) {
    recent := fmt.Sprintf(`SELECT {{ colnames .Fields "created_at" "modified_at" }} FROM {{ $table }} ORDER BY created_at DESC LIMIT %d`, n)
    q, err := db.Query(recent)
    if err != nil {
        return nil, err
    }
    defer q.Close()

    // load results
    res := []*{{ .Name }}{}
    for q.Next() {
        {{ $short }} := {{ .Name }}{}

        // scan
        err = q.Scan({{ fieldnames .Fields (print "&" $short) }})
        if err != nil {
            return nil, err
        }

        res = append(res, &{{ $short }})
    }

    return res, nil
}

Note that the context (.) in the template can be divined from the source code of xo, e.g. at the time of writing, . represents an instance of Type from github.com/knq/xo/internal/types.go.

You may want to consider deleting the templates for databases other than the one you are using, as well as the generated templates/templates.go "binary" file, from a vendored templates/ directory. Those are needed by xo upstream, but are most likely not needed for your project.

Oracle Support

Oracle support is disabled by default as the Go Oracle driver used by xo relies on the Oracle instantclient libs installed and available through pkg-config. If you already have rana`s Oracle driver installed, then you can simply pass the -tags oracle to go get, go install or go build in order to build/install xo with Oracle support:

$ go get -tags oracle -u github.com/knq/xo

For reference, the sath89/oracle-12c Docker image is used by the xo developers for testing Oracle database support. Additionally, there are some additional scripts available in the contrib directory that makes it significantly easier to rapidly build schemas using xo and an Oracle database.

Installing Oracle instantclient on Debian/Ubuntu

On Ubuntu/Debian, you may download the instantclient RPMs here.

You should then be able to do the following:

# install alien, if not already installed
$ sudo aptitude install alien

# install the instantclient RPMs
$ sudo alien -i oracle-instantclient-12.1-basic-*.rpm
$ sudo alien -i oracle-instantclient-12.1-devel-*.rpm
$ sudo alien -i oracle-instantclient-12.1-sqlplus-*.rpm

# get xo
$ go get -u github.com/knq/xo

# copy oci8.pc from xo/contrib to system pkg-config directory
$ sudo cp $GOPATH/src/github.com/knq/xo/contrib/oci8.pc /usr/lib/pkgconfig/

# install rana's ora driver
$ go get -u gopkg.in/rana/ora.v4

# install xo with oracle support enabled
$ go install -tags oracle github.com/knq/xo

Design, Origin, Philosophy, and History

xo can likely get you 99% "of the way there" on medium or large database schemas and 100% of the way there for small or trivial database schemas. In short, xo is a great launching point for developing standardized packages for standard database abstractions/relationships, and xo's most common use-case is indeed in a code generation pipeline, ala stringer.

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

xo was originally developed while migrating a large application written in PHP to Go. The schema in use in the original app, while well designed, had become inconsistent over multiple iterations/generations, mainly due to different naming styles adopted by various developers/database admins over the preceding years. Additionally, some components had been written in different languages (Ruby, Java) and had also accumulated significant drift from the original application and accompanying schema. Simultaneously, a large amount of growth meant that the PHP/Ruby code could no longer efficiently serve the traffic volumes.

In late 2014/early 2015, a decision was made to unify and strip out certain backend services and to fully isolate the API from the original application, allowing the various components to instead speak to a common API layer instead of directly to the database, and to build that service layer in Go.

However, unraveling the old PHP/Ruby/Java code became a large headache, as the code, the database, and the API, all had significant drift -- thus, underlying function names, fields, and API methods no longer coincided with the actual database schema, and were named differently in each language. As such, after a round of standardizing names, dropping cruft, and adding a small number of relationship changes to the schema, the various codebases were fixed to match the schema changes. After that was determined to be a success, the next target was to rewrite the backend services in Go.

In order to keep a similar and consistent workflow for the developers, the previous code generator (written in PHP and Twig templates) was modified to generate Go code. Additionally, at this time, but tangential to the story, the API definitions were ported from JSON to Protobuf to make use of its code generation abilities as well.

xo is the open source version of that code generation tool, and is the fruits of those development efforts. It is hoped that others will be able to use and expand xo to support other databases -- SQL or otherwise -- and that xo can become a common tool in any Go developer's toolbox.

Part of xo's goal is to avoid writing an ORM, or an ORM-like in Go, and to instead generate static, type-safe, fast, and idiomatic Go code across languages and databases. Additionally, the xo developers are of the opinion that relational databases should have proper, well-designed relationships and all the related definitions should reside within the database schema itself: ie, a "self-documenting" schema. xo is an end to that pursuit.

Related Projects

• dburl - a Go package providing a standard, URL style mechanism for parsing and opening database connection URLs
• usql - a universal command-line interface for SQL databases

Other Projects

The following projects work with similar concepts as xo:

Go Generators

• ModelQ
• sqlgen
• squirrel
• scaneo
• acorn and rootx [read overview here]

Go ORM-likes

• sqlc

TODO

• Completely refactor / fix code, templates, and other issues (PRIORITY #1)
• Add (finish) stored proc support for Oracle + Microsoft SQL Server
• Better standardize example gen.sh scripts, using usql tool
• Unit tests / code coverage / continuous builds for binary package releases
• Move database introspection to separate package for reuse by other Go packages
• Overhaul/standardize type parsing
• Finish support for --{incl, excl}[ude] types
• Write/publish template set for protobuf
• Add support for generating models for other languages
• Finish many-to-many and link table support
• Finish example and code for generated *Slice types
• Add example for many-to-many relationships and link tables
• Add support for supplying a file (ie, *.sql) for query generation
• Add support for full text types (tsvector, tsquery on PostgreSQL)
• Finish COMMENT support for PostgreSQL/MySQL and update templates accordingly.
• Add support for JSON types (json, jsonb on PostgreSQL, json on MySQL)
• Add support for GIN index queries (PostgreSQL)
• Add ability to read *.sql files with 'markup' to parse multiple queries (a la migration scripts) [is this even necessary?]