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".
- Version
- v0.0.0-20230424224118-210768274e00 (latest)
- Published
- Apr 24, 2023
- Platform
- linux/amd64
- Imports
- 8 packages
- Last checked
- 1 week ago –
Tools for package owners.