Skip to content

Generator

Jump to bottom
go-jet edited this page May 14, 2022 · 20 revisions

Contents

Before we can write SQL queries in Go we have to generate necessary Go files. To generate files we need running database instance containing already defined database schema.

This files can be generated in from command line and programmatically from the code.

Generating from command line

Install jet to one of the folder from PATH environment variable. This will allow generating jet files from any location inside the shell.

Jet generator executable can be installed in one of the following ways:

  • (Go1.16+) Install jet generator using go install:
go install github.com/go-jet/jet/v2/cmd/jet@latest

Jet generator is installed to the directory named by the GOBIN environment variable, which defaults to $GOPATH/bin or $HOME/go/bin if the GOPATH environment variable is not set.

  • Install jet generator to specific folder:
git clone https://github.com/go-jet/jet.git
cd jet && go build -o dir_path ./cmd/jet

Make sure dir_path folder is added to the PATH environment variable.

Test jet generator can be found in the PATH.


$ jet -h

Jet generator 2.8.0

Usage:
  -source
        Database system name (postgres, mysql, cockroachdb, mariadb or sqlite)
  -dsn
        Data source name. Unified format for connecting to database.
        PostgreSQL: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
                Example:
                        postgresql://user:pass@localhost:5432/dbname
        MySQL: https://dev.mysql.com/doc/refman/8.0/en/connecting-using-uri-or-key-value-pairs.html
                Example:
                        mysql://jet:jet@tcp(localhost:3306)/dvds
        SQLite: https://www.sqlite.org/c3ref/open.html#urifilenameexamples
                Example:
                        file://path/to/database/file
  -host
        Database host path. Used only if dsn is not set. (Example: localhost)
  -port
        Database port. Used only if dsn is not set.
  -user
        Database user. Used only if dsn is not set.
  -password
        The user’s password. Used only if dsn is not set.
  -dbname
        Database name. Used only if dsn is not set.
  -schema
        Database schema name. (default "public")(PostgreSQL only)
  -params
        Additional connection string parameters(optional). Used only if dsn is not set.
  -sslmode
        Whether or not to use SSL. Used only if dsn is not set. (optional)(default "disable")(PostgreSQL only)
  -path
        Destination dir for files generated.
  -ignore-tables
        Comma-separated list of tables to ignore
  -ignore-views
        Comma-separated list of views to ignore
  -ignore-enums
        Comma-separated list of enums to ignore

Example command:

        $ jet -dsn=postgresql://jet:jet@localhost:5432/jetdb?sslmode=disable -schema=dvds -path=./gen
        $ jet -dsn=postgres://jet:jet@localhost:26257/jetdb?sslmode=disable -schema=dvds -path=./gen   #cockroachdb
        $ jet -source=postgres -dsn="user=jet password=jet host=localhost port=5432 dbname=jetdb" -schema=dvds -path=./gen
        $ jet -source=mysql -host=localhost -port=3306 -user=jet -password=jet -dbname=jetdb -path=./gen
        $ jet -source=sqlite -dsn="file://path/to/sqlite/database/file" -path=./gen

PostgreSQL or CockroachDB

To generate jet SQL Builder and Data Model files from postgres(or cockroach) database we need to call jet generator with postgres connection parameters and root destination folder path for generated files.
Assuming we are running local postgres database, with user jet, password pass, database jetdb and schema dvds, we will use this command:

jet -dsn=postgresql://jet:pass@localhost:5432/jetdb -schema=dvds -path=./.gen

or

jet -source=postgres -host=localhost -port=5432 -user=jet -password=pass -dbname=jetdb -schema=dvds -path=./gen
Connecting to postgres database: host=localhost port=5432 user=jet password=pass dbname=jetdb sslmode=disable 
Retrieving schema information...
	FOUND 15 table(s), 7 view(s), 1 enum(s)
Destination directory: ./gen/jetdb/dvds
Cleaning up destination directory...
Generating table sql builder files...
Generating view sql builder files...
Generating enum sql builder files...
Generating table model files...
Generating view model files...
Generating enum model files...
Done

MySQL or MariaDB

To generate jet SQL Builder and Data Model files from MySQL or MariaDB database we need to provide jet generator with connection parameters and root destination folder path for generated files.

jet -dsn="mariadb://jet:pass@tcp(localhost:3306)/dvds" -path=./gen

or

jet -source=MySQL -host=localhost -port=3306 -user=jet -password=pass -dbname=dvds -path=./gen

SQLite

To generate jet SQL Builder and Data Model files from SQLite database we just need to provide database file path and destination folder path.

jet -dsn="file:///path/to/sqlite/database/file" -path=./gen

Generating from the code

The same files can be generated manually from the code.
PostgreSQL:

import "github.com/go-jet/jet/generator/postgres"

...

err = postgres.Generate("./gen", // or GenerateDSN(...)
  postgres.DBConnection{
    Host:       "localhost",
    Port:       5432,
    User:       "jet",
    Password:   "jet",
    DBName:     "jetdb",
    SchemaName: "dvds",
    SslMode:    "disable",
})

MySQL or MariaDB

import "github.com/go-jet/jet/generator/mysql"

...

err = mysql.Generate("./.gen", // or GenerateDSN(...)
   mysql.DBConnection{
    Host:     "localhost",
    Port:     3306,
    User:     "jet",
    Password: "jet",
    DBName:   "jetdb",
})

Whether the files are generated from command line or from the code, generator will:

  • connect to database and retrieve information about the tables, views and enums
  • delete everything in destination folder
  • table, view, and enum information are used as a template to generate two types of Go files:
    • SQL Builder files - used to write type safe SQL statements in Go (table, view and enum package)
    • Model files - used to combine and store result from database queries (model package)

Generated files folder structure will look like this:

PostgreSQL:
|-- gen                               # -path
|   `-- jetdb                         # database name
|       `-- dvds                      # schema name
|           |-- enum                  # sql builder package for enums
|           |   |-- mpaa_rating.go
|           |-- table                 # sql builder package for tables
|               |-- actor.go
|               |-- address.go
|               |-- category.go
|               ...
|           |-- view                 # sql builder package for views
|               |-- actor_info.go
|               |-- film_list.go
|               ...
|           |-- model                 # data model types for each table, view and enum
|           |   |-- actor.go
|           |   |-- address.go
|           |   |-- mpaa_rating.go
|           |   ...
MySQL or MariaDB:
|-- gen                           # destination folder
|   `-- jetdb                     # database name
|       |-- enum                  # sql builder folder for enums
|       |   |-- mpaa_rating.go
|       |-- table                 # sql builder folder for tables
|           |-- actor.go
|           |-- address.go
|           |-- film.go
            ...
|       |-- view                 # sql builder package for views
|           |-- actor_info.go
|           |-- film_list.go
|           ...
|       |-- model                 # Plain Old Data for every enum and table
|           |-- actor.go
|           |-- address.go
|           |-- film.go
            ...

Generator customization

All aspects of generated model and SQLBuilder files can be customized.
For instance, there is often a need to change the type of the specific model field. In this example we'll use exact decimal type - decimal.Decimal{}, instead default float64, to prevent possible loss of precision during conversion.

err := postgres.Generate(   //or GenerateDSN(...)
	"./gen/dest/dir",
	dbConnection,
	template.Default(postgres2.Dialect).
		UseSchema(func(schema metadata.Schema) template.Schema {
			return template.DefaultSchema(schemaMetaData).
				UseModel(template.DefaultModel().
					UseTable(func(table metadata.Table) template.TableModel {
						return template.DefaultTableModel(table).
							UseField(func(column metadata.Column) template.TableModelField {
								defaultTableModelField := template.DefaultTableModelField(column)

								if schema.Name == "public" && 
									table.Name == "accounts" && 
									column.Name == "balance" {
								   defaultTableModelField.Type = template.NewType(decimal.Decimal{})
								}
								return defaultTableModelField
							})
					}),
				)
		}),
)

It is also possible to:
- skip model file generation,
- skip sql builder file generation,
- change models destination path,
- change sqlbuilder destination path,
- rename model files,
- rename sql builder files,
- skip specific model files,
- skip specific sql builder files,
- add tags to fields of the model types,
- change type of the model field,
- change type of sql builder column

Clone this wiki locally