Jet

Jet is a complete solution for efficient and high performance database access, combining a type-safe SQL builder with code generation and automatic query result mapping.

Jet currently supports the following database engines:

• PostgreSQL
• MySQL
• SQLite

This list is not exclusive, as many other databases implement compatible wire protocols. For example, CockroachDB uses the PostgreSQL wire protocol, and MariaDB is based on the MySQL protocol. Both databases are tested and known to work with Jet.

Support for additional databases may be introduced in future releases.

Jet is the easiest, and the fastest way to write complex type-safe SQL queries as a Go code and map database query result into complex object composition.

[!Note] Jet is not an ORM.

Contents

• Motivation
• Features
• Getting Started
  • Prerequisites
  • Installation
  • Quick Start
    • Generate sql builder and model types
    • Lets write some SQL queries in Go
    • Execute query and store result
• Benefits
• Dependencies
• Versioning
• License
• Support the Project

Motivation

https://medium.com/@go.jet/jet-5f3667efa0cc

Features

1) Auto-generated type-safe SQL Builder. Statements supported: * SELECT, SELECT_JSON (DISTINCT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET, FOR, LOCK_IN_SHARE_MODE, UNION, INTERSECT, EXCEPT, WINDOW, sub-queries) * INSERT (VALUES, MODEL, MODELS, QUERY, ON_CONFLICT/ON_DUPLICATE_KEY_UPDATE, RETURNING), * UPDATE (SET, MODEL, WHERE, RETURNING), * DELETE (WHERE, ORDER_BY, LIMIT, RETURNING), * LOCK (IN, NOWAIT), (READ, WRITE) * WITH

2) Auto-generated Data Model types - Go types mapped to database type (table, view or enum), used to store result of database queries. Can be combined to create complex query result destination. 3) Query execution with result mapping to arbitrary destination.

Getting Started

Prerequisites

To install Jet package, you need to install Go and set your Go workspace first.

Go version 1.24+ is required

Installation

Use the command bellow to add jet as a dependency into go.mod project:

$ go get -u github.com/go-jet/jet/v2

Jet generator can be installed using one of the following methods:

• ✅ Option 1: Install via go install:

go install github.com/go-jet/jet/v2/cmd/jet@latest

[!Tip] 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.

• ✅ Option 2: Build manually from source and install jet generator to specific folder:

git clone https://github.com/go-jet/jet.git
cd jet && go build -o <target_directory> ./cmd/jet

[!Tip] Make sure target_directory is included in your system’s PATH environment variable to allow global access to the jet command.

Quick Start

For this quick start example we will use PostgreSQL sample 'dvd rental' database. Full database dump can be found in ./tests/testdata/init/postgres/dvds.sql. A schema diagram illustrating the relevant part of the database is available here.

Generate SQL Builder and Model types

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

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

Connecting to postgres database: postgresql://user:pass@localhost:5432/jetdb?sslmode=disable 
Retrieving schema information...
  FOUND 15 table(s), 7 view(s), 1 enum(s)
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

Procedure is similar for MySQL, CockroachDB, MariaDB and SQLite. For example:

jet -source=mysql -dsn="user:pass@tcp(localhost:3306)/dbname" -path=./.gen
jet -dsn=postgres://user:pass@localhost:26257/jetdb?sslmode=disable -schema=dvds -path=./.gen  #cockroachdb
jet -dsn="mariadb://user:pass@tcp(localhost:3306)/dvds" -path=./.gen              # source flag can be omitted if data source appears in dsn
jet -source=sqlite -dsn="/path/to/sqlite/database/file" -schema=dvds -path=./.gen
jet -dsn="file:///path/to/sqlite/database/file" -schema=dvds -path=./.gen         # sqlite database assumed for 'file' data sources

*User has to have a permission to read information schema tables.

As indicated by the command output, Jet will perform the following actions: - ✅ Connect to the PostgreSQL database and retrieve metadata for all tables, views, and enums within the dvds schema. - ⚠️ Delete all contents in the target schema folder: ./.gen/jetdb/dvds. - ⚙️ Generate SQL Builder and Data Model types for each table, view, and enum found in the schema.

Generated files folder structure will look like this:

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

Types from the table, view, and enum packages are used to write type-safe SQL queries in Go, while types from the model types are combined to store results of the SQL queries.

[!Note] It is possible to customize the default Jet generator behavior. All the aspects of generated SQLBuilder and model types are customizable(see wiki).

Let's write some SQL queries in Go

First we need to import postgres SQLBuilder and generated packages from the previous step:

import (
  // dot import so go code would resemble as much as native SQL
  // dot import is not mandatory
  . "github.com/go-jet/jet/v2/examples/quick-start/.gen/jetdb/dvds/table"
  . "github.com/go-jet/jet/v2/postgres"

  "github.com/go-jet/jet/v2/examples/quick-start/.gen/jetdb/dvds/enum"
  "github.com/go-jet/jet/v2/examples/quick-start/.gen/jetdb/dvds/model"  
)

Let's say we want to retrieve the list of all actors who acted in films longer than 180 minutes, film language is 'English', film category is not 'Action' and film rating is not 'R'.

stmt := SELECT(
    Actor.ActorID, Actor.FirstName, Actor.LastName, Actor.LastUpdate,  // or just Actor.AllColumns
    Film.AllColumns,                                                  
    Language.AllColumns.Except(Language.LastUpdate),  // all language columns except last_update 
    Category.AllColumns,
).FROM(
    Actor.
        INNER_JOIN(FilmActor, Actor.ActorID.EQ(FilmActor.ActorID)).  
        INNER_JOIN(Film, Film.FilmID.EQ(FilmActor.FilmID)).          
        INNER_JOIN(Language, Language.LanguageID.EQ(Film.LanguageID)).
        INNER_JOIN(FilmCategory, FilmCategory.FilmID.EQ(Film.FilmID)).
        INNER_JOIN(Category, Category.CategoryID.EQ(FilmCategory.CategoryID)),
).WHERE(
    AND(
        Language.Name.EQ(Char(20)("English")), // string columns Language.Name and Category.Name can be compared only with string expression
        Category.Name.NOT_EQ(Text("Action")),
        Film.Length.GT(Int32(180)), // Film.Length is integer column and can be compared only with integer expression
        Film.Rating.NOT_EQ(enum.MpaaRating.R),
        String("Trailers").EQ(ANY(Film.SpecialFeatures)), // type safety is also enforced on array element types
    ),             
).ORDER_BY(
    Actor.ActorID.ASC(),
    Film.FilmID.ASC(),
)

[!Tip] Package(dot) import is used, so the statements look as close as possible to the native SQL.

Note that every column has a type. String columns, such as Language.Name and Category.Name can only be compared with string columns and expressions. Similarly, Actor.ActorID, FilmActor.ActorID, Film.Length are integer columns and can only be compared with integer columns and expressions. The same type safety rules apply to arrays and their element types.

How to Get a Parametrized SQL Query from the Statement?

query, args := stmt.Sql()

query - parametrized query
args - query arguments

Click to see query and args

SELECT actor.actor_id AS "actor.actor_id",
       actor.first_name AS "actor.first_name",
       actor.last_name AS "actor.last_name",
       actor.last_update AS "actor.last_update",
       film.film_id AS "film.film_id",
       film.title AS "film.title",
       film.description AS "film.description",
       film.release_year AS "film.release_year",
       film.language_id AS "film.language_id",
       film.rental_duration AS "film.rental_duration",
       film.rental_rate AS "film.rental_rate",
       film.length AS "film.length",
       film.replacement_cost AS "film.replacement_cost",
       film.rating AS "film.rating",
       film.last_update AS "film.last_update",
       film.special_features AS "film.special_features",
       film.fulltext AS "film.fulltext",
       language.language_id AS "language.language_id",
       language.name AS "language.name",
       category.category_id AS "category.category_id",
       category.name AS "category.name",
       category.last_update AS "category.last_update"
FROM dvds.actor
         INNER JOIN dvds.film_actor ON (actor.actor_id = film_actor.actor_id)
         INNER JOIN dvds.film ON (film.film_id = film_actor.film_id)
         INNER JOIN dvds.language ON (language.language_id = film.language_id)
         INNER JOIN dvds.film_category ON (film_category.film_id = film.film_id)
         INNER JOIN dvds.category ON (category.category_id = film_category.category_id)
WHERE (
          (language.name = $1::char(20))
              AND (category.name != $2::text)
              AND (film.length > $3::integer)
              AND (film.rating != 'R')
              AND ($4::text = ANY(film.special_features))
          )
ORDER BY actor.actor_id ASC, film.film_id ASC;

[English Action 180 Trailers]

How to Get Debug SQL from Statement? go debugSql := stmt.DebugSql() debugSql - this query string can be copy-pasted into sql editor and executed.

[!Warning] Debug SQL is not intended to be used in production. For debug purposes only!!!

Click to see debug sql

```sql SELECT actor.actor_id AS "actor.actor_id", actor.first_name AS "actor.first_name", actor.last_name AS "actor.last_name", actor.last_update AS "actor.last_update", film.film_id AS "film.film_id", film.title AS "film.title", film.description AS "film.description", film.release_year AS "film.release_year", film.language_id AS "film.language_id", film.rental_duration AS "film.rental_duration", film.rental_rate AS "film.rental_rate", film.length AS "film.length", film.replacement_cost AS "film.replacement_cost", film.rating AS "film.rating", film.last_update AS "film.last_update", film.special_features AS "film.special_features", film.fulltext AS "film.fulltext", language.language_id AS "language.language_id", language.name AS "language.name", category.category_id AS "category.category_id", category.name AS "category.name", category.last_update AS "category.last_update" FROM dvds.actor INNER JOIN dvds.film_actor ON (actor.actor_id = film_actor.actor_id) INNER JOIN dvds.film ON (film.film_id = film_actor.film_id) INNER JOIN dvds.language ON (language.language_id = film.language