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