Introduction to SQLBoiler: Go framework for ORMs

Object Relational Mapping, ORM, is a programming technique that allows you to convert data between two incompatible type systems.

When building software, typically, you’d have a database layer and an application layer that holds your business logic. Usually, the way that data is stored in the database is not compatible with your chosen programming language, meaning you have to manipulate data between the database and your application layer.

Database ORMs make this process easier by abstracting away the boilerplate, letting you interact with your data in the same language in which you’ve written your business logic. In this article, we’ll explore SQLBoiler, a tool for generating Go ORMs.

Why should you use SQLBoiler?

Most programming languages offer a wide range of libraries that provide the features of ORMs. Go is no exception. While SQLBoiler is not as widely adopted as some of its alternatives like Ent, it has been in active development for more than five years and brings a whole new dimension to how we reason about database interactions.

One of the more obvious drawbacks of traditional ORMs is the tradeoff when it comes to type safety for your models. Due to the lack of generics in Go, these libraries rely on the use of reflection under the hood to handle schema changes, which can significantly harm your app’s performance. However, with SQLBoiler, you get fully type-safe models through the code generated from your database schema.

Database-first vs. code-first approach

SQLBoiler takes a database-first approach, implying that you manage your own database schema, and models are generated from the defined schema. Because your models accurately match the types you defined in your schema, this approach gives you the advantage of predictability.

A code-first approach is the opposite, meaning you define your models or entities and allow the ORM to create your database and tables accordingly. One advantage this gives you is the ability to make changes to the database from your code.

SQLBoiler features

Out of the box, SQLBoiler includes the following features:

• Complete model generation
• Eager loading
• Raw SQL fallback
• Transactions
• Model hooks
• Multi-schema support
• Handling complex table relationships

Getting Started with SQLBoiler

To demonstrate some of the features of SQLBoiler, we’ll design a simple schema for a college database management system and generate models.

Requirements:

You can get started quickly by installing the SQLBoiler package in an existing Go module project. You’ll need the following:

• Go v≥ 1.13
• Database: In this article, we’ll use Postgres

Create a Go module project:

$ mdkir <project-name>
$ cd <project-name>
$ go mod init <your-github-url>

If you’ve never used SQLBoiler, download the code-gen binaries:

$ go install github.com/volatiletech/sqlboiler/v4@latest
$ go install github.com/volatiletech/sqlboiler/v4/drivers/sqlboiler-psql@latest

Finally, install SQLBoiler:

$ go get github.com/volatiletech/sqlboiler/v4

Database configuration

In the config file, we’ll specify database connection options and other code-gen flags. To get started quickly, you can create a sqlboiler.toml file in the root of your project, paste this example configuration from GitHub, and update the necessary fields.

Defining a schema

First, we define a database schema in the schema.sql file:

// schema.sql
drop table if exists students;
drop table if exists departments;
drop table if exists staffs;
drop table if exists classes;

create table students (
   id serial not null primary key,
   firstname varchar not null,
   lastname varchar not null,
   email varchar not null,
   admission_number varchar not null,
   year int not null,
   cgpa float not null
);

create table classes (
   id serial not null primary key,
   title varchar not null,
   code varchar not null,
   unit int not null,
   semester int not null,
   location varchar not null
);

create table departments (
   id serial not null primary key,
   name varchar not null,
   code varchar not null,
   telephone varchar not null,

   foreign key (user_id) references users (id)
);

create table staffs (
   id serial not null primary key,
   firstname varchar not null,
   lastname varchar not null,
   email varchar not null,
   telephone varchar not null,
   salary bigint not null,
);

create table classes_students (
   class_id int not null,
   student_id int not null,

   primary key (class_id, student_id),
   foreign key (student_id) references students (id),
   foreign key (class_id) references classes (id)
);

create table classes_instructors (
   class_id int not null,
   staff_id int not null,

   primary key (class_id, staff_id),
   foreign key (staff_id) references staffs (id),
   foreign key (class_id) references classes (id)
);

insert into users (name) values ('Franklin');
insert into users (name) values ('Theressa');

SQLBoiler doesn’t provide a migration tool out of the box, but there are plenty of options from the community. sql-migrate is the recommended tool to use, however, in this context, we’ll just load the schema file directly into the database as follows:

$ psql --username <user> --password <password> < schema.sql

Generating models

Next, we’ll use the SQLBoiler CLI to generate our models from the define schema. One interesting part of this step is that the CLI also generates tests for your models. You can run these tests to ensure that your models conform to the defined schema. You can also skip tests using the --no-tests flag to reduce your application binary.

Check out the list of flags supported by the CLI. You can either define flags in your sqlboiler.toml file, or pass them as arguments to the CLI command. To generate your models, run the following command:

$ sqlboiler psql -c sqlboiler.toml --wipe --no-tests

The command above will create a models directory containing all your database models. Just like that, you have a full, type-safe ORM to interact with your database. If you excluded the --no-tests flag, you can run go test ./models to run the generated tests.

SQLBoiler Query Mod System

SQLBoiler generates starter methods, which are the entry points for you to begin querying any of your models. An example starter method would look like models.Students(), in which Students represent the student model.

Query mods let you specify the type of queries you want to make, for example, qm.Where("age=?", 2) translates to a where clause.

SQLBoiler generates these methods for every SQL clause you might need. With the help of auto-completion, you can see all the possible clauses when you type qm.

Finishers serve as endpoints that you attach to the end of your query for you to execute. For example, let’s say you want to fetch all students from your college management database. The limiter will be .All(ctx, db). Other finishers include .One(ctx, db), .Count(ctx, db), and .Exists(ctx, db).

You’ll build your queries in SQLBoiler through a combination of starters, query mods, and finishers. Let’s look at the complete example of working with the Query Mod System:

// initialize a db connection
db, err := sql.Open("postgres", `dbname=<dbname> host=localhost user=<user> password=<password>`)
if err != nil {} // handle err

// Fetch all students
students, err := models.Students().All(ctx, db)
if err != nil {} // handle err

// Fetch single student
student, err := models.Students(qm.Where("id=?", 1).One(ctx, db)
if err != nil {} // handle err

// Count all students in database
count, err := models.Students().Count(ctx, db)

SQLBoiler doesn’t force you to use certain conventions. If you want to make very specific SQL queries, you can easily create a raw query like the following:

var department models.Department
err := db.Raw("select * from departments where population between 1500 and 3200").Bind(ctx, db, &department)
if err != nil {} // handle err

When creating raw queries, you need to bind to a struct, either one generated by SQLBoiler or one that you custom define.

Relationships

Handling relationships between tables is a breeze in SQLBoiler, which generates helper methods for any type of relationship you define in your schema via foreign keys, like 1-1, 1-n, or m-n.

One common performance bottleneck with ORMs is the n+1 query problem that occurs when querying tables that include joins.

Let’s say that we want to query our database for a list of students in a department. We run a query to fetch all students, but now you also want to include all the classes that each student attends. You loop through your students results and fetch all classes, implying that for every student, you’re making an additional query to the database to fetch their classes.

If we had N students, we’re making N extra queries, which is unnecessary because we could have fetched all classes along with each students in the initial query. SQLBoiler provides an elegant solution to this problem via eager loading, which significantly reduces the number of queries made to the database.

If you look at the schema we defined above, you’ll notice that the departments table holds a foreign key user_id that references the users table. This is a 1-n relationship in which a department has many students.

We also have a join-table called classes-students that holds foreign keys referencing the classes and students tables. This is a m-n relationship in which a class can have many students and a student can belong to many classes.

Let’s look at examples of how we can query relationships with eager loading:

//...
// fetch department including students
department, err := models.Departments(qm.Where("id=?", 1), qm.Load(models.DepartmentRels.Student)).One(ctx, db)
if err != nil {} // handle err

We can even combine Query Mods to filter the data that was eager loaded:

//...
// fetch classes including students with cgpa >= 2.6
classes, err := models.Classes(qm.Load(models.ClassRels.Student, qm.Where("cgpa >= ?", 2.6))).All(ctx, db)
if err != nil {} // handle err

For each class, the query above will only return students with cgpa greater than or equal to 2.6.

CRUD operations

We’ve already seen how we can perform queries with the Query Mod system. Now, let’s explore other CRUD operations like creating, updating, and deleting entities.

Creating an entity

To create an entity, we simply declare an instance of the model, set the required fields, and make a call to Insert. With the third argument to Insert, we’ll specify what columns to include. Infer tells SQLBoiler to handle that automatically, but if you want more fine-grained control over the columns, other options include Whitelist, Blacklist, and Greylist:

//...
// create a department
var department models.Department
department.Name = "Computer Science"
department.Code = "CSC"
department.Telephone = "+1483006541"
err := department.Insert(ctx, db, boil.Infer())
if err != nil {} // handle err

Updating an entity

Just like inserting an entity, carrying out updates is just as intuitive. We fetch the entity, set the fields to new values, and make a call to Update:

//...
// update a student
student, err := models.FindStudent(ctx, db, 1)
if err != nil {} // handle err

student.year = 4
student.cgpa = 4.0

_, err := student.Update(ctx, db, boil.Infer())
if err != nil {} // handle err

Deleting an entity

Deleting an entity is very similar. Fetch the entity from the database and call the Delete method:

//...
// delete a student
student, err := models.FindStudent(ctx, db, 1)
if err != nil {} // handle err

_, err := student.Delete(ctx, db)
if err != nil {} // handle err

// delete multiple classes
classes, err := models.Classes(qm.Where("unit < ?", 3)).All(ctx, db)
if err != nil {} // handle err

_, err := classes.DeleteAll(ctx, db)
if err != nil {} // handle err

Transactions

Transactions let us group multiple SQL statements as one atomic operation, ensuring that all statements either run successfully, or if one or more statements should fail, revert the database to the previous state when the transaction started.

Let’s say we’re creating a new department. It would also make sense to create one or more classes that belong to that department. However, in the event that one of these operations fails, we don’t want a dangling class row in the database that doesn’t point to any department. Here, transactions can be useful:

//...
// start a transaction
tx, err := db.BeginTx(ctx, nil)
if err != nil {} // handle err

// create a department
var department models.Department
department.Name = "Computer Science"
department.Code = "CSC"
department.Telephone = "+1483006541"
err = department.Insert(ctx, tx, boil.Infer())
if err != nil {
  // rollback transaction
  tx.Rollback()
}

// create a class
var class models.Class
class.Title = "Database Systems"
class.Code = "CSC 215"
class.Unit = 3
class.Semester = "FIRST"
err = class.Insert(ctx, tx, boil.Infer())
if err != nil {
  // rollback transaction
  tx.Rollback()
}

// add class to department
class, err := models.Classes(qm.Where("code=?", "CSC 215")).One(ctx, tx)
department, err := models.Departments(qm.Where("code=?", "CSC")).One(ctx, tx)
err = department.AddClasses(ctx, tx, class)
if err != nil {
  // rollback transaction
  tx.Rollback()
}

// commit transaction
tx.Commit()

First, we start a transaction by calling BeginTx, which returns tx, a database handle that will be used throughout the transaction lifetime. We create a department and a class, then add that class to the department entity.

In case of errors, we call the Rollback method to restore the state of the database to what it was when our transaction started. If everything succeeds, we simply call the Commit method to persist the changes.

Conclusion

In this article, we’ve learned how to work with SQLBoiler and leverage its code generation feature to seamlessly interact with a database using fully type-safe models and helper methods.

If you have an existing database that you want to build a project on, then SQLBoiler is definitely a great option. Of course, SQLBoiler may not always be the best choice for your unique use case. You might find yourself in a situation where you don’t know how your database schema is going to turn out, and you just want to start with a few data points.

In this case, a code-first ORM is probably ideal. Also, the lack of a built-in migration tool might be a drawback to your development experience, meaning other ORMs like Ent may be a better option. Like anything in software development, using the right tool for the job will give you the best results.

I hope you enjoyed this article, and leave a comment if you have any questions.