xo/xo

Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

xo

xo is a command-line tool to generate idiomatic code for different languages code based on a database schema or a custom query.

Installing | Building | Using | Releases

Supported languages

At the moment, xo only supports Go. Support for other languages will come soon.

How it works

In schema mode, xo connects to your database and generates code using Go templates. xo 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, xo 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 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.

In query mode, xo 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	✅	✅	✅	✅	✅
Primary Keys	✅	✅	✅	✅	✅
Foreign Keys	✅	✅	✅	✅	✅
Indexes	✅	✅	✅	✅	✅
Stored Procs	✅	✅	✅	✅	✅
Functions	✅	✅	✅	✅	✅
ENUM types	✅	✅
Custom types	✅

Installing

xo can be installed via Release, via Homebrew, via AUR, via Scoop or via Go:

Installing via Release

1. Download a release for your platform
2. Extract the xo or xo.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 xo from the xo/xo tap in the usual way with the brew command:

# install
$ brew install xo/xo/xo

Installing via AUR (Arch Linux)

Install xo from the Arch Linux AUR in the usual way with the yay command:

# install
$ yay -S xo-cli

Alternately, build and install using makepkg:

# clone package repo and make/install package
$ git clone https://aur.archlinux.org/xo-cli.git && cd xo-cli
$ makepkg -si
==> Making package: xo-cli 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 xo 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 xo with scoop
> scoop install xo

Installing via Go

Install xo in the usual Go fashion:

# install latest xo version
$ go install github.com/xo/xo@latest

Quickstart

The following is a quick overview of using xo 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)
$ xo schema postgres://user:pass@host/dbname

# Generate code from a Microsoft SQL schema using a custom template directory (see notes below)
$ mkdir -p mssqlmodels
$ xo schema mssql://user:pass@host/dbname -o mssqlmodels --src custom/templates

# Generate code from a custom SQL query for Postgres
$ xo 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 xo's command-line commands, arguments, and options:

$ xo --help-long
usage: xo [<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

xo provides a set of generic "base" 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 xo source tree, and use those with xo 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 xo (see below quickstart and related example).

Custom Template Quickstart

The following is a quick overview of copying the base templates contained in the xo project's templates/ directory, editing to suit, and using with xo:

# Create a working directory
$ mkdir -p my-tpl

# Dump an embedded template to disk
$ xo dump -t createdb my-tpl

# edit base template files
$ vi my-tpl/*.go.tpl

# see command line options for the template
$ xo schema --src my-tpl --help

# generate a schema using the custom template
$ xo 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 xo 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:

# Add to custom xo command to go generate:
$ tee -a gen.go << END
package mypackage

//go:generate xo 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 xo templates for models'

Note: via the --template/-t parameter of xo dump you can generate other templates with xo. The default template is the go template.

Template Language/Syntax

xo templates are standard Go text templates. Please see the documentation for Go's standard text/template package 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 (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/.

Template File	Type	Description
hdr.xo.*.tpl		Base template. Executed with content for a template.
db.xo.*.tpl		Package level template with base types and interface data. Generated once per package.
schema/enum.xo.*.tpl	Enum	Template for schema enum type definitions. Generates types and related methods.
schema/foreignkey.xo.*.tpl	ForeignKey	Template for foreign key relationships. Generates related method.
schema/index.xo.*.tpl	Index	Template for schema indexes. Generates related method.
schema/proc.xo.*.tpl	Proc	Template to generate functions to call defined stored procedures in the db.
schema/typedef.xo.*.tpl	Type	Template for schema table/views.
query/custom.xo.*.tpl	Query	Template for custom query execution.
query/typedef.xo.*.tpl	Type	Template for custom query's generated type.

For example, Go has templates/gotpl/schema/foreignkey.xo.go.tpl which defines the template used by xo for generating a function to get the foreign key type in Go. The templates are designed to be Database agnostic, so they are used for both PostgreSQL and Microsoft SQL the same, and all other supported database types. The template is passed a different instance of templates.ForeignKey instance (for each foreign key in a table). To get the Name field in from ForeignKey, the template can use {{ .Data.Name }}, or any other field similarly.

Examples

Example: End-to-End

Please see the booktest example for a full end-to-end example for each supported database, showcasing how to use a database schema with xo, and the resulting code generated by xo.

Additionally, please see the northwind and django for a demonstration of running xo 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 xo. As such, when you need xo 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:

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 xo by passing the --exclude or -e flag:

# Ignore special fields
$ xo schema postgres://user:pass@host/db -e users.created_at -e users.modified_at
# or, To ignore these fields in all tables
$ xo 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 xo, 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 xo Go template:

$ mkdir -p my-tpl

$ xo 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 edit our copy of the typedef.xo.go.tpl template:

$ vi templates/gotpl/schema/typedef.xo.go.tpl

And add the following templated GetMostRecent func at the end of the file:

// GetMostRecent{{ $type.Name }} returns n most recent rows from '{{ $table }}',
// ordered by "created_at" in descending order.
func GetMostRecent{{ $type.Name }}(ctx context.Context, db DB, n int) ([]*{{ $type.Name }}, error) {
    const sqlstr = `SELECT ` +
        `{{ $type.Fields "created_at" "modified_at" }}` +
        `FROM {{ $table }} ` +
        `ORDER BY created_at DESC LIMIT $1`

    rows, err := db.QueryContext(ctx, sqlstr, n)
    if err != nil {
        return nil, logerror(err)
    }
    defer rows.Close()

    // load results
    var res []*{{ $type.Name }}
    for rows.Next() {
        {{ $short }} := {{ $type.Name }}{
        {{- if $type.PrimaryKey }}
            _exists: true,
        {{ end -}}
        }
        // scan
        if err := rows.Scan({{ fieldnames $type.Fields (print "&" $short) }}); err != nil {
            return nil, logerror(err)
        }
        res = append(res, &{{ $short }})
    }
    return res, nil
}

We can then use the templates in conjunction with xo to generate our "model" code:

$ xo schema postgres://user:pass@localhost/dbname --src templates/

There will now be a GetMostRecentUsers func defined in models/user.xo.go, which can be used as follows:

db, err := dburl.Open("postgres://user:pass@localhost/dbname")
if err != nil { /* ... */ }

// retrieve 15 most recent items
mostRecentUsers, err := models.GetMostRecentUsers(context.Background(), db, 15)
if err != nil { /* ... */ }
for _, user := range users {
    log.Printf("got user: %+v", user)
}

Using SQL Drivers

Please note that the base xo templates do not import any SQL drivers. It is left for the user of xo's generated code to import the actual drivers. For reference, these are the expected drivers to use with the code generated by xo:

Database (driver)	Package
PostgreSQL (postgres)	github.com/lib/pq
SQLite3 (sqlite3)	github.com/mattn/go-sqlite3
MySQL (mysql)	github.com/go-sql-driver/mysql
Microsoft SQL Server (mssql)	github.com/microsoft/go-mssqldb
Oracle (ora)	github.com/sijms/go-ora/v2

Additionally, please see below for usage notes on specific SQL database drivers.

MySQL (mysql)

If your schema or custom query contains table or column names that need to be escaped using any of the --escape-* options, you must pass the sql_mode=ansi option to the MySQL driver:

$ xo --escape-all 'mysql://user:pass@host/?parseTime=true&sql_mode=ansi' -o models

And when opening a database connection:

db, err := dburl.Open("mysql://user:pass@host/?parseTime=true&sql_mode=ansi")

Additionally, when working with date/time column types in MySQL, one should pass the parseTime=true option to the MySQL driver:

$ xo schema 'mysql://user:pass@host/dbname?parseTime=true' -o models

And when opening a database connection:

db, err := dburl.Open("mysql://user:pass@host/dbname?parseTime=true")

SQLite3 (sqlite3)

While not required, one should specify the loc=auto option when using xo with a SQLite3 database:

$ xo schema 'file:mydatabase.sqlite3?loc=auto' -o models

And when opening a database connection:

db, err := dburl.Open("file:mydatabase.sqlite3?loc=auto")

About Primary Keys

For row inserts xo determines whether the primary key is automatically generated by the DB or must be provided by the application for the table row being inserted. For example a table that has a primary key that is also a foreign key to another table, or a table that has multiple primary keys in a many-to-many link table, it is desired that the application provide the primary key(s) for the insert rather than the DB.

xo will query the schema to determine if the database provides an automatic primary key and if the table does not provide one then it will require that the application provide the primary key for the object passed to the Insert method. Below is information on how the logic works for each database type to determine if the DB automatically provides the PK.

PostgreSQL Auto PK Logic

• Checks for a sequence that is owned by the table in question.

MySQL Auto PK Logic

• Checks for an autoincrement row in the information_schema for the table in question.

SQLite Auto PK Logic

• Checks the SQL that is used to generate the table contains the AUTOINCREMENT keyword.
• Checks that the table was created with the primary key type of INTEGER.

If either of the above conditions are satisfied then the PK is determined to be automatically provided by the DB. For the case of integer PK's when you want to override that the PK be manually provided then you can define the key type as INT instead of INTEGER, for example as in the following many-to-many link table:

  CREATE TABLE site_contacts (
  contact_id	INT NOT NULL,
  site_id	INT NOT NULL,
  PRIMARY KEY(contact_id,siteid),
  FOREIGN KEY(contact_id) REFERENCES contacts (contact_id),
  FOREIGN KEY(site_id) REFERENCES sites (site_id)
)

SQL Server Auto PK Logic

• Checks for an identity associated with one of the columns for the table in question.

Oracle Auto PK Logic

ALWAYS GENERATED types will be parsed as Auto PK types for Oracle.

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

Design

xo is NOT designed to be an ORM or to generate an ORM. Instead, xo is designed to vastly reduce the overhead/redundancy of (re-)writing types and funcs for common database queries/relationships -- it is not meant to be a "silver bullet".

History

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

Goals

Part of xo's goals 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

xo's Issues

--custom-types-package not imported.

I suppose it would be nice to add - --custom-types-package-import-line.

For example I have been generating models from my rails app and there is an Inet Type. --custom-types-package property subbed unimplemented types but failed to import the package. I can deal with unsupported types by adding a file to models directory with package 'models' which will cover unsupported types and it seems a little bit more elegant in lieu of import("appname/xocustom") ...

Multi-DBs in the same project

I know most often we should put all tables in the same DB, but I do have a case to deal with different DBs in the same project, because the content in each DB is totally irrelevant to other DBs.

How to deal with Multi-DBs in the same project? Can they co-exist in the same models folder? Or, I have to use -o to generate them into different model folders? The latter is something I want to avoid. Thx.

errors when a column name matches a go reserved keyword

If your database table has a field named "type" or some other go reserved keyword and it's part of some index, then xo will name some local variables "type" which collides with the reserved keyword.

Some examples:

// ProductsByParentIDType retrieves a row from 'phx.products' as a Product.
//
// Generated from index 'products_parent_id_type'.
func ProductsByParentIDType(db XODB, parentID sql.NullInt64, type sql.NullString) ([]*Product, error) {

// ProductsBySkuIDType retrieves a row from 'phx.products' as a Product.
//
// Generated from index 'ku_id_type'.
func ProductsBySkuIDType(db XODB, sku sql.NullString, id int, type sql.NullString) ([]*Product, error) {

Renaming the variable from "type" to some other name ("t", for example) solves the problem.

Binary release for Microsoft SQL Server

Hi,

I know Microsoft SQL Server are now supported in Linux, but I need the xo executable under Microsoft Windows. Can you build a xo binary release for Microsoft Windows please?

I tried it myself but found it is too much a mouthful for me:

...\Go\src\github.com\knq\xo>go build -v
github.com/alexflint/go-arg
github.com/knq/dburl
github.com/gedex/inflector
github.com/knq/snaker
github.com/knq/xo/models
github.com/knq/xo/templates
golang.org/x/net/context
github.com/go-sql-driver/mysql
github.com/denisenkom/go-mssqldb
github.com/lib/pq/oid
github.com/lib/pq
github.com/knq/xo/internal
github.com/mattn/go-sqlite3
# github.com/mattn/go-sqlite3
exec: "gcc": executable file not found in %PATH%

Have you ever built a xo executable under Microsoft Windows? If doing a binary release is too much a hassle, can you put the executable somewhere that I can grab please? Thanks!

Marshaling data into sql.NullString

Hi. Any suggestions on how to handle marshaling JSON data into structures created by xo?

For example, I have the following table:

create table projects (
  id varchar(40) primary key default gen_random_uuid(),
  project_name varchar(256),
  docker_image_name varchar(256),
  project_homepage text,
  project_tags text,
  project_description text,
  publisher_id varchar(40),
  featured varchar(5) default 'false'
);

xo generates this structure:

type Project struct {
    ID                 string         `json:"id"`                  // id
    ProjectName        sql.NullString `json:"project_name"`        // project_name
    DockerImageName    sql.NullString `json:"docker_image_name"`   // docker_image_name
    ProjectHomepage    sql.NullString `json:"project_homepage"`    // project_homepage
    ProjectTags        sql.NullString `json:"project_tags"`        // project_tags
    ProjectDescription sql.NullString `json:"project_description"` // project_description
    PublisherID        sql.NullString `json:"publisher_id"`        // publisher_id
    Featured           sql.NullString `json:"featured"`            // featured

    // xo fields
    _exists, _deleted bool
}

I'm trying to marshal this json data (from a POST request I'm making) into a structure:

{
  "ID": "1234",
  "ProjectName": "test projects",
  "ProjectAuthor": "odewahn",
  "GitOrigin": "www.example.com",
  "DockerhubPullCommand": "project",
  "ProjectDescription": "this is a great projects",
  "ProjectTags": "test data",
  "Featured": "false"
}

Using this command:

    var project models.Project
    err := json.Unmarshal(dat, &project)

The problem I'm having its that the Marshal doesn't seem to work with these structures, or throws an error like

json: cannot unmarshal string into Go value of type sql.NullBool

I would appreciate any ideas or suggestions!

Tables with names beginning with "q" and indexes generate invalid code

"q.Scan undefined (type Queue has no field or method Scan)"

If the table name is such that shortname is "q" that clashes with the use of "q" to hold the result of db.Query in code generated from postgres.query.go.tpl.

(Changing "q" to "_q" in a local copy of the template used with --template-path works around it easily enough.)

Naming collision with multiple foreign keys referencing one table

Tables with multiple foreign keys that point to the same foreign table result in naming collisions.

A simplified schema of what I'm working with:

CREATE TABLE "address" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "name" text NOT NULL
);
CREATE TABLE "mail" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "sender_id" integer NOT NULL,
  "recipient_id" integer NOT NULL,
  FOREIGN KEY ("recipient_id") REFERENCES "address" ("id"),
  FOREIGN KEY ("sender_id") REFERENCES "address" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE INDEX "idx_mail_recipient_id" ON "mail" ("recipient_id");
CREATE INDEX "idx_mail_sender_id" ON "mail" ("sender_id");

mail.xo.go ends up containing this:

// Address returns the Address associated with the Mail's RecipientID (recipient_id).
//
// Generated from foreign key 'mail_recipient_id_fkey'.
func (m *Mail) Address(db XODB) (*Address, error) {
    return AddressByID(db, m.RecipientID)
}

// Address returns the Address associated with the Mail's SenderID (sender_id).
//
// Generated from foreign key 'mail_sender_id_fkey'.
func (m *Mail) Address(db XODB) (*Address, error) {
    return AddressByID(db, m.SenderID)
}

I'd like to suggest naming those functions differently. How about using the column name without _id? e.g. Recipient() and Sender()? or maybe even combine it with the table name: RecipientAddress() and SenderAddress()?

Unless of course I'm missing something. I could probably copy the templates and modify them for now, but I'd rather see this fixed :)

sql scan err: []uint8 into *time.Time

Hi. I was testing out your package and either did something wrong or found a bug:

$ go run main.go
2016/05/28 12:12:26 main.go:31: sql: Scan error on column index 1: unsupported Scan, storing driver.Value type []uint8 into type *time.Time
exit status 1

To reproduce, use the following mysql schema:

CREATE TABLE `some_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `some_int` int(11) NOT NULL,
  `some_nullable_int` int(11) DEFAULT NULL,
  `some_string` varchar(100) NOT NULL,
  `some_nullable_string` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8;

Generate the schema: xo mysql://root@localhost/allthethings -o models

and run the following main (note, obviously use your own model dir):

package main

import (
    "database/sql"
    "log"

    _ "github.com/go-sql-driver/mysql"
    "< PATH TO MODEL>"
)

func main() {
    log.SetFlags(log.LstdFlags | log.Lshortfile)

    db, err := sql.Open("mysql", "root@tcp(localhost:3306)/allthethings")
    if err != nil {
        log.Fatal(err)
    }
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    st := models.SomeTable{SomeString: "foo"}
    err = st.Insert(db)
    if err != nil {
        log.Fatal(err)
    }

        // the following line errors
    results, err := models.SomeTablesBySomeString(db, "foo")
    if err != nil {
        log.Fatal(err)
    }

    for _, r := range results {
        log.Printf("Found id %d", r.ID)
    }
    log.Println("complete")
}

The error happens at results, err := models.SomeTablesBySomeString(db, "foo").

Insert function assumes primary key is auto increment

The code generated for the Insert() function assumes that that the primary key is auto increment and does not insert it and uses LastInsertId to set it.

MySQL driver selects int16 for MEDIUMINT type

Should use int32 instead

Tests do not use transaction

Hi, it seems that test do not use created transactions.
You have:
tx, err := db.Begin()
and later on
err = b0.Save(db)
instead of
err = b0.Save(tx)

Also, there should be a defered rollback i suppose.

error: Error 1242: Subquery returns more than 1 row

I get the error described in the header when I try to run xo against my MySQL schema. Is there any flags/env vars to print debug info, e.g. so I could finding out which query that crashes it?

I found:
http://stackoverflow.com/questions/12597620/1242-subquery-returns-more-than-1-row-mysql
which might be related to the issue.

errors generating schema_migration model

This is an easy one to report and, well, one that I can ignore pretty easily, but will impede Rails projects wanting to us xo. All Rails projects have a schema_migration table. Here's the MySQL description:

mysql> describe schema_migrations;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| version | varchar(255) | NO   | PRI | NULL    |       |
+---------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> show index from schema_migrations;
+-------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table             | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| schema_migrations |          0 | unique_schema_migrations |            1 | version     | A         |         661 |     NULL | NULL   |      | BTREE      |         |               |
+-------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

And here is the error go fmt gives on the output:

$ go fmt schemamigration.xo.go 
schemamigration.xo.go:93:16: expected operand, found ','
schemamigration.xo.go:95:2: missing ',' in argument list
schemamigration.xo.go:96:1: expected operand, found '}'
schemamigration.xo.go:100:2: missing ',' in argument list
schemamigration.xo.go:101:3: expected operand, found 'return'
schemamigration.xo.go:102:2: expected operand, found '}'
schemamigration.xo.go:104:2: missing ',' in argument list
schemamigration.xo.go:105:1: expected operand, found '}'
schemamigration.xo.go:109:2: missing ',' in argument list
schemamigration.xo.go:112:2: missing ',' in argument list
schemamigration.xo.go:113:3: expected operand, found 'return'

schemamigration.xo.go.zip

Table fields with names that match methods

In the event that fields match methods the generator should prefix struct fields with Is or something similar.

error: sql: Scan error on column index 0: unsupported driver -> Scan pair: <nil> -> *string

Using MSSQL Driver from https://github.com/denisenkom/go-mssqldb.git

Getting this trying this from CLI.

xo mssql://user:pass@host:1433/db -o models -N -M -B -T Match << ENDSQL
select * from match ;
ENDSQL

error: sql: Scan error on column index 0: unsupported driver -> Scan pair: -> *string

Is there another driver I should be using?

Thanks.

Check exist on index

Any plan to add checking exist on index in xo?

Other than returning all rolls for an index (feature generated by xo), many times, people just want to checking the existence of the index key, and count maybe as well.

Any plan to add generated code for it/them? Thx.

Pointer to time.Time when column is not null

Hi, I have a question.

Why is this library using *time.Time for not null column? It seems like pq.NullTime is for nullable time column when transforming table to struct.

pg stored procedures returning trigger generate code with undefined type Trigger

CREATE OR REPLACE FUNCTION public.fake_trigger(
) RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RETURN NEW;
END;
$$;

generates:

// FakeTrigger calls the stored procedure 'public.fake_trigger() trigger' on db.
func FakeTrigger(db XODB) (Trigger, error) {
  var err error

  // sql query
  const sqlstr = `SELECT public.fake_trigger()`

  // run query
  var ret Trigger
  XOLog(sqlstr)
  err = db.QueryRow(sqlstr).Scan(&ret)
  if err != nil {
    return Trigger{}, err
  }

  return ret, nil
}

which fails to build with models/sp_faketrigger.xo.go:7: undefined: Trigger

Fails to generate <table>ByID methods for some tables

Let me start by saying that I'm thrilled with the work being done on this utility.
I see XO as being an integral part of bootstrapping web applications written in GO,
and I'd love to see how it continues to grow.
Unfortunately I'm having a problem.
I've got a schema for MySQL 5.5.46 (which I've attached),
which had fallen victim to Issue #3
I went through and removed indexes until XO was able to run completely.
The generated code had a couple issues.
First, some of the (table)ByID methods were defined like this:

func ArtistByID(db XODB, iD int64, iD int64, iD int, iD int64, iD int64, iD int64, iD int64, iD int64)(*Artist, error) {...}

There were also a couple of tables which didn't have a (table)ByID method generated.

I haven't really dug into this, I will when I have a chance tomorrow.
db_init.txt

Excluding the schema from the generated SQL

Context: we have several instances of the same database schema, under different schema names in a single MySQL installation. We would like to be able to switch between them just by changing the schema in the connection string we pass to our binary in a flag.

Problem: This doesn't seem possible with xo at the moment because it adds the schema to all table and proc names in the generated code. Switching between different schemas requires re-generating the xo code and re-compiling.

Possible solution?: New flag to disable schema concatenation in internal.schmafn. More than happy to send a pull request if this sounds reasonable.

Current: UPDATE dev.User SET
Desired: UPDATE User SET

Support sql.ErrNoRows

Since this is a model between the database and the application it would make sense for XO to add a flag option for adding for sql.ErrNoRows to models which return a single struct.

This is a relatively simple fix (using a marker in the Template) which changes

err = db.QueryRow(sqlstr, x).Scan(....)
if err != nil {
	return nil, err
}

Into this:

err = db.QueryRow(sqlstr, x).Scan(....)
if err != nil {

	// there was no row, but otherwise no error occurred
	if err == sql.ErrNoRows {
		return nil, nil
	}

	return nil, err
}

Then the application can just check that they get something back (if Foo != nil) rather than needing to add this additional check every place the model is used.

Database name ending with 's'

Hi, just a quick question: why does xo truncates the db name when it ends with an 's'.
e.g. db name: sms --> sm.xo file

Is there any specific reason for this? And is there a way to force xo to keep the 's'?

Thank you!

generated postgres json datatype undefined

i generated a model from a postgres db. the struct had a line that looked similar to this:
MyColumnText JSON json:"my_column_text" // my_column_text

but when building the application JSON was undefined. is there an expectation that i would have already handled this type on my own or am i missing something with regard to using xo?

not sure what to do next error in mysql.type.go.tpl

Running xo with mysql:// and I got the following error (this is the entire error):

error: template: mysql.type.go.tpl:1: illegal number syntax: "-"

I'm not familiar with Go templates, so I'm at a loss for debugging. Any suggestions?

(BTW, I'm super pumped for this project. It looks like exactly what I want.)

High level schema design

Viewing from a very high level, what xo currently does, is basically reverse-engine physical designs from the database to generate code.

What I hope is, someday xo can start not from reverse-engine physical databases, but from schema high level designs.

Take this ER diagram for example (taken from http://symfony.com/legacy/doc/askeet/1_0/en/2),

It can be expressed in high level in something like this (taken from http://symfony.com/legacy/doc/askeet/1_0/en/2):

propel:
  _attributes:   { noXsd: false, defaultIdMethod: none, package: lib.model }

  ask_question:
    _attributes: { phpName: Question, idMethod: native }
    id:          { type: integer, required: true, primaryKey: true, autoIncrement: true }
    user_id:     { type: integer, foreignTable: ask_user, foreignReference: id }
    title:       { type: longvarchar }
    body:        { type: longvarchar }
    created_at:  ~
    updated_at:  ~

  ask_answer:
    _attributes: { phpName: Answer, idMethod: native }
    id:          { type: integer, required: true, primaryKey: true, autoIncrement: true }
    question_id: { type: integer, foreignTable: ask_question, foreignReference: id }
    user_id:     { type: integer, foreignTable: ask_user, foreignReference: id }
    body:        { type: longvarchar }
    created_at:  ~

  ask_user:
    _attributes: { phpName: User, idMethod: native }
    id:          { type: integer, required: true, primaryKey: true, autoIncrement: true }
    nickname:    { type: varchar(50), required: true, index: true }
    first_name:  varchar(100)
    last_name:   varchar(100)
    created_at:  ~

  ask_interest:
    _attributes: { phpName: Interest, idMethod: native }
    question_id: { type: integer, foreignTable: ask_question, foreignReference: id, primaryKey: true }
    user_id:     { type: integer, foreignTable: ask_user, foreignReference: id, primaryKey: true }
    created_at:  ~

  ask_relevancy:
    _attributes: { phpName: Relevancy, idMethod: native }
    answer_id:   { type: integer, foreignTable: ask_answer, foreignReference: id, primaryKey: true }
    user_id:     { type: integer, foreignTable: ask_user, foreignReference: id, primaryKey: true }
    score:       { type: integer }
    created_at:  ~

The syntax details is irrelevant, but my point is that YAML can express the tables, PK, FK, and relationships very clearly, in very high level. The physical design, and implementation details should be left to the code gen.

Just a wish.

Blocking empty string values when columns are null?

Go defines the zero value for strings to be an empty string, so there is no way to test if the value has been set to empty, or was simply empty from the beginning without tracking each set on the struct properties.

How should we handle columns that do not allow null values, but should also not allow empty values for structs that have strings as the column type?

type Person struct {
	ID     int64           `json:"id"`     // id
	Name string          `json:"name"` // name
	_exists, _deleted bool
}

...
	p = models.Person{}
	err = p.Save(db) // <-- Should not have worked since struct is empty and person.name does not allow null values

	if err != nil {
		fmt.Println(err)
	}
	fmt.Println("Person.ID, p.ID) 

I can manually edit all the models but surely more people need "" == nil in this case than not.
See also sql.NullString.

Missing functions for tables using inheritance (postgresql)

I've only tested this with PostgreSQL (9.5), so I don't know if this affects other DBs.

When I have a schema of:

CREATE TABLE parent (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL);

CREATE TABLE child (
blah TEXT;
) INHERITS (parent);

When using xo to generate code, it generates Insert/Update/Delete/Upsert/etc functions for "parent" table, but not for "child" table. Is there a way to force this? At the moment, I have to copy, paste and modify the parent functions to use in child.xo.go

documenting with graph

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 -- call it "self-documenting" schema. xo is an end to that pursuit

Totally agree. However, I hope thing will not ends there, but bring the documenting to the next level -- using E-R diagrams. Since xo has already had a clear view of the table, PK, FK and their relationships, dumping out such relationships should not be too difficult.

And I hope the Dot format from GraphViz is one of the output format. It can be more beautiful than people normally think -- check out https://github.com/BurntSushi/erd for example.

thanks

TitleCase names with characters that are Invalid:In:Go:Identifiers

I have a target database with column and table table with colons and the generated types look like:

// Account:order represents a row from public.account:order.
type Account:order struct {
    Session:id string // session:id
    Session:type sql.NullString // session:type
    Session:brandSlug string // session:brand_slug
    Session:created sql.NullInt64 // session:created
    Event:id string // event:id
    ...
}

One idea would be to title case the identifiers using illegal characters as word boundaries then using struct tags to indicate the column names and providing a constant for the table name:

const AccountOrderTable = "account:order"

type AccountOrder struct {
    SessionId string `xo:"session:id"`
    SessionType sql.NullString `xo:"session:type"`
    SessionBrandSlug string `xo:"session:brand_slug"`
    SessionCreated sql.NullInt64 `xo:"session:created"`
    EventId string `xo:"event:id"`
    ...
}

Comment support

Hey @kenshaw. I'm keen to add support for Postgres comments on tables and columns etc. I see the readme has

Finish COMMENT support for PostgreSQL/MySQL and update templates accordingly.

but I can't find any the existing work on the feature.

Has this been started? If not, I'd like to take a crack at it if you're happy to look at a PR for this.

Thanks for the great tool!

struct method and property with same name when foreign key used

Given the following schema, a struct is created with a method name matching a property name.

CREATE TABLE `fragment` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`fragment` VARCHAR(100) NOT NULL,
`fragment_id` BIGINT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE `fragment` ADD CONSTRAINT `fragment_fragment_unique` UNIQUE (`fragment`);
ALTER TABLE `fragment` ADD CONSTRAINT `fragment_fragment_id_fk` FOREIGN KEY (`fragment_id`) REFERENCES `fragment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Apparently it's the foreign key to the table of the same name.

// Fragment represents a row from 'dbname.fragment'.
type Fragment struct {
    ID         int64  `json:"id"`          // id
    Fragment   string `json:"fragment"`    // fragment
    FragmentID int64  `json:"fragment_id"` // fragment_id

    // xo fields
    _exists, _deleted bool
}

// Fragment returns the Fragment associated with the Fragment's FragmentID (fragment_id).
//
// Generated from foreign key 'fragment_fragment_id_fk'.
func (f *Fragment) Fragment(db XODB) (*Fragment, error) {
    return FragmentByID(db, f.FragmentID)
}

Perhaps rather than adding logic for this, there should just be a warning about columns that match the name of the table when using foreign keys. Doesn't seem like a common use case.

Question: many-to-many and link table support

Hello There,

Fantastic tool. Thank you so much for your effort. This is exactly what I needed.

I looked around, however could not find any further information on many-to-many and link table support. I was wondering if there is an early version I could play around with? This along with Postgres JSON support and an input query file for query generation are few of my biggest needs. Would deeply appreciate any guidance on when these may drop.

Thanks

Naming collision with field_name == other_table_name

Similar to #14 . Identical?

The naming collision occurs although if one field is named after another table.

CREATE TABLE account (
    id BIGSERIAL PRIMARY KEY,
    name text UNIQUE NOT NULL
);

CREATE TABLE session (
    id BIGSERIAL PRIMARY KEY,
    account bigint REFERENCES "account" (id) NOT NULL
);

The SQL above generates:

type Session struct {
        ID      int64 `json:"id"`      // id
        Account int64 `json:"account"` // account
...
}

func (s *Session) Account(db XODB) (*Account, error) {
        return AccountByID(db, s.Account)
}

This leads to a collision: models/session.xo.go:160: type Session has both field and method named Account

Is it possible to detect this collisions and handle them?
Like --fk-mode field does?
This leads to usable code.

-// Account returns the Account associated with the Session's Account (account).
+// AccountByAccount returns the Account associated with the Session's Account (account).
 //
 // Generated from foreign key 'session_account_fkey'.
-func (s *Session) Account(db XODB) (*Account, error) {
+func (s *Session) AccountByAccount(db XODB) (*Account, error) {
    return AccountByID(db, s.Account)
 }

Enum name collisions with mysql

When two tables in a MySQL database have enum columns with the same name, the created constants might colide.

This can be fixed by using the following queries.
In MyEnums:

	const sqlstr = `SELECT ` +
		`DISTINCT CONCAT("enum_", table_name, "_", column_name) AS enum_name ` +
		`FROM information_schema.columns ` +
		`WHERE data_type = 'enum' AND table_schema = ?`

In MyEnumValue:

	const sqlstr = `SELECT ` +
		`SUBSTRING(column_type, 6, CHAR_LENGTH(column_type) - 6) AS enum_values ` +
		`FROM information_schema.columns ` +
		`WHERE data_type = 'enum' AND table_schema = ? AND CONCAT("enum_", table_name, "_", column_name) = ?`

This will avoid colissions between enums and enums as well as enums and tables

Uppercase data type affects code generation

Using sqlite the following schema

CREATE TABLE authors (
  author_id integer NOT NULL PRIMARY KEY,
  name text NOT NULL DEFAULT ''
);

results in

type Author struct {
    AuthorID int    // author_id
    Name     string // name

    // xo fields
    _exists, _deleted bool
}

while

CREATE TABLE authors (
  author_id INTEGER NOT NULL PRIMARY KEY,
  name text NOT NULL DEFAULT ''
);

results in

type Author struct {
    AuthorID string // author_id
    Name     string // name

    // xo fields
    _exists, _deleted bool
}

Postgres UUID Types.

Sorry to open another issue while we are still working on the first, but I wanted to mention something I noticed as well.

I generated the models without issue using "xo"

I noticed it created this "UUID" type.
But I don't see it imported???

Not sure if this is by design or my mistake.

Thanks.

package models

// GENERATED BY XO. DO NOT EDIT.

import (
"database/sql"
"errors"
)

// VUser represents a row from public.v_users.
type VUser struct {
UserUUID UUID // user_uuid
DomainUUID UUID // domain_uuid
Username sql.NullString // username
Password sql.NullString // password
Salt sql.NullString // salt
ContactUUID UUID // contact_uuid
UserStatus sql.NullString // user_status
APIKey UUID // api_key
UserEnabled sql.NullString // user_enabled
AddUser sql.NullString // add_user
AddDate sql.NullString // add_date

    // xo fields
    _exists, _deleted bool

}

error: exit status 2

I'm getting

error: exit status 2

when running xo now under go 1.6.

It appears to be generating all the models (this is a huge DB, there are 192 model files), but I'm not sure what the error is and if I should be worried or not. Running with -v shows this at the very end:

SQL:
SELECT seq_in_index AS seq_no, column_name FROM information_schema.statistics WHERE index_schema = ? AND table_name = ? AND index_name = ? ORDER BY eq_in_index
PARAMS:
[phoenix companies_before_add_refund_cols_to_companies idx_companies_on_updated_on]

error: exit status 2

Thank you.

sqlite3 generator does not handle table constraints

Here is my test schema

CREATE TABLE user (
user_id TEXT NON NULL PRIMARY KEY,
email TEXT,
first_name TEXT,
last_name TEXT,
headline TEXT,
profile_url TEXT,
location TEXT,
username TEXT,
password TEXT
);

CREATE TABLE id_provider
(
provider_id TEXT NOT NULL,
user_id TEXT NOT NULL REFERENCES user( user_id ),
network TEXT,

PRIMARY KEY( provider_id, user_id )

);

If I remove the PRIMARY KEY table constraint it generates fine. With it the following error is generated

error: sql: Scan error on column index 5: sql/driver: couldn't convert 2 into type bool

Would it be possible to support composite primary keys? I need them for the project I'm working on and would like to use xo to save me some time. If not is there some sort of work around where I can still generate most of code using xo.

Regards and thanks for making this work public.

Multiple schemas with fk in postgres

Hi, I'm having problems generating code for a database with multiple schemas. Consider the following example:

CREATE TABLE asset(
  id SERIAL PRIMARY KEY,
  name VARCHAR(10)
);

CREATE SCHEMA clientx;

CREATE TABLE clientx.asset(
  asset_id INT REFERENCES asset,
  special_property BOOLEAN
);

We use this pattern to keep additional fields for some customers.

I ran xo using the flag -s twice (xo $PGURL -s public -s clientx -o .), but I got error: could not find col, refTpl, or refCol. Is this supported?

BTW: very useful project :)

PostgreSQL functional indexes

The entry in pg_index for a postgresql functional index has indkey=0, leading to NULL results from the PgIndexColumns query:

error: sql: Scan error on column index 1: converting driver.Value type <nil> ("<nil>") to a int: invalid syntax

Adding i.indkey <> '0' AND to the where clause in both PgIndexColumns and PgTableIndexes seems to be the right fix.

(I'd do this as a pull request, but I don't have databases in place to test the go generate step).

Getting error when running xo

I am getting the following error when I run xo. This is on PostgreSQL 9.5.3

error: could not find public.safc_illness_injury_cause index safc_illness_injury_cause_ux1 column id 0

Here is my table structure from pgAdmin

`CREATE TABLE public.safc_illness_injury_cause
(
pk integer NOT NULL DEFAULT nextval('safc_illness_injury_cause_pk_seq'::regclass),
company_pk integer NOT NULL,
illness_injury_cause character varying NOT NULL,
created_on timestamp with time zone,
updated_on timestamp with time zone,
created_by text,
updated_by text,
CONSTRAINT safc_illness_injury_cause_pkey PRIMARY KEY (pk),
CONSTRAINT safc_illness_injury_cause_company_pk_fkey FOREIGN KEY (company_pk)
REFERENCES public.app_company (pk) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.safc_illness_injury_cause
OWNER TO mana;

CREATE INDEX safc_illness_injury_cause_ix1
ON public.safc_illness_injury_cause
USING btree
(company_pk);

CREATE UNIQUE INDEX safc_illness_injury_cause_ux1
ON public.safc_illness_injury_cause
USING btree
(company_pk, lower(illness_injury_cause::text) COLLATE pg_catalog."default");

CREATE TRIGGER trgb_safc_illness_injury_cause_timestamp
BEFORE INSERT OR UPDATE
ON public.safc_illness_injury_cause
FOR EACH ROW
EXECUTE PROCEDURE public.fn_trigb_update_timestamp();`

Thanks.

pg stored procedures returning void generate code with undefined type Void

CREATE OR REPLACE FUNCTION public.void_return(
) RETURNS void LANGUAGE plpgsql STRICT AS $$
BEGIN
  RAISE NOTICE 'this function returns nothing';
END;
$$;

generates:

// VoidReturn calls the stored procedure 'public.void_return() void' on db.
func VoidReturn(db XODB) (Void, error) {
  var err error

  // sql query
  const sqlstr = `SELECT public.void_return()`

  // run query
  var ret Void
  XOLog(sqlstr)
  err = db.QueryRow(sqlstr).Scan(&ret)
  if err != nil {
    return Void{}, err
  }

  return ret, nil
}

which fails to build with: models/sp_voidreturn.xo.go:7: undefined: Void

Is there an option to automatically include created/updated datetime fields on generated types?

I looked through the command line options and I didn't see this feature, is this something that is implemented or that you plan to implement? If not, would you consider accepting a pull request with this functionality?

Scan error on column : converting driver.Value type time.Time to a int64

xo generate the following postgres types as int64:

• “time with time zone"
• “time without time zone"
• “timestamp without time zone"

This behaviour will rise the following error:

sql: Scan error on column index 0: converting driver.Value type time.Time ("2016-09-17 15:35:37.816458 +0000 +0000") to a int64

Based on github.com/lib/pq driver, those should be of type time.time or pq.NullTime:

Test:

func main() {
    db, err := sql.Open("postgres", "dbname=test01 user=postgres  host=localhost sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }

    pgtypes := []string{
        "date",
        "timestamp without time zone",
        "timestamp with time zone",
        "time without time zone",
        "time with time zone"}
    for _, t := range pgtypes {
        v := new(interface{})
        rows, _ := db.Query(fmt.Sprintf("SELECT $1::%v", t), "2013-01-04 20:14:58.80033")
        // row.Scan(v)
        rows.Next()
        c, _ := rows.Columns()
        rows.Scan(v)

        fmt.Printf("Go: %T\tPG: %-30v PG-Short: %v\n", *v, t, c[0])
    }
}

Output:

Go: time.Time   PG: date                           PG-Short: date
Go: time.Time   PG: timestamp without time zone    PG-Short: timestamp
Go: time.Time   PG: timestamp with time zone       PG-Short: timestamptz                                                                                   
Go: time.Time   PG: time without time zone         PG-Short: time
Go: time.Time   PG: time with time zone            PG-Short: timetz

This fixed the issue for me:

diff --git a/loaders/postgres.go b/loaders/postgres.go
index a027c99..7cade4f 100644
--- a/loaders/postgres.go
+++ b/loaders/postgres.go
@@ -151,20 +151,13 @@ func PgParseType(args *internal.ArgType, dt string, nullable bool) (int, string,
                typ = "byte"

        case "date", "timestamp with time zone":
+       case "time with time zone", "time without time zone", "timestamp without time zone":
                typ = "*time.Time"
                if nullable {
                        nilVal = "pq.NullTime{}"
                        typ = "pq.NullTime"
                }

-       case "time with time zone", "time without time zone", "timestamp without time zone":
-               nilVal = "0"
-               typ = "int64"
-               if nullable {
-                       nilVal = "sql.NullInt64{}"
-                       typ = "sql.NullInt64"
-               }
-
        case "interval":
                typ = "*time.Duration"

Influence generated enum type names?

In the example you have this:

const (
    // FictionBookType is the book_type for 'FICTION'.
    FictionBookType = BookType(1)

    // NonfictionBookType is the book_type for 'NONFICTION'.
    NonfictionBookType = BookType(2)
)

But I prefer the generated names to be:

const (
    // BookTypeFiction is the book_type for 'FICTION'.
    BookTypeFiction = BookType(1)

    // BookTypeNonfiction is the book_type for 'NONFICTION'.
    BookTypeNonfiction = BookType(2)
)

I think this will make them easier to find through an IDE. And more in line with go std lib like https://golang.org/src/net/http/status.go

Is this possible?

Broken code emited for table called time

Whene a table is called time, a type Time is created and all timerelated columns are mapped to it. For example. For example:

 CREATE TABLE `times` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `start` time DEFAULT NULL,
  `end` time DEFAULT NULL,
  `project` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `txt` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=648 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

produces:

type Time struct {
	ID      int            `json:"id"`      // id
	Date    *time.Time `json:"date"`    // date
	Start   Time           `json:"start"`   // start
	End     Time           `json:"end"`     // end
	Project sql.NullString `json:"project"` // project
	Txt     sql.NullString `json:"txt"`     // txt

	// xo fields
	_exists, _deleted bool
}

Start and End should be *mysql.NullTime or *time.Time in case of NOT NULL.

Database generated "Created" and "LastModified" column Values

This library looks really promising, thanks for sharing it.

How should we should handle columns holding timestamps that are maintained within the database, e.g:

CREATE OR REPLACE FUNCTION "Update_Modified_Column"() RETURNS TRIGGER AS '
BEGIN
NEW."Modified" = NOW();
RETURN NEW;
END;
' LANGUAGE 'plpgsql'

CREATE TABLE "MyTable" (
"ID" SERIAL PRIMARY KEY NOT NULL,
"SomeText" TEXT,
"Created" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
"Modified" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
)

CREATE TRIGGER "TR_MyTable_Update_Modified_Column" BEFORE UPDATE
ON "MyTable" FOR EACH ROW EXECUTE PROCEDURE
"Update_Modified_Column"()

In this case, I would prefer that the code generated by xo never inserted/update values in columns "Modified" or "Created". E.g. something like this would work:
--never-update "Modified","Created"

Fails on initialization with Go 1.5.1

Running xo and/or any of the examples yields the following traceback:

panic: template: postgres.idx.go.tpl:16: illegal number syntax: "-"

goroutine 1 [running]:
text/template.Must(0x0, 0x688028, 0xc82016e220, 0x0)
    /usr/local/Cellar/go/1.5.1/libexec/src/text/template/helper.go:23 +0x4b
github.com/knq/xo/templates.init.1()
    /go/src/github.com/knq/xo/templates/templates.go:253 +0x36c
github.com/knq/xo/templates.init()
    /go/src/github.com/knq/xo/templates/types.go:72 +0x112a
github.com/knq/xo/loaders.init()
    /go/src/github.com/knq/xo/loaders/postgres.go:720 +0x79
main.init()
    /go/src/github.com/knq/xo/xo.go:271 +0x7e

goroutine 17 [syscall, locked to thread]:
runtime.goexit()
    /usr/local/Cellar/go/1.5.1/libexec/src/runtime/asm_amd64.s:1696 +0x1

Is this dependent on new text/template features in Go 1.6?

mssql: Invalid object name 'booktest.authors'

I use xo executable under Linux to access Microsoft SQL Server under Microsoft Windows and generated the models just fine. Just that the tables are named booktest. something to dbo. something. E.g., in author.xo.go, here are part of the changes:

@@ -1,2 +1,2 @@
-// Package models contains the types for schema 'booktest'.
+// Package models contains the types for schema 'dbo'.
 package models
@@ -7,3 +7,3 @@
 
-// Author represents a row from 'booktest.authors'.
+// Author represents a row from 'dbo.authors'.
 type Author struct {
@@ -36,3 +36,3 @@
        // sql query
-       const sqlstr = `INSERT INTO booktest.authors (` +
+       const sqlstr = `INSERT INTO dbo.authors (` +
                `name` +
@@ -77,3 +77,3 @@
        // sql query
-       const sqlstr = `UPDATE booktest.authors SET ` +
+       const sqlstr = `UPDATE dbo.authors SET ` +

This is more correct for Microsoft SQL Server, because in it, to access a table, it should normal be booktest.dbo.authors, or booktest..authors, or dbo.authors.

However, when run the built code from the generated the models, I get the error of:

2017/01/05 11:46:47 mssql: Invalid object name 'booktest.authors'.

This is strange because I don't know where the booktest.authors is coming from:

$ grep -ir authors *
models/authorbookresult.xo.go:          `JOIN authors a ON a.author_id = b.author_id ` +
models/author.xo.go:// Author represents a row from 'dbo.authors'.
models/author.xo.go:    const sqlstr = `INSERT INTO dbo.authors (` +
models/author.xo.go:    const sqlstr = `UPDATE dbo.authors SET ` +
models/author.xo.go:    const sqlstr = `DELETE FROM dbo.authors WHERE author_id = $1`
models/author.xo.go:// AuthorByAuthorID retrieves a row from 'dbo.authors' as a Author.
models/author.xo.go:// Generated from index 'PK__authors__86516BCF3EE7ECFC'.
models/author.xo.go:            `FROM dbo.authors ` +
models/author.xo.go:// AuthorsByName retrieves a row from 'dbo.authors' as a Author.
models/author.xo.go:// Generated from index 'authors_name_idx'.
models/author.xo.go:func AuthorsByName(db XODB, name string) ([]*Author, error) {
models/author.xo.go:            `FROM dbo.authors ` +

I.e., no where in my code under mssql folder has the booktest.authors string and all of them are of form dbo.authors.

What's wrong? Thx.

docs - warn go 1.6 is a requirement to build

to use the new template trimming functionality