Skip to content

Generator

Jump to bottom
go-jet edited this page Oct 22, 2021 · 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 executables can be installed in the following ways:

  1. Install jet generator to GOPATH/bin folder:
cd $GOPATH/src/ && GO111MODULE=off go get -u github.com/go-jet/jet/cmd/jet

Make sure GOPATH/bin folder is added to the PATH environment variable.

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

  1. (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.

Test jet generator can be found in the PATH.

$ jet -h

Jet generator 2.6.0

Usage:
  -dsn string
        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
  -source string
        Database system name (PostgreSQL, MySQL, MariaDB or SQLite)
  -host string
        Database host path (Example: localhost)
  -port int
        Database port
  -user string
        Database user
  -password string
        The user’s password
  -dbname string
        Database name
  -params string
        Additional connection string parameters(optional)
  -schema string
        Database schema name. (default "public") (ignored for MySQL, MariaDB and SQLite)
  -sslmode string
        Whether or not to use SSL(optional) (default "disable") (ignored for MySQL, MariaDB and SQLite)
  -path string
        Destination dir for files generated.

Example commands:

        $ jet -source=PostgreSQL -dbname=jetdb -host=localhost -port=5432 -user=jet -password=jet -schema=dvds -path=./gen
        $ jet -dsn=postgresql://jet:jet@localhost:5432/jetdb -schema=dvds -path=./gen
        $ jet -source=postgres -dsn="user=jet password=jet host=localhost port=5432 dbname=jetdb" -schema=dvds -path=./gen
        $ jet -source=sqlite -dsn="file://path/to/sqlite/database/file" -schema=dvds -path=./gen

PostgreSQL

To generate jet SQL Builder and Data Model files from postgres 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=PostgreSQL -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" -schema=dvds -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 model field:

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