rom: read schema information from various DBMS and create go code to access the data

#rom, relational object mapper

reads schema information from various DBMS and generates according code.

#install

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.

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

#tables

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}
}

#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)

#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)

#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
}

#views

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

#stored procedures

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

#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 program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 3 of the License.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see https://www.gnu.org/licenses/.

#contact

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

~rbn/rom