SQL Builder

Contents

• Table/View SQL Builder files
• Enum SQL Builder files
• Column list

SQL Builder files are Go files that contain types needed to write secure type-safe SQL queries. They are automatically generated using information about databases tables, views and enums.

Table/View SQL Builder files

Following rules are applied to generate table/views SQL Builder files:

• for every table/view there is one Go SQL Builder file generated. File names is always snake case of the table/view name.
• every file contains one type - struct with nested jet.Table.
• for every column of table/view there is a field column in SQL Builder table type. Field name is camel case of column name. See below table for type mapping.
• AllColumns is used as shorthand notation for list of all columns.
• MutableColumns are all columns except primary key and generated columns. (Useful in INSERT or UPDATE statements).
• DefaultColumns are all columns with DEFAULT value set.

Mappings of database types to sql builder column types:

PostgreSQL:

Database type(postgres)	Sql builder column type
boolean	ColumnBool
smallint, integer, bigint	ColumnInteger
real, numeric, decimal, double precision	ColumnFloat
date	ColumnDate
timestamp without time zone	ColumnTimestamp
timestamp with time zone	ColumnTimestampz
time without time zone	ColumnTime
time with time zone	ColumnTimez
bytea	ColumnBytea
enums, text, character, character varying
uuid
and all remaining types	ColumnString

MySQL and MariaDB:

Database type(postgres)	Sql builder column type
boolean	ColumnBool
tinyint, smallint, mediumint, integer, bigint	ColumnInteger
real, numeric, decimal, double precision	ColumnFloat
date	ColumnDate
timestamp, datetime	ColumnTimestamp
time	ColumnTime
blob	ColumnBlob
enums, text, character, character varying
and all remaining types	ColumnString

Example

PostgreSQL table address:

CREATE TABLE dvds.address
(
    address_id serial NOT NULL DEFAULT,
    address character varying(50) NOT NULL,
    address2 character varying(50),
    district character varying(20) NOT NULL,
    city_id smallint NOT NULL,
    postal_code character varying(10),
    phone character varying(20) NOT NULL,
    last_update timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT address_pkey PRIMARY KEY (address_id)
)

Part of the table sql builder file for table address.

package table

import (
	"github.com/go-jet/jet"
)

var Address = newAddressTable()

type AddressTable struct {
	postgres.Table

	//Columns
	AddressID  postgres.ColumnInteger
	Address    postgres.ColumnString
	Address2   postgres.ColumnString
	District   postgres.ColumnString
	CityID     postgres.ColumnInteger
	PostalCode postgres.ColumnString
	Phone      postgres.ColumnString
	LastUpdate postgres.ColumnTimestamp

	AllColumns     postgres.ColumnList
	MutableColumns postgres.ColumnList
}

Enum SQL Builder files

Following rules are applied to generate enum SQL Builder files:

• file names is always snake case of the enum name.
• for every enum there is one Go SQL Builder file generated.
• every file contains one type.
  • PostgreSQL: File name is a snake case of enum name.
  • MySQL or MariaDB: File name is snake case of table/view name + enum name.
• for every enum value there is a field in SQL Builder enum struct. Field name is camel case of enum value. Type is jet.StringExpression, meaning it can be used by string expressions methods.

Example

PostgreSQL enum mpaa_rating:

CREATE TYPE dvds.mpaa_rating AS ENUM
    ('G', 'PG', 'PG-13', 'R', 'NC-17');

Enum SQL Builder file for mpaa_rating:

package enum

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

var MpaaRating = &struct {
	G    postgres.StringExpression
	PG   postgres.StringExpression
	PG13 postgres.StringExpression
	R    postgres.StringExpression
	NC17 postgres.StringExpression
}{
	G:    postgres.NewEnumValue("G"),
	PG:   postgres.NewEnumValue("PG"),
	PG13: postgres.NewEnumValue("PG-13"),
	R:    postgres.NewEnumValue("R"),
	NC17: postgres.NewEnumValue("NC-17"),
}

This enum can be used in expressions like:

Film.Rating.NOT_EQ(enum.MpaaRating.R)

Column list

In addition to the generated AllColumns, MutableColumns and DefaultColumns lists, the developer can create new ColumnList:

updateColumnList := ColumnList{Link.Description, Link.Name, Link.URL}

It is also possible to create new ColumnList by excluding columns from the existing lists:

Address.AllColumns.Except(Address.LastUpdate)
Address.AllColumns.Except(Address.PostalCode, Address.Phone, Address.LastUpdate)
Address.AllColumns.Except(StringColumn("postal_code"), StringColumn("phone"), TimestampColumn("last_update"))

excludedColumns := ColumnList{Address.PostalCode, Address.Phone, Address.LastUpdate, Film.Title}
Address.AllColumns.Except(excludedColumns)

To change table alias of each column within ColumnList use As method:

Address.AllColumns.As("my_address.*")

Address.AllColumns.As("") // this will remove table alias