SQLBoiler
ORM generator. Generate a featureful and blazing-fast ORM tailored to your database schema
About SQLBoiler
This large README is shown as a plain-text preview. Read the full README at the source
[](https://github.com/aarondl/sqlboiler/blob/master/LICENSE)
[](https://pkg.go.dev/mod/github.com/aarondl/sqlboiler/v4)
[](https://sqlboiler.from-the.cloud)
[](http://goreportcard.com/report/aarondl/sqlboiler)
# Maintenance Mode
This package is currently in maintenance mode, which means:
1. It generally does not accept new features.
2. It does accept bug fixes and version compatability changes provided by the community.
3. Maintainers usually do not resolve reported issues.
4. Community members are encouraged to help each other with reported issues.
## Alternatives
If looking for an actively maintained alternative, consider the following:
### 1. Bob - <https://github.com/stephenafamo/bob>
Bob is very similar to SQLBoiler. It was directly inspired by SQLBoiler and was created by a maintainer of SQLBoiler.
A comparison can be found here: <https://bob.stephenafamo.com/vs/sqlboiler/>.
### 2. sqlc - <https://github.com/sqlc-dev/sqlc>
`sqlc` is a command line tool that generates type-safe code from SQL.
It is not an ORM but for many use cases it can be a good alternative to SQLBoiler.
# About SQLBoiler
SQLBoiler is a tool to generate a Go ORM tailored to your database schema.
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](https://github.com/rubenv/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](#benchmarks) and optimize the hot-paths, perform like hand-rolled `sql.DB` code.
We believe with SQLBoiler and our database-first code-generation approach we've been able to successfully meet all of these goals. On top
of that SQLBoiler also confers the following benefits:
- 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](#sqlboiler)
- [Why another ORM](#why-another-orm)
- [About SQL Boiler](#about-sql-boiler)
- [Features](#features)
- [Missing Features](#missing-features)
- [Supported Databases](#supported-databases)
- [A Small Taste](#a-small-taste)
- [Requirements & Pro Tips](#requirements--pro-tips)
- [Requirements](#requirements)
- [Pro Tips](#pro-tips)
- [Getting started](#getting-started)
- [Videos](#videos)
- [Download](#download)
- [Configuration](#configuration)
- [Initial Generation](#initial-generation)
- [Regeneration](#regeneration)
- [Controlling Version](#controlling-version)
- [Controlling Generation](#controlling-generation)
- [Aliases](#aliases)
- [Types](#types)
- [Imports](#imports)
- [Templates](#templates)
- [Extending Generated Models](#extending-generated-models)
- [Diagnosing Problems](#diagnosing-problems)
- [Features & Examples](#features--examples)
- [Automatic CreatedAt/UpdatedAt](#automatic-createdatupdatedat)
- [Skipping Automatic Timestamps](#skipping-automatic-timestamps)
- [Overriding Automatic Timestamps](#overriding-automatic-timestamps)
- [Query Building](#query-building)
- [Query Mod System](#query-mod-system)
- [Function Variations](#function-variations)
- [Finishers](#finishers)
- [Raw Query](#raw-query)
- [Binding](#binding)
- [Relationships](#relationships)
- [Hooks](#hooks)
- [Skipping Hooks](#skipping-hooks)
- [Transactions](#transactions)
- [Debug Logging](#debug-logging)
- [Select](#select)
- [Find](#find)
- [Insert](#insert)
- [Update](#update)
- [Delete](#delete)
- [Upsert](#upsert)
- [Reload](#reload)
- [Exists](#exists)
- [Enums](#enums)
- [Constants](#constants)
- [FAQ](#faq)
- [Won't compiling models for a huge database be very slow?](#wont-compiling-models-for-a-huge-database-be-very-slow)
- [Missing imports for generated package](#missing-imports-for-generated-package)
- [How should I handle multiple schemas](#how-should-i-handle-multiple-schemas)
- [How do I use the types.BytesArray for Postgres bytea arrays?](#how-do-i-use-typesbytesarray-for-postgres-bytea-arrays)
- [Why aren't my time.Time or null.Time fields working in MySQL?](#why-arent-my-timetime-or-nulltime-fields-working-in-mysql)
- [Where is the homepage?](#where-is-the-homepage)
- [Why are the auto-generated tests failing?](#why-are-the-auto-generated-tests-failing)
- [Benchmarks](#benchmarks)
- [Third-Party Extensions](#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
- Only postgresql is supported
### Supported Databases
| Database | Driver Location |
| ----------------- | --------------------------------------------------------------------------------------------------- |
| PostgreSQL | [https://github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-psql](drivers/sqlboiler-psql) |
| MySQL | [https://github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-mysql](drivers/sqlboiler-mysql) |
| MSSQLServer 2012+ | [https://github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-mssql](drivers/sqlboiler-mssql) |
| SQLite3 | [https://github.com/aarondl/sqlboiler/v4/drivers/sqlboiler-sqlite3](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](#features--examples).
```go
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_.
- For join tables to be used transparently for relationships your join table must have
a _composite primary key_ that encompasses both foreign table foreign keys and
no other columns in the table. For example, on a join table named
`user_videos` you should have: `primary key(user_id, video_id)`, with both
`user_id` and `video_id` being foreign key columns to the users and videos
tables respectively and there are no other columns on this table.
- 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
[time.Time parsing](https://github.com/go-sql-driver/mysql#timetime-support) when making your
MySQL database connection. SQLBoiler uses `time.Time` and `null.Time` to represent time in
it's models and without this enabled any models with `DATE`/`DATETIME` columns will not work.
### Pro Tips
- SQLBoiler generates type safe identifiers for table names, table column names,
a table's relationship names and type-safe where clauses. You should use these
instead of strings due to the ability to catch more errors at compile time
when your database schema changes. See [Constants](#constants) for details.
- It's highly recommended to use transactions where sqlboiler will be doing
multiple database calls (relationship setops with insertions for example) for
both performance and data integrity.
- Foreign key column names should end with `_id`.
- Foreign key column names in the format `x_id` will generate clearer method names.
It is advisable to use this naming convention whenever it makes sense for your database schema.
- If you never plan on using the hooks functionality you can disable generation of this
feature using the `--no-hooks` flag. This will save you some binary size.
## 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](https://www.youtube.com/watch?v=y5utRS9axfg)
[SQLBoiler: What's New in v3](https://www.youtube.com/watch?v=-B-OPsYRZJA)
[SQLBoiler: Advanced Queries and Relationships](https://www.youtube.com/watch?v=iiJuM9NR8No)
[Old (v2): SQLBoiler Screencast #1: How to get started](https://www.youtube.com/watch?v=fKmRemtmi0Y)
#### 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.
```shell
# 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.
```shell
# 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](https://github.com/spf13/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/`
We will assume TOML for the rest of the documentation.
##### 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:
```toml
[psql]
dbname = "your_database_name"
```
When you use an environment variable it must also be prefixed by the driver
name:
```sh
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:
```toml
[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
```toml
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.
```text
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 tablesFrequently Asked Questions
What is SQLBoiler?
SQLBoiler is a ORM library for the Go programming language. ORM generator. Generate a featureful and blazing-fast ORM tailored to your database schema
How many GitHub stars does SQLBoiler have?
SQLBoiler has 6,983 GitHub stars in the directory's latest synchronization.
How do I install SQLBoiler?
Install SQLBoiler with the Go module system using `go get volatiletech/sqlboiler`. Check the repository for the current installation instructions.
What category does SQLBoiler belong to?
SQLBoiler is listed under ORM, specifically ORM.