!sqlboiler logo

!ActionsCI

It is a "database-first" ORM as opposed to "code-first" (like gorm/gorp). That means you must first create your database schema. Please use something like sql-migrate or some other migration tool to manage this part of the database's life-cycle.

Note on versions

v1, v2, and v3 are no longer maintained.

v3 is the last GOPATH-compatible version.

v4 has no real breaking changes between v3 and itself other than Go modules and is the only maintained version. Note this does not work with GOPATH projects.

Why another ORM

While attempting to migrate a legacy Rails database, we realized how much ActiveRecord benefited us in terms of development velocity. Coming over to the Go database/sql package after using ActiveRecord feels extremely repetitive, super long-winded and down-right boring. Being Go veterans we knew the state of ORMs was shaky, and after a quick review we found what our fears confirmed. Most packages out there are code-first, reflect-based and have a very weak story around relationships between models. So with that we set out with these goals:

• Work with existing databases: Don't be the tool to define the schema, that's better left to other tools.
• ActiveRecord-like productivity: Eliminate all sql boilerplate, have relationships as a first-class concept.
• Go-like feel: Work with normal structs, call functions, no hyper-magical struct tags, small interfaces.
• Go-like performance: Benchmark and optimize the hot-paths, perform like hand-rolled sql.DB code.

• The models package is type safe. This means no chance of random panics due to passing in the wrong type. No need for interface{}.
• Our types closely correlate to your database column types. This is expanded by our extended null package which supports nearly all Go data types.
• A system that is easy to debug. Your ORM is tailored to your schema, the code paths should be easy to trace since it's not all buried in reflect.
• Auto-completion provides work-flow efficiency gains.

Table of Contents

• SQLBoiler

• Benchmarks
• Third-Party Extensions

About SQL Boiler

Features

• Full model generation
• Extremely fast code generation
• High performance through generation & intelligent caching
• Uses boil.Executor (simple interface, sql.DB, sqlx.DB etc. compatible)
• Uses context.Context
• Easy workflow (models can always be regenerated, full auto-complete)
• Strongly typed querying (usually no converting or binding to pointers)
• Hooks (Before/After Create/Select/Update/Delete/Upsert)
• Automatic CreatedAt/UpdatedAt
• Automatic DeletedAt
• Table and column whitelist/blacklist
• Relationships/Associations
• Eager loading (recursive)
• Custom struct tags
• Transactions
• Raw SQL fallback
• Compatibility tests (Run against your own DB schema)
• Debug logging
• Basic multiple schema support (no cross-schema support)
• 1d arrays, json, hstore & more
• Enum types
• Out of band driver support
• Support for database views
• Supports generated/computed columns

Missing features

• Multi-column foreign key support
• Materialized view support

Supported Databases

| Database | Driver Location | | ----------------- | --------------------------------------------------------------------------------------------------- | | PostgreSQL | https://github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-psql | | MySQL | https://github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-mysql | | MSSQLServer 2012+ | https://github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-mssql | | SQLite3 | https://github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-sqlite3 | | CockroachDB | https://github.com/glerchundi/sqlboiler-crdb |

Note: SQLBoiler supports out of band driver support so you can make your own

We are seeking contributors for other database engines.

A Small Taste

For a comprehensive list of available operations and examples please see Features & Examples.

import (
  // Import this so we don't have to use qm.Limit etc.
  . "github.com/aarondl/sqlboiler/v4/queries/qm"
)
// Open handle to database like normal
db, err := sql.Open("postgres", "dbname=fun user=abc")
if err != nil {
  return err
}
// If you don't want to pass in db to all generated methods
// you can use boil.SetDB to set it globally, and then use
// the G variant methods like so (--add-global-variants to enable)
boil.SetDB(db)
users, err := models.Users().AllG(ctx)
// Query all users
users, err := models.Users().All(ctx, db)
// Panic-able if you like to code that way (--add-panic-variants to enable)
users := models.Users().AllP(db)
// More complex query
users, err := models.Users(Where("age > ?", 30), Limit(5), Offset(6)).All(ctx, db)
// Ultra 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)
// Use any "boil.Executor" implementation (sql.DB, sql.Tx, data-dog mock db)
// for any query.
tx, err := db.BeginTx(ctx, nil)
if err != nil {
  return err
}
users, err := models.Users().All(ctx, tx)
// Relationships
user, err := models.Users().One(ctx, db)
if err != nil {
  return err
}
movies, err := user.FavoriteMovies().All(ctx, db)
// Eager loading
users, err := models.Users(Load("FavoriteMovies")).All(ctx, db)
if err != nil {
  return err
}
fmt.Println(len(users.R.FavoriteMovies))

Requirements & Pro Tips

Requirements

• Go 1.13, older Go versions are not supported.
• Join tables should use a _composite primary key_.

• MySQL 5.6.30 minimum; ssl-mode option is not supported for earlier versions.
• For MySQL if using the github.com/go-sql-driver/mysql driver, please activate

Pro Tips

• SQLBoiler generates type safe identifiers for table names, table column names,

• It's highly recommended to use transactions where sqlboiler will be doing

• Foreign key column names should end with _id.

• If you never plan on using the hooks functionality you can disable generation of this

Getting started

Videos

If you like learning via a video medium, sqlboiler has a number of screencasts available.

_NOTE:_ These videos predate modules (v4), the installation/import paths will be different though everything else should remain similar.

SQLBoiler: Getting Started

SQLBoiler: What's New in v3

SQLBoiler: Advanced Queries and Relationships

Old (v2): SQLBoiler Screencast #1: How to get started

Download

First you have to install the code generator binaries. There's the main binary and then a separate driver binary (select the right one for your database).

Be very careful when installing, there's confusion in the Go ecosystem and knowing what are the right commands to run for which Go version can be tricky. Ensure you don't forget any /v suffixes or you'll end up on an old version.

# Go 1.16 and above:
go install github.com/aarondl/sqlboiler/v4@latest
go install github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-psql@latest
Go 1.15 and below:
Install sqlboiler v4 and the postgresql driver (mysql, mssql, sqlite3 also available)
NOTE: DO NOT run this inside another Go module (like your project) as it will
pollute your go.mod with a bunch of stuff you don't want and your binary
will not get installed.
GO111MODULE=on go get -u -t github.com/aarondl/sqlboiler/v4
GO111MODULE=on go get github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-psql

To install sqlboiler as a dependency in your project use the commands below inside of your go module's directory tree. This will install the dependencies into your go.mod file at the correct version.

# Do not forget the trailing /v4 and /v8 in the following commands
go get github.com/aarondl/sqlboiler/v4
Assuming you're going to use the null package for its additional null types
go get github.com/aarondl/null/v8

Configuration

Create a configuration file. Because the project uses viper, TOML, JSON and YAML are all usable but only TOML is supported. Environment variables are also able to be used.

The configuration file should be named sqlboiler.toml and is searched for in the following directories in this order:

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

##### Database Driver Configuration

The configuration for a specific driver (in these examples we'll use psql) must all be prefixed by the driver name. You must use a configuration file or environment variables for configuring the database driver; there are no command-line options for providing driver-specific configuration.

In the configuration file for postgresql for example you would do:

[psql]
dbname = "your_database_name"

When you use an environment variable it must also be prefixed by the driver name:

PSQL_DBNAME="your_database_name"

The values that exist for the drivers:

| Name | Required | Postgres Default | MySQL Default | MSSQL Default | | ----------- | -------- | ---------------- | ------------- | ------------- | | 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" | | unix-socket | no | N/A | "" | N/A | | whitelist | no | [] | [] | [] | | blacklist | no | [] | [] | [] |

Example of whitelist/blacklist:

[psql]
Removes migrations table, the name column from the addresses table, and
secret_col of any table from being generated. Foreign keys that reference tables
or columns that are no longer generated because of whitelists or blacklists may
cause problems.
blacklist = ["migrations", "addresses.name", "*.secret_col"]

##### Generic config options

You can also pass in these top level configuration values if you would prefer not to pass them through the command line or environment variables:

| Name | Defaults | | ------------------------- | -------- | | pkgname | "models" | | output | "models" | | tag | [] | | debug | false | | add-global-variants | false | | add-panic-variants | false | | add-enum-types | false | | enum-null-prefix | "Null" | | no-context | false | | no-hooks | false | | no-tests | false | | no-auto-timestamps | false | | no-rows-affected | false | | no-driver-templates | false | | no-relation-getters | false | | tag-ignore | [] | | strict-verify-mod-version | false |

##### Full Example

output   = "my_models"
wipe     = true
no-tests = true
add-enum-types = 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"
  tinyint_as_int = true
[mssql]
  dbname  = "dbname"
  host    = "localhost"
  port    = 1433
  user    = "dbusername"
  pass    = "dbpassword"
  sslmode = "disable"
  schema  = "notdbo"

Initial Generation

After creating a configuration file that points at the database we want to generate models for, we can invoke the sqlboiler command line utility.

SQL Boiler generates a Go ORM from template files, tailored to your database schema.
Complete documentation is available at http://github.com/aarondl/sqlboiler
Usage:
  sqlboiler [flags] <driver>
Examples:
sqlboiler psql
Flags:
      --add-global-variants        Enable generation for global variants
      --add-panic-variants         Enable generation for panic variants
      --add-soft-deletes           Enable soft deletion by updating deleted_at timestamp
      --add-enum-types             Enable generation of types for enums
      --enum-null-prefix           Name prefix of nullable enum types (default "Null")
  -c, --config string              Filename of config file to override default lookup
  -d, --debug                      Debug mode prints stack traces on error
  -h, --help                       help for sqlboiler
      --no-auto-timestamps         Disable automatic timestamps for created_at/updated_at
      --no-back-referencing        Disable back referencing in the loaded relationship structs
      --no-context                 Disable context.Context usage in the generated code
      --no-driver-templates        Disable parsing of templates defined by the database driver
      --no-hooks                   Disable hooks feature for your models
      --no-rows-affected           Disable rows affected in the generated API
      --no-tests                   Disable generated go test files
      --no-relation-getters        Disable generating getters for relationship tables
  -o, --output string              The name of the folder to output to (default "models")
  -p, --pkgname string             The name you wish to assign to your generated package (default "models")
      --struct-tag-casing string   Decides the casing for go structure tag names. camel, title, alias or snake (default "snake")
  -t, --tag strings                Struct tags to be included on your models in addition to json, yaml, toml
      --tag-ignore strings         List of column names that should have tags values set to '-' (ignored during parsing)
      --templates strings          A templates directory, overrides the embedded template folders in sqlboiler
      --replace strings  An array of templates file and the actual template file to be replaces 
      --version                    Print the version
      --strict-verify-mod-version  Prevent code generation, if project version of sqlboiler not match with executable
      --wipe                       Delete the output folder (rm -rf) before generation to ensure sanity

Follow the steps below to do some basic model generation. Once you've generated your models, you can run the compatibility tests which will exercise the entirety of the generated code. This way you can ensure that your database is compatible with SQLBoiler. If you find there are some failing tests, please check the Diagnosing Problems section.

# Generate our models and exclude the migrations table
When passing 'psql' here, it looks for a binary called
'sqlboiler-psql' in your CWD and PATH. You can also pass
an absolute path to a driver if you desire.
sqlboiler psql
Run the generated tests
go test ./models

_Note: No mysqldump or pg_dump equivalent for Microsoft SQL Server, so generated tests must be supplemented by tables_schema.sql with CREATE TABLE ... queries_

You can use go generate for SQLBoiler if you want to to make it easy to run the command for your application:

//go:generate sqlboiler --flags-go-here psql

It's important to not modify anything in the output folder, which brings us to the next topic: regeneration.

Regeneration

When regenerating the models it's recommended that you completely delete the generated directory in a build script or use the --wipe flag in SQLBoiler. The reasons for this are that sqlboiler doesn't try to diff your files in any smart way, it simply writes the files it's going to write whether they're there or not and doesn't delete any files that were added by you or previous runs of SQLBoiler. In the best case this can cause compilation errors, in the worst case this may leave extraneous and unusable code that was generated against tables that are no longer in the database.

The bottom line is that this tool should always produce the same result from the same source. And the intention is to always regenerate from a pure state. The only reason the --wipe flag isn't defaulted to on is because we don't like programs that rm -rf things on the filesystem without being asked to.

Controlling Version

When sqlboiler is used on a regular basis, sometimes problems arise on the developers' side that the version they are using does not match the version specified in the project.

Sqlboiler will warn, if version in project and executable mismatch. Sqlboiler can also fail to prevent code generation, when --strict-verify-mod-version flag (or aliased version in toml) is enabled.

Controlling Generation

The templates get executed in a specific way each time. There's a variety of configuration options on the command line/config file that can control what features are turned on or off.

In addition to the command line flags there are a few features that are only available via the config file and can use some explanation.

##### Aliases

In sqlboiler, names are automatically generated for you. If you name your database entities properly you will likely have descriptive names generated in the end. However in the case where the names in your database are bad AND unchangeable, or sqlboiler's inference doesn't understand the names you do have (even though they are good and correct) you can use aliases to change the name of your tables, columns and relationships in the generated Go code.

_Note: It is not required to provide all parts of all names. Anything left out will be inferred as it was in the past._

# Although team_names works fine without configuration, we use it here for illustrative purposes
[aliases.tables.team_names]
up_plural     = "TeamNames"
up_singular   = "TeamName"
down_plural   = "teamNames"
down_singular = "teamName"
# Columns can also be aliased.
  [aliases.tables.team_names.columns]
  team_name = "OurTeamName"

When creating aliases for relationships, it's important to know how sqlboiler names relationships. For a given table the foreign key name is used as a unique identifier to refer to a given relationship. If you are going to be aliasing relationships it's highly recommended that you name your foreign keys explicitly in your database or the auto-generated names could one day change/break your aliases.

Each relationship has a local and a foreign function name. The function name will be inserted into your generated code as a function to retrieve relationship data as well as refer to the relationship in a few other places. local means "the function name that refers to the table with the foreign key on it" and conversely foreign means "the function that refers to the table the foreign key points to".

For example - let's have a videos -> users many to one relationship that looks like this:

The tables and their columns:
| videos  | users |
|---------|-------|
| user_id | id    |
Our foreign key:
videos_user_id_fkey: videos.user_id -> users.id

In this example local (how we refer to the table with the foreign key) is going to be inferred as Videos. We're going to override that below to be AuthoredVideos.

Conversely foreign (how we refer to the table the foreign key points to) is going to be inferred as User, which we'd like to rename to Author to suit our domain language a bit better.

With the configuration snippet below we can use the following relationship helper functions off of the respective models: video.Author and user.AuthoredVideos which make a bit more sense than the inferred names when we see it in the code for our domain. Note the use of the foreign key name to refer to the relationship in the configuration key.

[aliases.tables.videos.relationships.videos_author_id_fkey]
The local side would originally be inferred as AuthorVideos, which
is probably good enough to not want to mess around with this feature, avoid it where possible.
local   = "AuthoredVideos"
Even if left unspecified, the foreign side would have been inferred correctly
due to the proper naming of the foreign key column.
foreign = "Author"

In a many-to-many relationship it's a bit more complicated. Let's look at an example relationship between videos <-> tags with a join table in the middle. Imagine if the join table didn't exist, and instead both of the id columns in the join table were slapped on to the tables themselves. You'd have videos.tag_id and tags.video_id. Using a similar method to the above (local is the name with which we refer to the side that has the foreign key) we can rename the relationships. To change Videos.Tags to Videos.Rags we can use the example below.

Keep in mind that naming ONE side of the many-to-many relationship is sufficient as the other side will be automatically mirrored, though you can specify both if you so choose.

[aliases.tables.video_tags.relationships.fk_video_id]
local   = "Rags"
foreign = "Videos"

The above definition will specify Rags as the name of the property with which a given Video entity will be able to access all of it's tags. If we look the other way around - a single Tag entity will refer to all videos that have that specific tag with the Videos property.

There is an alternative syntax available for those who are challenged by the key syntax of toml or challenged by viper lowercasing all of your keys. Instead of using a regular table in toml, use an array of tables, and add a name field to each object. The only one that changes past that is columns, which now has to have a new field called alias.

[[aliases.tables]]
name          = "team_names"
up_plural     = "TeamNames"
up_singular   = "TeamName"
down_plural   = "teamNames"
down_singular = "teamName"
[[aliases.tables.columns]]
  name  = "team_name"
  alias = "OurTeamName"
[[aliases.tables.relationships]]
  name    = "fk_video_id"
  local   = "Rags"
  foreign = "Videos"

##### Custom Struct Tag Case

Sometimes you might want to customize the case style for different purpose, for example, use camel case for json format and use snake case for yaml, You may create a section named [struct-tag-cases] to define these custom case for each different format:

[struct-tag-cases]
toml = "snake"
yaml = "camel"
json = "camel"
boil = "alias"

By default, the snake case will be used, so you can just setup only few formats:

[struct-tag-cases]
json = "camel"

##### Foreign Keys

You can add foreign keys not defined in the database to your models using the following configuration:

[foreign_keys.jet_pilots_fkey]
table = "jets"
column = "pilot_id"
foreign_table = "pilots"
foreign_column = "id"
[foreign_keys.pilot_language_pilots_fkey]
table = "pilot_languages"
column = "pilot_id"
foreign_table = "pilots"
foreign_column = "id"
[foreign_keys.pilot_language_languages_fkey]
table = "pilot_languages"
column = "language_id"
foreign_table = "languages"
foreign_column = "id"

##### Inflections

With inflections, you can control the rules sqlboiler uses to generates singular/plural variants. This is useful if a certain word or suffix is used multiple times and you do not want to create aliases for every instance.

[inflections.plural]
Rules to convert a suffix to its plural form
ium = "ia"
[inflections.plural_exact]
Rules to convert an exact word to its plural form
stadium = "stadia"

[inflections.singular]
Rules to convert a suffix to its singular form
ia = "ium"

[inflections.singular_exact]
Rules to convert an exact word to its singular form
stadia = "stadium"

[inflections.irregular]
The singular -> plural mapping of an exact word that doen't follow conventional rules
radius = "radii"

##### Types

There exists the ability to override types that the driver has inferred. The way to accomplish this is through the config file.

[[types]]
  # The match is a drivers.Column struct, and matches on almost all fields.
  # Notable exception for the unique bool. Matches are done
  # with "logical and" meaning it must match all specified matchers.
  # Boolean values are only checked if all the string specifiers match first,
  # and they must always match.
  #
  # Not shown here: db_type is the database type and a very useful matcher
  # We can also whitelist tables for this replace by adding to the types.match:
  # tables = ['users', 'videos']
  #
  # Note there is precedence for types.match, more specific things should appear
  # further down in the config as once a matching rule is found it is executed
  # immediately.
  [types.match]
    type = "null.String"
    nullable = true
# The replace is what we replace the strings with. You cannot modify any
  # boolean values in here. But we could change the Go type (the most useful thing)
  # or the DBType or FullDBType etc. if for some reason we needed to.
  [types.replace]
    type = "mynull.String"
# These imports specified here overwrite the definition of the type's "based_on_type"
  # list. The type entry that is replaced is the replaced type's "type" field.
  # In the above example it would add an entry for mynull.String, if we did not
  # change the type in our replacement, it would overwrite the null.String entry.
  [types.imports]
    third_party = ['"github.com/me/mynull"']

##### Imports

Imports are overridable by the user. This can be used in conjunction with replacing the templates for extreme cases. Typically this should be avoided.

Note that specifying any section of the imports completely overwrites that section. It's also true that the driver can still specify imports and those will be merged in to what is provided here.

[imports.all]
  standard = ['"context"']
  third_party = ['"github.com/my/package"']
Changes imports for the boil_queries file
[imports.singleton."boil_queries"]
  standard = ['"context"']
  third_party = ['"github.com/my/package"']
Same syntax as all
[imports.test]
Same syntax as singleton
[imports.test_singleton]
Changes imports when a model contains null.Int32
[imports.based_on_type.string]
  standard = ['"context"']
  third_party = ['"github.com/my/package"']

When defining maps it's possible to use an alternative syntax since viper automatically lowercases all configuration keys (same as aliases).

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

##### Templates

In advanced scenarios it may be desirable to generate additional files that are not go code. You can accomplish this by using the --templates flag to specify all the directories you wish to generate code for. With this flag you specify root directories, that is top-level container directories.

If root directories have a _test suffix in the name, this folder is considered a folder full of templates for testing only and will be omitted when --no-tests is specified and its templates will be generated into files with a _test suffix.

Each root directory is recursively walked. Each template found will be merged into table_name.ext where ext is defined by the shared extension of the templates. The directory structure is preserved with the exception of singletons.

For files that should not be generated for each model, you can use a singleton directory inside the directory where the singleton file should be generated. This will make sure that the file is only generated once.

Here's an example:

templates/
├── 00_struct.go.tpl               # Merged into output_dir/table_name.go
├── 00_struct.js.tpl               # Merged into output_dir/table_name.js
├── singleton
│   └── boil_queries.go.tpl        # Rendered as output_dir/boil_queries.go
└── js
    ├── jsmodel.js.tpl             # Merged into output_dir/js/table_name.js
    └── singleton
        └── jssingle.js.tpl        # Merged into output_dir/js/jssingle.js

The output files of which would be:

output_dir/
├── boil_queries.go
├── table_name.go
├── table_name.js
└── js
    ├── table_name.js
    └── jssingle.js

Note: Because the --templates flag overrides the embedded templates of sqlboiler, if you still wish to generate the default templates it's recommended that you include the path to sqlboiler's templates as well.

templates = [
  "/path/to/sqlboiler/templates",
  "/path/to/sqlboiler/templates_test",
  "/path/to/your_project/more_templates"
]

Extending generated models

There will probably come a time when you want to extend the generated models with some kinds of helper functions. A general guideline is to put your extension functions into a separate package so that your functions aren't accidentally deleted when regenerating. Past that there are 3 main ways to extend the models, the first way is the most desirable:

Method 1: Simple Functions

// Package modext is for SQLBoiler helper methods
package modext
// UserFirstTimeSetup is an extension of the user model.
func UserFirstTimeSetup(ctx context.Context, db sql.DB, u models.User) error { ... }

Code organization is accomplished by using multiple files, and everything is passed as a parameter so these kinds of methods are very easy to test.

Calling code is also very straightforward:

user, err := Users().One(ctx, db)
// elided error check
err = modext.UserFirstTimeSetup(ctx, db, user)
// elided error check

Method 2: Empty struct methods

The above is the best way to code extensions for SQLBoiler, however there may be times when the number of methods grows too large and code completion is not as helpful anymore. In these cases you may consider structuring the code like this:

// Package modext is for SQLBoiler helper 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 { ... }

Calling code then looks a little bit different:

user, err := Users().One(ctx, db)
// elided error check
err = modext.Users.FirstTimeSetup(ctx, db, user)
// elided error check

This is almost identical to the method above, but gives slight amounts more organization at virtually no cost at runtime. It is however not as desirable as the first method since it does have some runtime cost and doesn't offer that much benefit over it.

Method 3: Embedding

This pattern is not for the faint of heart, what it provides in benefits it more than makes up for in downsides. It's possible to embed the SQLBoiler structs inside your own to enhance them. However it's subject to easy breakages and a dependency on these additional objects. It can also introduce inconsistencies as some objects may have no extended functionality and therefore have no reason to be embedded so you either have to have a struct for each generated struct even if it's empty, or have inconsistencies, some places where you use the enhanced model, and some where you do not.

user, err := Users().One(ctx, db)
// elided error check
enhUser := modext.User{user}
err = ehnUser.FirstTimeSetup(ctx, db)
// elided error check

I don't recommend this pattern, but included it so that people know it's an option and also know the problems with it.

Diagnosing Problems

The most common causes of problems and panics are:

• Forgetting to exclude tables you do not want included in your generation, like migration tables.
• Tables without a primary key. All tables require one.
• Forgetting to put foreign key constraints on your columns that reference other tables.
• The compatibility tests require privileges to create a database for testing purposes, ensure the user

• A nil or closed database handle. Ensure your passed in boil.Executor is not nil.

• Naming collisions, if the code fails to compile because there are naming collisions, look at the

• Race conditions in tests or when using global variable models and using

• A field not being inserted (usually a default true boolean), boil.Infer looks at the zero

• decimal library showing errors like: pq: encode: unknown type types.NullDecimal

For errors with other causes, it may be simple to debug yourself by looking at the generated code. Setting boil.DebugMode to true can help with this. You can change the output using boil.DebugWriter (defaults to os.Stdout).

If you're still stuck and/or you think you've found a bug, feel free to leave an issue and we'll do our best to help you.

Features & Examples

Most examples in this section will be demonstrated using the following Postgres schema, structs and variables:

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);
ALTER TABLE jets ADD CONSTRAINT jet_pilots_fkey FOREIGN KEY (pilot_id) REFERENCES pilots(id);
CREATE TABLE languages (
  id integer NOT NULL,
  language text NOT NULL
);
ALTER TABLE languages ADD CONSTRAINT language_pkey PRIMARY KEY (id);
-- Join table
CREATE TABLE pilot_languages (
  pilot_id integer NOT NULL,
  language_id integer NOT NULL
);
-- Composite primary key
ALTER TABLE pilot_languages ADD CONSTRAINT pilot_language_pkey PRIMARY KEY (pilot_id, language_id);
ALTER TABLE pilot_languages ADD CONSTRAINT pilot_language_pilots_fkey FOREIGN KEY (pilot_id) REFERENCES pilots(id);
ALTER TABLE pilot_languages ADD CONSTRAINT pilot_language_languages_fkey FOREIGN KEY (language_id) REFERENCES languages(id);

The generated model structs for this schema look like the following. Note that we've included the relationship structs as well so you can see how it all pieces together:

type Pilot struct {
  ID   int    boil:"id" json:"id" toml:"id" yaml:"id"
  Name string boil:"name" json:"name" toml:"name" yaml:"name"
R *pilotR boil:"-" json:"-" toml:"-" yaml:"-"
  L pilotR  boil:"-" json:"-" toml:"-" yaml:"-"
}
type pilotR struct {
  Languages LanguageSlice
  Jets      JetSlice
}
type Jet struct {
  ID      int    boil:"id" json:"id" toml:"id" yaml:"id"
  PilotID int    boil:"pilot_id" json:"pilot_id" toml:"pilot_id" yaml:"pilot_id"
  Age     int    boil:"age" json:"age" toml:"age" yaml:"age"
  Name    string boil:"name" json:"name" toml:"name" yaml:"name"
  Color   string boil:"color" json:"color" toml:"color" yaml:"color"
R *jetR boil:"-" json:"-" toml:"-" yaml:"-"
  L jetR  boil:"-" json:"-" toml:"-" yaml:"-"
}
type jetR struct {
  Pilot *Pilot
}
type Language struct {
  ID       int    boil:"id" json:"id" toml:"id" yaml:"id"
  Language string boil:"language" json:"language" toml:"language" yaml:"language"
R *languageR boil:"-" json:"-" toml:"-" yaml:"-"
  L languageR  boil:"-" json:"-" toml:"-" yaml:"-"
}
type languageR struct {
  Pilots PilotSlice
}

// Open handle to database like normal
db, err := sql.Open("postgres", "dbname=fun user=abc")
if err != nil {
  return err
}

Automatic CreatedAt/UpdatedAt

If your generated SQLBoiler models package can find columns with the names created_at or updated_at it will automatically set them to time.Now() in your database, and update your object appropriately. To disable this feature use --no-auto-timestamps.

Note: You can set the timezone for this feature by calling boil.SetLocation()

Customizing the timestamp columns

Set the auto-columns map in your configuration file

[auto-columns]
    created = "createdAt"
    updated = "updatedAt"

Skipping Automatic Timestamps

If for a given query you do not want timestamp columns to be re-computed prior to an insert or update then you can use boil.SkipTimestamps on the context you pass in to the query to prevent them from being updated.

Keep in mind this has no effect on whether or not the column is included in the insert/update, it simply stops them from being set to time.Now() in the struct before being sent to the database (if they were going to be sent).

Overriding Automatic Timestamps

• Insert

• Update

• Upsert

Automatic DeletedAt (Soft Delete)

Soft deletes are a way of deleting records in a database for the average query without actually removing the data. This type of thing is important in certain scenarios where data retention is important. It is typically done by adding a deleted bool or a deleted_at timestamp to each table in the database that can be soft deleted and subsequent queries on that table should always make sure that deleted != true or deleted_at is null to prevent showing "deleted" data.

SQLBoiler uses the deleted_at variant to provide this functionality. If your table has a nullable timestamp field named deleted_at it will be a candidate for soft-deletion.

_NOTE_: As of writing soft-delete is opt-in via --add-soft-deletes and is liable to change in future versions.

_NOTE_: There is a query mod to bypass soft delete for a specific query by using qm.WithDeleted, note that there is no way to do this for Exists/Find helpers yet.

_NOTE_: The Delete helpers will _not_ set updated_at currently. The current philosophy is that deleting the object is simply metadata and since it returns in no queries (other than raw ones) the updated_at will no longer be relevant. This could change in future versions if people disagree with this but it is the current behavior.

Query Building

We generate "Starter" methods for you. These methods are named as the plural versions of your model, for example: models.Jets(). Starter methods are used to build queries using our Query Mod System. They take a slice of Query Mods as parameters, and end with a call to a Finisher method.

Here are a few examples:

// SELECT COUNT(*) FROM pilots;
count, err := models.Pilots().Count(ctx, db)
// SELECT * FROM "pilots" LIMIT 5;
pilots, err := models.Pilots(qm.Limit(5)).All(ctx, db)
// DELETE FROM "pilots" WHERE "id"=$1;
err := models.Pilots(qm.Where("id=?", 1)).DeleteAll(ctx, db)
// type safe version of above
err := models.Pilots(models.PilotWhere.ID.EQ(1)).DeleteAll(ctx, db)

In the event that you would like to build a query and specify the table yourself, you can do so using models.NewQuery():

// Select all rows from the pilots table by using the From query mod.
err := models.NewQuery(db, qm.From("pilots")).All(ctx, db)

As you can see, Query Mods allow you to modify your queries, and Finishers allow you to execute the final action.

We also generate query building helper methods for your relationships as well. Take a look at our Relationships Query Building section for some additional query building information.

Query Mod System

The query mod system allows you to modify queries created with Starter methods when performing query building. See examples below.

NOTE: SQLBoiler generates type-safe identifiers based on your database tables, columns and relationships. Using these is a bit more verbose, but is especially safe since when the names change in the database the generated code will be different causing compilation failures instead of runtime errors. It is highly recommended you use these instead of regular strings. See Constants for more details.

NOTE: You will notice that there is printf used below mixed with SQL statements. This is normally NOT OK if the user is able to supply any of the sql string, but here we always use a ? placeholder and pass arguments so that the only thing that's being printf'd are constants which makes it safe, but be careful!

```go // Dot import so we can access query mods directly instead of prefixing with "qm." import . "github.com/aarondl/sqlboiler/v4/queries/qm"

// Use a raw query against a generated struct (Pilot in this example) // If this query mod exists in your call, it will override the others. // "?" placeholders are not supported here, use "$1, $2" etc. SQL("select * from pilots where id=$1", 10) models.Pilots(SQL("select * from pilots where id=$1", 10)).All()

Select("id", "name") // Select specific columns. Select(models.PilotColumns.ID, models.PilotColumns.Name) From("pilots as p") // Specify the FROM table manually, can be useful for doing complex queries. From(models.TableNames.Pilots + " as p")

// WHERE clause building Where("name=?", "John") models.PilotWhere.Name.EQ("John") And("age=?", 24) // No equivalent type safe query yet Or("height=?", 183) // No equivalent type safe query yet

Where("(name=? and age=?) or (age=?)", "John", 5, 6) // Expr allows manual grouping of statements Where( Expr( models.PilotWhere.Name.EQ("John"

... (README truncated for length)