SQL Builder

SQL Builder

SQL Builder files are Go files, containing types necessary to write type safe SQL queries in Go. They are autogenerated from database tables and enums. File names is always snake case of the table or enum name.

Table SQL Builder files

Following rules are applied to generate table SQL Builder files:

• for every table there is one Go SQL Builder file generated.
• every file contains one type - struct with nested jet.Table.
• for every column of table 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 minus primary key columns (Useful in INSERT or UPDATE statements).

Mappings of database types to sql builder column types:

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
enums, text, character, character varying
bytea, uuid
and all remaining types	ColumnString

Example

Sql 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 {
	jet.Table

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

	AllColumns     jet.ColumnList
	MutableColumns jet.ColumnList
}

Enum SQL Builder files

Following rules are applied to generate enum SQL Builder files:

• for every enum there is one Go SQL Builder file generated.
• every file contains one type. Type name is a camel case of 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

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    jet.StringExpression
	PG   jet.StringExpression
	PG13 jet.StringExpression
	R    jet.StringExpression
	NC17 jet.StringExpression
}{
	G:    jet.NewEnumValue("G"),
	PG:   jet.NewEnumValue("PG"),
	PG13: jet.NewEnumValue("PG-13"),
	R:    jet.NewEnumValue("R"),
	NC17: jet.NewEnumValue("NC-17"),
}