rom command

Command rom reads schema information from various DBMS and generates according code.

Installation

the easiest method of installation is using go install

go install go.rbn.im/rom/cmd/rom

if you use additional SQLite features in your database, you may have to install rom with support for them. add the appropriate build tags as described at https://github.com/mattn/go-sqlite3#features for example, if your database uses fts5 use the fts5 tag:

go install -tags fts5 go.rbn.im/rom/cmd/rom

Supported Databases

currently supported are MySQL/MariaDB and SQLite. support for postgres is planned.

Usage

usage:	rom (-mysql|-sqlite) [-schema STRING] [-pkg STRING] [-dsn STRING]
	[-tables] [-views] [-procedures] [-methods] [-primary] [-foreign]

  the flags -mysql and -sqlite are mutually exclusive. the -dsn flag
  must always select the "information_schema" database where applicable
  (currently MySQL/MariaDB).  the database schema which code is generated
  for is selected by the -schema flag, it isn't used with SQLite.

  code can be generated for -tables, -procedures and -views, with fine grained
  control over what is generated by the -methods, -primary and -foreign flags.
  -table enables code generation of structs for each table in the schema,
  together with a ScanFrom method.  -views generates structs and ScanFrom for
  views similar to tables.  -procedures enables code generation to call stored
  procedures.  -methods enables code generation of the Select and Insert
  methods for tables and Select for views.i -primary enables code generation of
  the Find, Update and Delete methods, these require that the table has a
  primary key constraint.  -foreign enables code generation of methods to
  access other tables related with a foreign constraint.

  overview of flags:

  -dsn string
    	data source name (default "schematest:schematest@unix(/var/run/mysql/mysql.sock)/information_schema?parseTime=True")
  -exportedStructs
    	generate exported struct names
  -foreign
    	generate code which uses foreign keys
  -methods
    	generate code which doesn't use key constraints
  -mysql
    	read a mysql/maria database
  -package string
    	package name of generated code (default "foobar")
  -primary
    	generate code which uses primary keys
  -procedures
    	generate code for stored procedures
  -schema string
    	schema to read information for (default "schema")
  -sqlite
    	read a sqlite database
  -tables
    	generate table structs and ScanFrom method
  -views
    	generate view structs and ScanFrom method

Examples

to generate code for a MariaDB schema named "foobar" you would run rom like this:

rom -mysql -dsn "user:pass@unix(/path/to/mysql.sock)/information_schema?parseTime=True" -schema "foobar"

an example of usage for SQLite would be:

rom -sqlite -dsn "/path/to/database.sqlite"

note that the -schema switch is ignored for sqlite.

Overview of generated code

generated code is supposed to be used in addition to the standard functionality of database/sql , some additional functionality to implement a few standard CRUD actions.

there are always two exported methods generated, one plain method, one which takes an additional context. this reflects the structure of the database/sql package.

the Preparer/ContextPreparer interfaces which the methods use

type Preparer interface {
	Prepare(query string) (*sql.Stmt, error)
}

type ContextPreparer interface {
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

allow for using sql.DB, sql.TX and with ContextPreparer also sql.Conn as a way to give access to the database.

the Scanner interface

type Scanner interface {
	Scan(dest ...interface{}) error
	Columns() ([]string, error)
}

which is used as parameter for the generated ScanFrom methods is currently implemented by sql.Rows.

Generated Table Code

code generated for tables and views consists of a struct for each table together with a ScanFrom method for it.

consider a MariaDB table "bar":

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | int(11)    | NO   | PRI | NULL    |       |
| b     | text       | YES  |     | NULL    |       |
| c     | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

the generated struct for this table would look like this:

type bar struct {
	A int32          // a
	B sql.NullString // b
	C sql.NullInt64  // c
}

additionally a ScanFrom method is created which can be used to put data from sql.Rows into a bar, even if not every column is queried:

func (x *bar) ScanFrom(s Scanner) error {
	cs, err := s.Columns()
	if err != nil {
		return err
	}

	tmpBar := bar{}
	tmpVars := make([]interface{}, len(cs))
	for i, name := range cs {
		switch name {
		case "a":
			tmpVars[i] = &tmpBar.A
		case "b":
			tmpVars[i] = &tmpBar.B
		case "c":
			tmpVars[i] = &tmpBar.C

		default:
			return fmt.Errorf("unknown column name: %v", name)
		}
	}

	err = s.Scan(tmpVars...)
	if err != nil {
		return err
	}

	*x = tmpBar

	return nil
}

as long as column names aren't aliased (say "SELECT a AS x FROM bar;") and only data from this table is queried, it can unpack the result into bar.

to ease the usage of Update methods generated with -primary, a Clone method is generated which returns a copy of the methods receiver:

func (x *bar) Clone() *bar {
	return &bar{A: x.A, B: x.B, C: x.C}
}

Generated Methods

Select is a method to access rows of a table or view

func (x *bar) Select(p Preparer, where string, args ...interface{}) ([]bar, error)

it is like Query of the sql package but scans returned rows into a slice of bar. it can be used like this:

bars, err := (&bar{}).Select(db, "WHERE a == ?", 15)

for tables, a method to create new rows is generated:

func (x *bar) Insert(p Preparer) (sql.Result, error)

which inserts values of the method receiver into a new row:

x := &bar{A: 15}
x.Insert(db)

Generated Primary Methods

if a table has a primary key constraint and generation of code for it is enabled with -primary some additional methods which make use of the primary key are created: Update and Delete.

func (x *bar) Find(p Preparer) error

func (x *bar) Update(p Preparer, newBar *bar) (sql.Result, error)

func (x *bar) Delete(p Preparer) (sql.Result, error)

Find selects a single row selected by the fields of the methods receiver into the methods receiver.

b := &bar{A: 90}
err := b.Find(db)

Update updates a row selected by the fields of the methods receiver with new values of newBar.

b := &bar{A:15}
res, err := b.Update(db, &bar{A: b.A, C: sql.NullInt64{123456789, true}})

Delete deletes the row selected by the fields of the methods receiver.

err := (&bar{A:15}).Delete(db)

Generated Foreign Methods

for each foreign constraint of a table a method is generated to get a value of the referenced structs table.

given tables "bar"

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | int(11)    | NO   | PRI | NULL    |       |
| b     | text       | YES  |     | NULL    |       |
| c     | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

and "baz":

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| d     | int(11)    | YES  | MUL | NULL    |       |
| e     | text       | YES  |     | NULL    |       |
| f     | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

and a foreign constraint on table "baz":

CONSTRAINT `ConstName` FOREIGN KEY (`d`) REFERENCES `bar` (`a`)

a method ForeignConstName would be generated for struct type baz which selects the refered row in bar:

func (x *baz) ForeignF(p Preparer) (*bar, error) {
	stmt, err := x.prepareForeignF(p)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(x.D)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	if !rows.Next() {
		return nil, sql.ErrNoRows
	}

	y := &bar{}
	err = y.ScanFrom(rows)

	if err != nil {
		return nil, err
	}

	return y, nil
}

also the reverse version is generated as method for _bar_, to select every row which references to this baz:

func (x *bar) ForeignF(p Preparer) ([]baz, error) {

	stmt, err := x.prepareForeignF(p)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(x.A)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	ys := []baz{}
	for rows.Next() {
		y := &baz{}
		err = y.ScanFrom(rows)

		if err != nil {
			return nil, err
		}
		ys = append(ys, *y)
	}

	return ys, nil
}

Generated Views Code

code generated for views is the same as is generated for tables, except that no -primary or -foreign methods are generated.

Generated Procedures Code

code generation for stored procedures is largely untested for now.

for each stored procedure, a struct is generated with fields for the procedures arguments. if for example a procedure has two IN arguments X and Y, the generated struct would look like this:

type newbar3 struct {
	// IN x
	X uint32
	// IN y
	Y string
}

a method Call is generated to execute the stored procedure using the values of the methods receiver as parameters:

func (x newbar3) Call(p Preparer) (sql.Result, error) {
	stmt, err := p.Prepare("CALL foo.newbar3(?, ?)")

	if err != nil {
		return nil, err
	}

	defer stmt.Close()

	res, err := stmt.Exec(x.X, x.Y)
	if err != nil {
		return nil, err
	}

	return res, nil
}

if the procedure has OUT or INOUT parameters, a ScanFrom method like generated for tables is generated to read back the results.

type newbar4 struct {
	// OUT y
	Y int32
}

// ScanFrom implements ScannerFrom.
func (x *newbar4) ScanFrom(s Scanner) error {
	cs, err := s.Columns()
	if err != nil {
		return err
	}

	tmpnewbar4 := newbar4{}
	tmpVars := make([]interface{}, len(cs))
	for i, name := range cs {
		switch name {
		case "y":
			tmpVars[i] = &tmpnewbar4.Y

		default:
			return fmt.Errorf("unknown column name: %v", name)
		}
	}

	err = s.Scan(tmpVars...)
	if err != nil {
		return err
	}

	*x = tmpnewbar4

	return nil
}

Quirks

this line is intentionally left blank.

MySQL

stored procedures can be read, but is unlikely to be fully supported with MySQL/MariaDB, as the driver doesn't support named parameters. these are required to have OUT parameters fully supported.

unfortunately, information about columns returned by stored procedures isn't present in INFORMATION_SCHEMA, so there is no code like "Select" generated for them.

SQLite

SQLite only supports -tables, -views, -methods, -primary and -foreign.

if you want to generate code for views and use functions in the select statement, you have to alias the selected column in your view definition with AS, e.g.:

CREATE VIEW foobar AS SELECT foo || "bar" AS foo FROM bar;

otherwise the column name in go would be the literal 'foo || "bar"' which results in garbled code generation.

to have SQLite rowid columns available, you must alias them by creating a column of type INTEGER PRIMARY KEY, like described at https://sqlite.org/lang_createtable.html#rowids_and_the_integer_primary_key

Todo

i suppose the generated code isn't everybodys taste :) this is fixable with templates which are read from files. for now they are go:embed-ed, so if theres really any need they can be changed there.

it would be interesting to generate code for other languages.

License

This Source Code Form is subject to the terms of the Mozilla Public License, v. 2.0. If a copy of the MPL was not distributed with this file, You can obtain one at https://mozilla.org/MPL/2.0/.

Contact

email regarding this package (comments, questions, patches) can be send to "code ate rbn.im".