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

go install 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.

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

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

to work correctly with SQLite databases, they need to contain data. reading an empty database generates errors, as there are no information about the data types which can be gathered then. see https://www.sqlite.org/datatype3.html for more information about this.

#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