xo: command line Go code generation tool for SQL databases

[Kenneth Shaw]

I wanted to post an update on xo, the command line Go code generation tool for SQL. It is available here: https://github.com/knq/xo

Alternatively, install via the usual Go way:

    go get -u github.com/knq/xo

xo now supports (out of the box) PostgreSQL, MySQL (and its variants), Oracle, and SQLite databases. xo generates high quality, idiomatic Go code for your database schema that is type-safe and properly parameterized and relies only on standard packages (ie, the various SQL drivers and database/sql). After a fairly significant rewrite, xo is now very robust, and will become more feature complete in the following weeks. xo is not an ORM, or an ORM-like. Instead, xo takes the approach that database schemas should be self-contained, and that any code accessing that schema should, for the most part, be static in nature, taking advantage of the database's indexes, relationships, and other aspects of the schema that are already well-defined.

xo has two major operation modes: schema generation and custom query inspection:

When loading a database schema, xo handles all the heavy lifting of generating types (structs) and func's that handle the most common tasks that would be associated with your schema, such as loading rows based off indexes, foreign or unique keys, and more. When inspecting a custom query, xo correctly inspects the types from the raw SQL query using the target database schema and translates those into fully type-safe parameterized Go code. Additionally, all of this generated code is in native Go templates that you can easily copy and modify in order to generate your own variants, unique to how your schema, and/or to creating templated policies or business logic used by your organization (for instance, you could template various business rules/controls common across your schema).

In the future, there are plans to create a set of templates to generate Google protobuf's relevant/proper for your schema, as well as further inspection of the underlying schema to generate more idiomatic, type-safe code.

Here's an example of how xo is used:

    # generate a schema for a postgres database, placing the generated code in the models directory:

    xo pgsql://user:pass@host/dbname -o models

    # inspect a custom mysql query (xo generates the associated type and func), placing generated code in the my directory:

    xo mysql://user:pass@host/dbname -o my -Q 'select schema() as schema, current_user() as current_user' -T MyCustomQueryType

The above works, as one might expect for PostgreSQL, MySQL, Oracle, and SQLite.

Comments / questions / feature requests would be appreciated, as well as those using it against custom schemas and reporting any issues/problems that are encountered.

Thanks!

-Ken

[Sean Russell]

Hi Ken,

Thanks for this; I am a huge fan of this approach, and I really like the section of your README titled "Design, Origin, Philosophy, and History." Thanks.

You've consipicuously avoided mentioning NoSQL in the README, but the question is inevitible and so I'll ask it: do you have any roadmap plans to add support for non-SQL DBs? If not, why not?

Thanks again.

--- SER

[Kenneth Shaw]

Hi Sean,

I don't think I "conspicuously" avoided NoSQL. NoSQL just operates differently than relational, SQL databases do. xo uses the databases' internal schema (pg_catalog in postgres, information_schema on mysql, etc) to query the relationships and generate the types/funcs associated with it. I actually was thinking of adding support for MongoDB and possibly Cassandra (as I believe they have equivalents to "information_schema"), but I don't know how useful this would actually be to anyone, as part of the flexibility in using "NoSQL" is not conforming to a schema.

To be honest, xo at the moment is not very feature complete. The internal tool I've been writing xo to replace has a lot more functionality and generates a lot more code for relational databases, however it is written in PHP/Twig (but generating Go code!) and only supports Oracle. I wanted xo to be a generic tool and support practically all the same features on all the databases it supports. Because of the different storage models between relational and non-relational databases, this wouldn't be possible. The hard part is finally finished, which is that PostgreSQL, MySQL, Oracle, and SQLite are all supported and work in more or less the same manner. Now getting xo to produce the same generated code as the internal tool is simply a matter of adding to the existing templates. 

Additionally, I don't believe there are any NoSQL databases that provide a database/sql driver for Go, but feel free to correct me if I'm wrong.

[Sean Russell]

Hi Kenneth,

On Sunday, February 28, 2016 at 6:50:40 PM UTC-5, Kenneth Shaw wrote:

I don't think I "conspicuously" avoided NoSQL. NoSQL just operates differently than relational, SQL databases do. xo uses the databases' internal schema

Fair enough; by "conspicuous," I meant by absence -- there's no mention of NoSQL plans even if that plan is "never."

 

(pg_catalog in postgres, information_schema on mysql, etc) to query the relationships and generate the types/funcs associated with it. I actually was thinking of adding support for MongoDB and possibly Cassandra (as I believe they have equivalents to "information_schema"), but I don't know how useful this would actually be to anyone, as part of the flexibility in using "NoSQL" is not conforming to a schema.

I understand.

From my perspective, the benefits to the developer for extending to non-SQL DBs (whether they be graph, NoSQL, or simple key/value stores) would be the same as for SQL databases: it provides an abstraction layer.  Such a library provides value in a common persistence layer that allows standardization without forcing homogeneity, and makes migrations easier.

I agree with what you're saying about diminished returns for NoSQL, and I agree that the schema-less aspect of NoSQL removes a lot of pain.  I'm not convinced that avoiding schema issues is where the most of the value in this tool is, though. Code generation for a persistence layer essentially removes a lot of boiler-plate for storage.  Back in the 80's, there was an adage that 80% of programming is I/O. Accurate or not, I think there's still potential value in having a workflow that essentially enforces data storage encapsulation.

To be honest, xo at the moment is not very feature complete. The internal tool I've been writing xo to replace has a lot more functionality and generates a lot

Thanks for the contribution, in any case!

--- SER 

[lipeng xiao]

Have a look at https://github.com/xormplus/xorm

A really cool ORM

在 2016年2月28日星期日 UTC+8下午12:04:33，Kenneth Shaw写道：

[Jorge Riquelme]

Hi Kenneth, nice tool. I've been doing this "by hand". I'll give a try to xo, looks promising.

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[parais...@gmail.com]

> Please note that xo is NOT an ORM, nor does xo generate an ORM.

Yet that's exactly what it is , you take tables and relations between tables and map them to Go types , whether you use code generation or reflection is an implementation detail.

I know there is a hate for things called "ORM" or "web frameworks" in the Go community. But "A rose by any other name would smell as sweet" .