SQLBoiler Documentation

SQLBoiler is currently in maintenance mode. This means:

1. New features are generally not accepted.
2. Bug fixes and community-provided version compatibility changes are accepted.
3. Maintainers may not resolve all reported issues; community help is encouraged.

Versioning

• v1, v2, and v3: No longer maintained.
• v3: The last version compatible with GOPATH.
• v4: The only maintained version. It uses Go modules and is not compatible with GOPATH projects.

If you require an actively maintained ORM or tool, consider:

• Bob: A tool directly inspired by SQLBoiler, created by one of its maintainers.
• sqlc: A command-line tool that generates type-safe code from SQL. It is not an ORM but serves many similar use cases.

To use SQLBoiler effectively, ensure your environment and database schema meet the following requirements:

• Go Version: Requires Go 1.13 or higher.
• Join Tables: For transparent relationship handling, join tables must use a composite primary key that encompasses both foreign keys and contains no other columns.
  • Example: A user_videos table should have primary key(user_id, video_id) where both are foreign keys.
• MySQL:
  • Minimum version: 5.6.30 (earlier versions do not support the ssl-mode option).
  • Driver Configuration: If using github.com/go-sql-driver/mysql, you must activate time.Time parsing. SQLBoiler relies on time.Time and null.Time; without this enabled, models with DATE/DATETIME columns will fail to work.

You can override the Go types that the driver infers from the database using the [[types]] array in your configuration. This is useful for using custom null types or third-party libraries.

[[types]]
  [types.match]
    type = "null.String"
    nullable = true

  [types.replace]
    type = "mynull.String"

  [types.imports]
    third_party = ['"github.com/me/mynull"']

If your database schema uses names that are difficult to work with in Go, you can use aliases in your sqlboiler.toml to rename tables, columns, and relationships.

Tables and Columns: Use [aliases.tables.<table_name>] to define plural/singular names and [aliases.tables.<table_name>.columns] for specific column renames.

Relationships: Relationships are identified by their foreign key name. You can define local (the table with the FK) and foreign (the table the FK points to) names.

# Table and Column Aliases
[aliases.tables.team_names]
up_plural     = "TeamNames"
up_singular   = "TeamName"

  [aliases.tables.team_names.columns]
  team_name = "OurTeamName"

# Relationship Aliases
[aliases.tables.videos.relationships.videos_author_id_fkey]
local   = "AuthoredVideos"
foreign = "Author"

Follow these recommendations to improve type safety, performance, and code clarity:

• Use Type-Safe Identifiers: Instead of using raw strings for table names, column names, relationship names, or WHERE clauses, use the type-safe identifiers generated by SQLBoiler. This allows the Go compiler to catch errors when your database schema changes.
• Use Transactions: When performing multiple database calls (such as relationship set operations involving insertions), use transactions to ensure both data integrity and better performance.
• Naming Conventions: Name foreign key columns with the _id suffix (e.g., x_id). This convention results in clearer, more intuitive method names in the generated code.
• Optimize Binary Size: If you do not require the hooks functionality, you can reduce your binary size by using the --no-hooks flag during generation.

To include SQLBoiler and the recommended null package in your project's go.mod file, run the following commands inside your module's directory. Ensure you include the /v4 and /v8 suffixes.

go get github.com/aarondl/sqlboiler/v4
go get github.com/aarondl/null/v8

SQLBoiler generates "Starter" methods for each model (e.g., models.Pilots()). These methods accept Query Mods and must end with a Finisher method to execute.

Common patterns:

• Count(): Get the number of rows.
• All(): Retrieve all matching rows.
• One(): Retrieve a single row.
• DeleteAll(): Delete all matching rows.

// 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
err := models.Pilots(models.PilotWhere.ID.EQ(1)).DeleteAll(ctx, db)

SQLBoiler is a database-first ORM. Unlike 'code-first' ORMs (such as GORM or Gorp), you must define your database schema first.

It is recommended to use a migration tool like sql-migrate to manage your database lifecycle before using SQLBoiler to generate models.