“This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!”

This article is mainly a translation summary of the official documentation for SQLBoiler, but the content of foreign keys can be ignored.

Noun explanation

  • A model can be understood as a table in a database
  • Database Handle Database handle/data source

What is SQLBoiler

SQLBoiler is a tool for generating corresponding Go ORM code from database tables.

It is a database-first ORM framework, which means you need to design your database first, rather than designing structs first, as GORM does.

About SQLBoiler

features

  • Full Model Generation
  • Code generation is very fast
  • High performance through code generation and smart caching
  • With Boil.executor, you can adapt to SQL.db, SQLX.db, and so on
  • Use the context. The context
  • Strongly typed queries (usually without casting and pointer binding)
  • Hook (Before/After the Create/Select/Update/Delete/Upsert)
  • Automatically set CreatedAt/UpdatedAt/DeletedAt
  • Whitelist and blacklist of tables and columns
  • Custom struct tags
  • The transaction
  • Raw SQL
  • Compatibility testing (depending on the database schema you are using)
  • The Debug log
  • Eunm type
  • Support for many engines

Supported Databases

The database Drive the address
PostgreSQL Github.com/volatiletec…
MySQL Github.com/volatiletec…
MSSQLServer 2012+ Github.com/volatiletec…
SQLite3 Github.com/volatiletec…
CockroachDB Github.com/glerchundi/…

Give it a try

import (
  // Use.import so that you don't have to prefix the qm.Limit and so on
  . "github.com/volatiletech/sqlboiler/v4/queries/qm"
)

// Open the database
db, err := sql.Open("postgres"."dbname=fun user=abc")
iferr ! =nil {
  return err
}

// If you don't want to call methods with db arguments
// You can set the global data source by using boile.setdb ()
The --add-global-variants parameter will enable the generation of methods with the G(global) suffix when the code is generated
boil.SetDB(db)
users, err := models.Users().AllG(ctx)

// Query all users
users, err := models.Users().All(ctx, db)

// the P suffix, direct panic if err is not nil
// Adding the --add-panic parameter to code generation will enable the generation of methods with the P(panic) suffix
users := models.Users().AllP(db)

// Complex query
users, err := models.Users(Where("age > ?".30), Limit(5), Offset(6)).All(ctx, db)

// Complex query
users, err := models.Users(
  Select("id"."name"),
  InnerJoin("credit_cards c on c.user_id = users.id"),
  Where("age > ?".30),
  AndIn("c.kind in ?"."visa"."mastercard"),
  Or("email like ?".`%aol.com%`),
  GroupBy("id"."name"),
  Having("count(c.id) > ?".2),
  Limit(5),
  Offset(6),
).All(ctx, db)

// All queries can use any Boil.Executor implementation class (* sql.db, * sqL.tx, etc.)
tx, err := db.BeginTx(ctx, nil)
iferr ! =nil {
  return err
}
users, err := models.Users().All(ctx, tx)
Copy the code

Requirements and Hints

requirements

  • Go 1.13 or above.

  • Table and column names must be snake_case.

  • MySQL minimum 5.6.30; Because SSL-mode is not supported in previous versions.

  • For MySQL using github.com/go-sql-driver/mysql druver, add parseTime=True to the connection to enable time.time parsing. In Models, SQLBoiler uses time. time and null. time to represent time, and models will not be available if no DATE/DATETIME columns are enabled.

    • Db, err := SQL.Open(“mysql”,”root:root@tcp(127.0.0.1:3306)/dbname? parseTime=True”)

    • For details, see timetime-support. The default value of MySQL DATE and DATETIME is []byte. You can use a []byte, string or SQL.RawBytes variable to scan. This argument changes the internal output type from []byte to time.time.

      • A further reason is that the Go1.1 release is now availabletime.TimeBe the only one that can scanDATE 和 DATETIMEThat’s a violationsql.RawBytes support.

prompt

  • SQLBoiler generates type-safe table names (/models/boil_table_names.go/TableNames), column name (/ models / [table name]. Go/UserColumns) and the WHERE statement (/ models / [table name]. Go/UserWhere). You should use these instead of using the string directly, which is prone to errors when the database changes.
  • Transactions are recommended to improve performance and ensure data integrity when multiple statements need to be executed.
  • If you don’t consider using the hooks function, you can use the –no-hooks parameter to avoid generating code and reduce file size.

start

Download and install

You must install the code generator first.

#Go 1.16 or newer
go install github.com/volatiletech/sqlboiler/v4@latest
go install github.com/volatiletech/sqlboiler/v4/drivers/sqlboiler-mysql@latest

#Go 1.15 or before
#Boiler V4 (PSQL, mysql, MSSQL, SQlite3)
#Note: Do not install in another Go Module, or you will contaminate your go.mod file
GO111MODULE=on go get -u -t github.com/volatiletech/sqlboiler/v4
GO111MODULE=on go get github.com/volatiletech/sqlboiler/v4/drivers/sqlboiler-mysql
Copy the code

The dependency on SQLBoiler installed in your project using the following command will be automatically added to your go.mod file.

go get github.com/volatiletech/sqlboiler/v4
go get github.com/volatiletech/null/v8
Copy the code

configuration

Only TOML files and environment variables are supported.

The configuration file should be named SQLboiler. Toml and search in the following order:

  • . /
  • $XDG_CONFIG_HOME/sqlboiler/
  • $HOME/.config/sqlboiler/

Database driven configuration items

The configuration of the corresponding driver must start with driver name. You must use configuration files or environment variables to configure a database driver.

The MySQL configuration file should look like this:

[mysql]
dbname = "your_database_name"
Copy the code

The configuration must be prefixed with the driver name when you use an environment variable:

MYSQL_DBNAME="your_database_name"
Copy the code

Database parameters:

The configuration of Whether must Postgres default values MySQL default values MSSQL default values
schema no “public” none “dbo”
dbname yes none none none
host yes none none none
port no 5432 3306 1433
user yes none none none
pass no none none none
sslmode no “require” “true” “true”
whitelist no [] [] []
blacklist no [] [] []

Examples of blacklist and whitelist:

[mysql]
# Migrations table and Addresses table name field will not be generated
The foreign key corresponding to # will not be deleted either, as this may cause some errors.
blacklist = ["migrations"."addresses.name"]
Copy the code

General Configuration Items

If you do not want to specify these variables as environment variables or command-line arguments, you can add the following configuration items to the configuration file.

Configuration items The default value explain
pkgname “models” The package name of the generated code, models by default
output “models” The name of the folder where the code is generated, models by default
tag [] Tag structures other than JSON, YAML, tomL
debug false In debug mode, stack information is printed on an error
add-global-variants false Start generating global variables (method with G suffix)
add-panic-variants false Start generation of panic variable (method with P suffix)
no-context false Disables the use of context. context in generated code, such as for method arguments
no-hooks false Disable hook feature
no-tests false Disable test file generation
no-auto-timestamps false Disable automatic creATED_AT/updatED_at Settings
no-rows-affected false Disables the generated code’s impact line number return value
no-driver-templates false Disables parsing of database-driven defined templates
tag-ignore [] Which column names have the tag set to ‘-‘, that is, ignore parsing

Configuration example

output   = "my_models"
wipe     = true
no-tests = true

[psql]
  dbname = "dbname"
  host   = "localhost"
  port   = 5432
  user   = "dbusername"
  pass   = "dbpassword"
  schema = "myschema"
  blacklist = ["migrations"."other"]

[mysql]
  dbname  = "dbname"
  host    = "localhost"
  port    = 3306
  user    = "dbusername"
  pass    = "dbpassword"
  sslmode = "false"

[mssql]
  dbname  = "dbname"
  host    = "localhost"
  port    = 1433
  user    = "dbusername"
  pass    = "dbpassword"
  sslmode = "disable"
  schema  = "notdbo"
Copy the code

See this article for an introduction to the TOML format

Primary code generation

After writing the configuration file, we can call the SQLBoiler command tool to generate the code.

Mysql < mysql > mysql < mysql > - add - global - variants start to generate global variables (suffix) method with G - add - panic - variants start to generate panic variables (method with P suffix) - add - soft - start by updating deleted_at deletes Timestamp soft delete -c, --config file name configuration file name -d, --debug debug mode, In error, we print the stack. -h, --help --no-auto-timestamps Disabling automatic setting creATED_at/updatED_at --no-back-referencing Disable back referencing in the loaded Relationship structs --no-context disallows the use of context. context in generated code, For example, use method parameters --no-driver-templates to disable parsing of templates defined by the database driver --no-hooks to disable the hook feature --no-rows-affected to disable generated code's return value --no-tests to disable generated test files -o, --output string Name of the folder where the code is generated, default models -p, -- pkgName string Name of the package where the code is generated, Default models --struct-tag-casing string determines the format of the name on the Go code tag. Alias or Snake (default: "snake") -t, --tag strings --tag structures other than JSON, YAMl, toml --tag-ignore strings for column names whose tag is set to '-', This means ignoring the --templates strings directory and overwriting sqlBoiler's BinData template folder --version print version --wipe using rm -rf to remove the output folder before generating codeCopy the code

Here is a basic model generation. After generating code you can run compatibility tests to run all generated code to ensure that your database is compatible with SQLBoiler. If you fail, see the troubleshooting section below.

#Produce models
#Sqlboiler - PSQL is automatically invoked in your CWD and PATH when mysql parameters are found
sqlboiler mysql

#Run the test
go test ./models
Copy the code

You can also use Go generate to generate code if you want to make your app easy to run from the command line.

//go:generate sqlboiler mysql
Copy the code

Do not modify any build files, which will cause errors in the regenerate code.

Regenerate code

It is recommended to add a –wipe flag when regenerating code to completely remove the previously generated directory, because Sqlboiler does not compare the code before and after and then simply overwrite the previous code.

Code generation control

The template is executed in the specified manner, and the command line or configuration file can control the function.

The alias

Sqlboiler automatically generates names, but if your database name is poorly named and cannot be changed, or IF SQLBoiler cannot deduce your name, you can use aliases to modify table and column names in generated code.

Note: There is no need to provide all names, other names will be inferred as usual.

# Set the alias for the original table named user
[aliases.tables.user]
# Capitalized and plural names
up_plural     = "UserInfos"
The name when # is uppercase and singular
up_singular   = "UserInfo"
# Lowercase plural name
down_plural   = "userInfos"
# Lowercase singular name
down_singular = "userInfo"

    # set alias PSW for password column of table user
    [aliases.tables.user.columns]
    password = "psw"
Copy the code

Another way to write it is to use an array of TOML instead of a map, but now you need to use name to specify the name and alias to specify the alias.

# Set the alias for the original table named user
[[aliases.tables]]
# Original table name
name = "user"
# Capitalized and plural names
up_plural     = "UserInfos"
The name when # is uppercase and singular
up_singular   = "UserInfo"
# Lowercase plural name
down_plural   = "userInfos"
# Lowercase singular name
down_singular = "userInfo"

    Set PSW for password field of table user
    [[aliases.tables.columns]]
    # Original column name
    name = "password"
    # alias
    alias = "psw"
Copy the code

type

Sqlboiler drive inferences about types can be overridden with configuration files.

# Multiple type substitutions will be matched from top to bottom, and matching multiple types will be replaced sequentially
# More specific matching rules should be placed below, as they will be executed as soon as a match is found
[[types]]
  # Match the type to be replaced. All columns will be matched
  Types. Match: tables = ['users', 'videos']
  [types.match]
    type = "null.String"
    nullable = true
    # tables = ['user']

  # Type of replacement
  [types.replace]
    type = "mynull.String"

  Import packages of the replacement type
  [types.imports]
    third_party = ['"github.com/me/mynull"']
Copy the code

Import the Imports

Replace the contents of the import, which should normally be avoided.

[imports.all]
  standard = ['"context"']
  third_party = ['"github.com/my/package"']

Change the import of boil_queries file
[imports.singleton."boil_queries"]
  standard = ['"context"']
  third_party = ['"github.com/my/package"']

# same as all
[imports.test]

# same as Singleton
[imports.test_singleton]

Change import when model contains string
[imports.based_on_type.string]
  standard = ['"context"']
  third_party = ['"github.com/my/package"']
Copy the code

It can also be written like this, specified by the name field.

[[imports.singleton]]
  name = "boil_queries"
  third_party = ['"github.com/my/package"']

[[imports.based_on_type]]
  name = "null.Int64"
  third_party = ['"github.com/my/int64"']
Copy the code

Templates Templates

You can generate additional files in other languages by looking at document Templates if you want to

Extend the generated Models

Models can be extended with helper functions. It is best to place extended functions elsewhere in the Models outsourcing to avoid being removed during code regeneration. Here’s how to extend models 3:

Method 1: Simple functions

package modext

// UserFirstTimeSetup is an extension of the User Model
func UserFirstTimeSetup(ctx context.Context, db *sql.DB, u *models.User) error{... }Copy the code

Call mode:

user, err := Users().One(ctx, db)
// Error checking

err = modext.UserFirstTimeSetup(ctx, db, user)
// Error checking
Copy the code

Method 2: empty structure method

This is a better way to divide the methods.

package modext

type users struct {}

var Users = users{}

// FirstTimeSetup is an extension of the User Model
func (users) FirstTimeSetup(ctx context.Context, db *sql.DB, u *models.User) error{... }Copy the code

Call mode:

user, err := Users().One(ctx, db)
// Error checking

err = modext.Users.FirstTimeSetup(ctx, db, user)
// Error checking
Copy the code

Method 3: Nesting

user, err := Users().One(ctx, db)
// Error checking

enhUser := modext.User{user}
err = ehnUser.FirstTimeSetup(ctx, db)
// Error checking
Copy the code

Q&A

  • Forget to exclude unwanted tables, such as migration tables, when generating code.
  • The table forgot to set the primary key. All tables need a primary key.
  • Compatibility tests require permission to create a database for testing, please make sure that thesqlboiler.tomlTo configure sufficient permissions.
  • Nil or closed data source (database Handle). Make sure you pass inboil.ExecutorIt’s not nil, which is zeroboil.SetDB(db) 的 db.
    • If you useG(all data sources), be sure to initialize the global data source and use boil.setdb ()
  • Naming collisions. If the compilation fails due to a Naming conflict, check the alias feature.
  • The field is not inserted (usually a Boolean type with a default value of true),boil.InferTreat all zeros in Go as if you don’t want to insert. When you want to insert false of type bool, usewhitelist/greylistLet SQLBoiler know what fields you want to insert.
  • Decimal library errors, such aspq: encode: unknown type types.NullDecimalbecausegithub.com/ericlargergren/decimalThe library is too new or corrupted, please use the following version in go.modD4874d5 github.com/ericlagergren/decimal v0.0.0-20181231230500-73749

Additional error causes can be seen by looking at the generated code, or by setting Boil. DebugMode to true to see the SQL and parameters. You can also set boile. DebugWriter to redirect debug output streams; the default is os.stdout.

If you still haven’t solved it or you find a bug, issue it.

Features and Examples

Most of the following examples use this table structure:

CREATE TABLE pilots (
  id integer NOT NULL,
  name text NOT NULL
);
ALTER TABLE pilots ADD CONSTRAINT pilot_pkey PRIMARY KEY (id);

CREATE TABLE jets (
  id integer NOT NULL,
  pilot_id integer NOT NULL,
  age integer NOT NULL,
  name text NOT NULL,
  color text NOT NULL
);
ALTER TABLE jets ADD CONSTRAINT jet_pkey PRIMARY KEY (id);

CREATE TABLE languages (
  id integer NOT NULL.language text NOT NULL
);
ALTER TABLE languages ADD CONSTRAINT language_pkey PRIMARY KEY (id);

CREATE TABLE pilot_languages (
  pilot_id integer NOT NULL,
  language_id integer NOT NULL
);
ALTER TABLE pilot_languages ADD CONSTRAINT pilot_language_pkey PRIMARY KEY (pilot_id, language_id);
Copy the code

CreatedAt/UpdatedAt automatically

If you are generating SQLBoiler Models with creATED_AT or updatED_at columns it will be automatically set to time.now (). You can disable this feature by using –no-auto-timestamps.

Note: You can set your time zone with box.setLocation ().

Skip the automatic setting time

If you don’t want to automatically set a timestamp on an INSERT or update, you can avoid it by using Box.skiptimeStamps.

Override automatic setting time

  • Insert
    • created_at 或 updated_atA value of zero is automatically set.
    • If you want to set to NULL, setValid 为 falseAnd set theTimeIs a non-zero value (note that null.time is used here).
  • Update
  • updated_atThe column is always set totime.Now(). If you want to override this behavior, you can usequeries.Raw(), use hook coverageupdated_atOr create awrapper.
  • Upsert
    • ifcreated_atA value of zero will be set automatically. If you want to set to NULL, setValid 为 falseAnd set theTimeIs non-zero value.
    • updated_atThe column is always set totime.Now().

Automatic DeletedAt (soft delete)

SQLBoiler uses deleted_AT to provide soft delete functionality. Deleted_at should be a Nullable time field.

Note: Soft delete is launched with –add-soft-deletes, and may be modified for later versions.

Note: Queries can be bypassed by qM. WithDeleted. But there is currently no way to Exists/Find

Perform this operation.

Note: The current Delete does not set updated_at.

Query constructor

SQLBoiler generates Starter methods for you that are named plural forms of model, such as models.users (). The startup method is used to construct the Query Mod System. The start Method takes a set of Query mods as arguments and calls a Finisher Method at the end.

Some examples:

// SELECT COUNT(*) FROM user;
count, err := models.Users().Count(ctx, db)

// SELECT * FROM user LIMIT 5;
users, err := models.Users(qm.Limit(5)).All(ctx, db)

// DELETE FROM user WHERE id = 1;
err := models.Users(qm.Where("id = ?".1)).DeleteAll(ctx, db)
// The following is the type safe way
err := models.Users(models.UserWhere.ID.EQ(1)).DeleteAll(ctx, db)
Copy the code

You can also use models.newQuery () if you need to specify the tables directly.

rows, err := models.NewQuery(qm.From("user")).Query(db)
Copy the code

As you can see, the query module allows you to modify your query, and the end method allows you to perform the final operation.

Query module system (Query Mod System)

The query module system allows you to construct your query using the launch method.

Note: SQLBoiler generates type-safe identifiers for tables and columns. Using these identifiers is more secure because if the database is modified it will result in a compile error rather than a run-time error.

// Click import so that we can use the function directly without the "qm." prefix
import . "github.com/volatiletech/sqlboiler/v4/queries/qm"

// Use raw query to generate model
// If this query module exists, other query modules will be overwritten
SQL("select * from pilots where id=?".10)
models.Pilots(SQL("select * from pilots where id=?".10)).All()

Select("id"."name") // Specify the query column
Select(models.PilotColumns.ID, models.PilotColumns.Name)
From("pilots as p") // Specify the from table
From(models.TableNames.Pilots + " as p")

// Build the WHERE statement
Where("name=?"."John")
models.PilotWhere.Name.EQ("John")
And("age=?".24)
There is currently no equivalent type-safe query
Or("height=?".183)
There is currently no equivalent type-safe query

Where("(name=? and age=?) or (age=?) "."John".5.6)
// Expr allows you to group statements manually
Where(
  Expr(
    models.PilotWhere.Name.EQ("John"),
    Or2(models.PilotWhere.Age.EQ(5)),
  ),
  Or2(models.PilotAge),
)

// The WHERE IN statement is built
WhereIn("name, age in ?"."John".24."Tim".33) WHERE ("name","age") IN (($1,$2),($3,$4))
WhereIn(fmt.Sprintf("%s, %s in ?", models.PilotColumns.Name, models.PilotColumns.Age, "John".24."Tim".33))
AndIn("weight in ?".84)
AndIn(models.PilotColumns.Weight + " in ?".84)
OrIn("height in ?".183.177.204)
OrIn(models.PilotColumns.Height + " in ?".183.177.204)

InnerJoin("pilots p on jets.pilot_id=?".10)
InnerJoin(models.TableNames.Pilots + " p on " + models.TableNames.Jets + "." + models.JetColumns.PilotID + "=?".10)

GroupBy("name")
GroupBy("name like ? DESC, name"."John")
GroupBy(models.PilotColumns.Name)
OrderBy("age, height")
OrderBy(models.PilotColumns.Age, models.PilotColumns.Height)

Having("count(jets) > 2")
Having(fmt.Sprintf("count(%s) > 2", models.TableNames.Jets)

Limit(15)
Offset(5)

// Explicit locking
For("update")

/ / With statement
With("cte_0 AS (SELECT * FROM table_0 WHERE thing=? AND stuff=?) ")
Copy the code

Function variations

The function can generate its variants via –add-global-variants and — add-panicked variants.

// Set the global database handle (data source) G method variant
boil.SetDB(db)

pilot, _ := models.FindPilot(ctx, db, 1)

err := pilot.Delete(ctx, db) // Common mode requires db parameter
pilot.DeleteP(ctx, db)       // the P variant, which requires the db argument and panic on error
err := pilot.DeleteG(ctx)    // The G variant, which does not require the db parameter
pilot.DeleteGP(ctx)          // The GP variant, which does not require the DB parameter and panic on error

db.Begin()                   // Create a transaction in the normal way
boil.BeginTx(ctx, nil)       // Create the transaction using the global data source
Copy the code

The end of the method

All closing methods have P and G variants.

// The call is similar to the following:
models.Pilots().All(ctx, db)

One() // Query a row (same as LIMIT(1))
All() SELECT * FROM 'SELECT * FROM' where (SELECT * FROM ')
Count() // Query the number of rows (same as COUNT(*))
UpdateAll(models.M{"name": "John"."age": 23}) // Update all matching rows
DeleteAll() // Delete all matching rows
Exists() // Return a bool indicating whether the queried rows exist
Bind(&myObj) // Bind the query structure to your own struct
Exec() // Execute without any return SQL query
QueryRow() // Execute the SQL query that needs to be returned in one row
Query() // Execute SQL queries that require multiple rows to be returned
Copy the code

The original query

We provide queries.raw () to execute the original query. Usually you need to use Bind()

err := queries.Raw("select * from pilots where id=?".5).Bind(ctx, db, &obj)
Copy the code

You can use your own struct or generated struct to Bind(). Bind() ‘supports binding to a single object or to a slice.

Queries.raw () also has a method that does not require object binding.

You can also use models.newQuery () to apply the query module system with your own custom struct.

The binding

The Bind() terminating function allows you to Bind your own struct or generated struct using either the original query or the query construct.

// The custom struct uses two generated structs
type PilotAndJet struct {
  models.Pilot `boil:",bind"`
  models.Jet   `boil:",bind"`
}

var paj PilotAndJet
// Use the original query
err := queries.Raw(db, ` select pilots.id as "pilots.id", pilots.name as "pilots.name", jets.id as "jets.id", jets.pilot_id as "jets.pilot_id", jets.age as "jets.age", jets.name as "jets.name", jets.color as "jets.color" from pilots inner join jets on jets.pilot_id=?`.23,
).Bind(&paj)

// Use the query construct
err := models.NewQuery(
  Select("pilots.id"."pilots.name"."jets.id"."jets.pilot_id"."jets.age"."jets.name"."jets.color"),
  From("pilots"),
  InnerJoin("jets on jets.pilot_id = pilots.id"),
).Bind(ctx, db, &paj)
Copy the code
// Customize struct to query a subset
type JetInfo struct {
  AgeSum int `boil:"age_sum"`
  Count int `boil:"juicy_count"`
}

var info JetInfo

// Use the query construct
err := models.NewQuery(Select("sum(age) as age_sum"."count(*) as juicy_count", From("jets"))).Bind(ctx, db, &info)

// Use the original query
err := queries.Raw(`select sum(age) as "age_sum", count(*) as "juicy_count" from jets`).Bind(ctx, db, &info)
Copy the code

Control over Bind() can be done using the following struct tag mode:

type CoolObject struct {
  // If no name is specified, the TitleCase of the column name is used for matching
  Frog int

  // If a name is specified, the titlecased version of the column name is used for matching
  Specify an alternative name for the column, it will
  // be titlecased for matching, can be whatever you like.
  Cat int  `boil:"kitten"`

  // Ignore this domain and do not bind
  Pig int  `boil:"-"`

  // Binding is not implemented in the normal way, such as SQL-able strict such as time.time
  // Recursively search for the name of the query result in the Dog struct
  Dog      `boil:",bind"`

  // Same as above, except to specify a different table name
  Mouse    `boil:"rodent,bind"`

  // Ignore this domain and do not bind
  Bird     `boil:"-"`
}
Copy the code

hook

You can do all kinds of things with hooks. If you don’t want to include hooks in your generated code, you can use –no-hooks.

You can use the following types of hooks:

const (
  BeforeInsertHook HookPoint = iota + 1
  BeforeUpdateHook
  BeforeDeleteHook
  BeforeUpsertHook
  AfterInsertHook
  AfterSelectHook
  AfterUpdateHook
  AfterDeleteHook
  AfterUpsertHook
)
Copy the code

To register a hook, you need to define a hook function and mount it using the AddModelHook method.

// Define the hook function
func myHook(ctx context.Context, exec boil.ContextExecutor, p *Pilot) error {
  // Custom logic
  return nil
}

// Register BeforeInsert hooks
models.AddPilotHook(boil.BeforeInsertHook, myHook)
Copy the code

Skip the hook

You can skip the hooks for the current context by using Boil.skiphooks.

The transaction

The operation of a transaction is simple:

tx, err := db.BeginTx(ctx, nil)
iferr ! =nil {
  return err
}

users, _ := models.Pilots().All(ctx, tx)
users.DeleteAll(ctx, tx)

/ / Rollback or commit
tx.Commit()
tx.Rollback()
Copy the code

You can also start a transaction with the global data source using boile.begintx (), which uses the data source set by boile.setdb ().

The Debug log

Debug Logs print SQL statements and used parameters. Debug logging can be enabled using global variables:

boil.DebugMode = true

// Optionally set the output source. The default is OS. Stdout
fh, _ := os.Open("debug.txt")
boil.DebugWriter = fh
Copy the code

Select

Select is done by querying bindings and Find.

// Query a pilot
pilot, err := models.Pilots(qm.Where("name=?"."Tim")).One(ctx, db)
// Type safe mode
pilot, err := models.Pilots(models.PilotWhere.Name.EQ("Tim")).One(ctx, db)

// Query the specified column
jets, err := models.Jets(qm.Select("age"."name")).All(ctx, db)
// Type safe mode
jets, err := models.Jets(qm.Select(models.JetColumns.Age, models.JetColumns.Name)).All(ctx, db)
Copy the code

Find

Used to query a row by primary key.

// Query all columns
pilot, err := models.FindPilot(ctx, db, 1)

// Query the selected columns
jet, err := models.FindJet(ctx, db, 1."name"."color")
Copy the code

Insert

One of the important things to note about Insert operations is how you select the columns to Insert. You can specify this using one of the following: boil.infer, boil.whitelist, boil.blacklist, or Boil.greylist.

These operations will control what columns will be inserted into the database, and what values will be returned from the database to your struct (default, increment primary key, trigger). Your struct will be set to these values after the insert.

If you use Infer, the field won’t be selected if the field is the zero value of Go and the field has a default value in the database. Note that SQLBoiler does not know the default values set by your database, so the Go zero value is very important (this often leads to problems with columns of type bool that default to true). Use whitelist or greylist to insert Go zeros.

methods behavior
Infer Intelligent inference of inserted columns
Whitelist Insert only the specified column
Blacklist Intelligent inferences about inserted columns, but the specified column will not be inserted
Greylist Intelligent inference of inserted columns, but the specified column is inserted

**CreatedAt/UpdatedAt is not automatically included in a Whitelist.

var p1 models.Pilot
p1.Name = "Larry"
err := p1.Insert(ctx, db, boil.Infer()) // Specify to insert the name column
// p1's ID field is set to 1

var p2 models.Pilot
p2.Name = "Boris"
err := p2.Insert(ctx, db, boil.Infer()) // Specify to insert the name column
// THE ID domain of P2 is set to 2

var p3 models.Pilot
p3.ID = 25
p3.Name = "Rupert"
err := p3.Insert(ctx, db, boil.Infer()) // Insert the id, name column

var p4 models.Pilot
p4.ID = 0
p4.Name = "Nigel"
err := p4.Insert(ctx, db, boil.Whitelist("id"."name")) // Insert the id, name column
// Note: we use whitelist here, otherwise SQLBoiler will think we want to use autoincrement primary keys,
// Because ID = 0 is the zero value of Go
Copy the code

Update

Update operations can be performed on a struct, or a slice, or with terminating functions.

Update operations on a struct can use whitelist to specify which columns are to be updated.

methods behavior
Infer Intelligently inferring updated columns
Whitelist Only the specified column is updated
Blacklist Intelligent inferences about updated columns, but the specified column will not be updated
Greylist Intelligently inferring the updated columns, but the specified columns are updated

**CreatedAt/UpdatedAt is not automatically included in a Whitelist.

// Query a pilot and update its name
pilot, _ := models.FindPilot(ctx, db, 1)
pilot.Name = "Neo"
rowsAff, err := pilot.Update(ctx, db, boil.Infer())

// Update slice pilots' name to "Smith"
pilots, _ := models.Pilots().All(ctx, db)
rowsAff, err := pilots.UpdateAll(ctx, db, models.M{"name": "Smith"})

Update all pilots' name to "Smith"
rowsAff, err := models.Pilots().UpdateAll(ctx, db, models.M{"name": "Smith"})
Copy the code

Delete

Delete operations can be performed on a struct, or a slice, or with a terminating function.

pilot, _ := models.FindPilot(db, 1)
/ / delete the pilot
rowsAff, err := pilot.Delete(ctx, db)

// Delete all pilots
rowsAff, err := models.Pilots().DeleteAll(ctx, db)

// Delete one slice pilots
pilots, _ := models.Pilots().All(ctx, db)
rowsAff, err := pilots.DeleteAll(ctx, db)
Copy the code

Upsert

Upsert allows you to perform an insert operation and an optional update operation while the record exists.

var p1 models.Pilot
p1.ID = 5
p1.Name = "Gaben"

// INSERT INTO pilots ("id", "name") VALUES ($1, $2)
// ON DUPLICATE KEY UPDATE SET "name" = EXCLUDED."name"
err := p1.Upsert(ctx, db, boil.Whitelist("name"), boil.Infer())
Copy the code
  • MySQL and MSSQL
    • By setting theupdateColumns 为boil.None()To specify no update in case of conflict.

Note: Upsert is not part of the SQL standard and SQLBoiler is not guaranteed to support it.

Reload

If your object is out of sync with the database for some reason, you can use Reload and ReloadAll to Reload using the primary key attached to the object.

pilot, _ := models.FindPilot(ctx, db, 1)

// Objects become out of sync for some reason

// Refresh objects from the database
err := pilot.Reload(ctx, db)

// Refresh all objects from the database
pilots, _ := models.Pilots().All(ctx, db)
err := pilots.ReloadAll(ctx, db)
Copy the code

Exists

jet, err := models.FindJet(ctx, db, 1)

// Check whether record 5 exists
exists, err := models.Pilots(Where("id=?".5)).Exists(ctx, db)
Copy the code

Enums

If your database table is using the enumeration type SQLBoiler to generate the corresponding output to represent the corresponding value, you can use this in your query:

CREATE TABLE event_one (
  id     serial PRIMARY KEY NOT NULL,
  name   VARCHAR(255),
  day    ENUM('monday'.'tuesday'.'wednesday'.'thursday'.'friday') NOT NULL
);
Copy the code

The following code is generated:

const (
  WorkdayMonday    = "monday"
  WorkdayTuesday   = "tuesday"
  WorkdayWednesday = "wednesday"
  WorkdayThursday  = "thursday"
  WorkdayFriday    = "friday"
)
Copy the code

Use table name + column name + TitleCased enumeration values for MySQL generated code.

Constants

The Models package also contains structs representing all table and column names, and also generates a type-safe WHERE query module.

These type-safe identifiers are stored in:

  • models.TableNames.TableName
  • models.ModelColumns.ColumnName
  • models.ModelWhere.ColumnName.Operator

For example, the table name is below models.TableNames:

// The generated code
var TableNames = struct {
  Messages  string
  Purchases string
}{
  Messages:  "messages",
  Purchases: "purchases",}/ / use cases
fmt.Println(models.TableNames.Messages)
Copy the code

Models.{Model}Columns

// The generated code
var MessageColumns = struct {
  ID         string
  PurchaseID string
}{
  ID:         "id",
  PurchaseID: "purchase_id",}/ / use cases
fmt.Println(models.MessageColumns.ID)
Copy the code

{Model} where.{Column}.{Operator}

var MessageWhere = struct {
  ID       whereHelperint
  Text     whereHelperstring
}{
  ID:         whereHelperint{field: `id`},
  PurchaseID: whereHelperstring{field: `purchase_id`}},/ / use cases
models.Messages(models.MessageWhere.PurchaseID.EQ("hello"))
Copy the code