Generator

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 two ways:

1) Generating from command line

Install jet to GOPATH bin folder. This will allow generating jet files from the command line.

go install github.com/go-jet/jet/cmd/jet

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

Test jet generator can be found in the PATH.

Jet generator 2.0.0

Usage:
  -source string
        Database system name (PostgreSQL, MySQL or MariaDB)
  -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 and MariaDB)
  -sslmode string
        Whether or not to use SSL(optional) (default "disable") (ignored for MySQL and MariaDB)
  -path string
        Destination dir for files generated.

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 -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.
Assuming we are running local database, with user jet, password pass, database jetdb, we will use this command:

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

Connecting to MySQL database: jet:pass@tcp(localhost:3306)/dvds
Retrieving database information...
        FOUND 16 table(s), 7 view(s), 1 enum(s)
Destination directory: gen/dvds
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

2) Generating from code

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

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

...

err = postgres.Generate("./gen", 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", 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
            ...