curvegrid/sqlboiler repository overview

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 goose⁠, sql-migrate⁠ or some other migration tool to manage this part of the database's life-cycle.

⁠Note on v2 vs v3

v3 has been released, please upgrade when possible, v2 is on life support only now.

⁠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.

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⁠
  • Why another ORM⁠
  • About SQL Boiler⁠
    • Features⁠
    • Supported Databases⁠
    • A Small Taste⁠
  • Requirements & Pro Tips⁠
    • Requirements⁠
    • Pro Tips⁠
  • Getting started⁠
    • Videos⁠
    • Download⁠
    • Configuration⁠
    • Initial Generation⁠
    • Regeneration⁠
    • Controlling Generation⁠
      • Aliases⁠
      • Types⁠
      • Imports⁠
      • Templates⁠
    • Extending Generated Models⁠
  • Diagnosing Problems⁠
  • Features & Examples⁠
    • Automatic CreatedAt/UpdatedAt⁠
      • Skipping Automatic Timestamps⁠
      • Overriding Automatic Timestamps⁠
    • Query Building⁠
    • Query Mod System⁠
    • Function Variations⁠
    • Finishers⁠
    • Raw Query⁠
    • Binding⁠
    • Relationships⁠
    • Hooks⁠
      • Skipping Hooks⁠
    • Transactions⁠
    • Debug Logging⁠
    • Select⁠
    • Find⁠
    • Insert⁠
    • Update⁠
    • Delete⁠
    • Upsert⁠
    • Reload⁠
    • Exists⁠
    • Enums⁠
    • Constants⁠
  • FAQ⁠
    • Won't compiling models for a huge database be very slow?⁠
    • Missing imports for generated package⁠
    • How should I handle multiple schemas⁠
    • How do I use the types.BytesArray for Postgres bytea arrays?⁠
    • Why aren't my time.Time or null.Time fields working in MySQL?⁠
    • Where is the homepage?⁠
• Benchmarks⁠

⁠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
• 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

⁠Supported Databases

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/volatiletech/sqlboiler/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.6 minimum, and Go 1.7 for compatibility tests.
• Table names and column names should use snake_case format.
  • We require snake_case table names and column names. This is a recommended default in Postgres, and we agree that it's good form, so we're enforcing this format for all drivers for the time being.
• 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⁠ 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⁠ 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.

SQLBoiler: Getting Started⁠

SQLBoiler: What's New in v3⁠

SQLBoiler: Advanced Queries and Relationships⁠

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

⁠Download

go get -u -t github.com/volatiletech/sqlboiler

# Also install the driver of your choice, there exists psql, mysql, mssql
# These are separate binaries.
go get github.com/volatiletech/sqlboiler/drivers/sqlboiler-psql

⁠Configuration

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

The configuration file should be named sqlboiler.toml for TOML, sqlboiler.json for JSON and sqlboiler.yaml or sqlboiler.yml for YAML 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.

We require you pass in your psql and mysql database configuration via the configuration file rather than env vars. There is no command line argument support for database configuration. Values given under the postgres and mysql block are passed directly to the psql and mysql drivers. Here is a rundown of all the different values that can go in that section:

Example of whitelist/blacklist:

[psql]
# Removes migrations table, and the name column from the addresses 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"]

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:

Example:

[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"

⁠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/volatiletech/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
  -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-context                 Disable context.Context usage in the generated code
      --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
  -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 or snake (default "snake")
  -t, --tag strings                Struct tags to be included on your models in addition to json, yaml, toml
      --templates strings          A templates directory, overrides the bindata'd template folders in sqlboiler
      --version                    Print the version
      --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 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"

⁠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
  [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 definitio