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

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
  • Missing Features
  • Supported Databases
  • A Small Taste
• Requirements & Pro Tips
  • Requirements
  • Pro Tips
• Getting started
  • Videos
  • Download
  • Configuration
  • Initial Generation
  • Regeneration
  • Controlling Version
  • 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?
  • Why are the auto-generated tests failing?
• 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
• Only postgresql is supported

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

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 ins