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.
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.
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.
Out of the box, SQLBoiler includes the following features:
To demonstrate some of the features of SQLBoiler, we’ll design a simple schema for a college database management system and generate models.
You can get started quickly by installing the SQLBoiler package in an existing Go module project. You’ll need the following:
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
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.
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
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 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.
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
.
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.
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
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 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 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.
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.
Install LogRocket via npm or script tag. LogRocket.init()
must be called client-side, not
server-side
$ npm i --save logrocket // Code: import LogRocket from 'logrocket'; LogRocket.init('app/id');
// Add to your HTML: <script src="https://cdn.lr-ingest.com/LogRocket.min.js"></script> <script>window.LogRocket && window.LogRocket.init('app/id');</script>
Would you be interested in joining LogRocket's developer community?
Join LogRocket’s Content Advisory Board. You’ll help inform the type of content we create and get access to exclusive meetups, social accreditation, and swag.
Sign up nowSOLID principles help us keep code flexible. In this article, we’ll examine all of those principles and their implementation using JavaScript.
JavaScript’s Date API has many limitations. Explore alternative libraries like Moment.js, date-fns, and the new Temporal API.
Explore use cases for using npm vs. npx such as long-term dependency management or temporary tasks and running packages on the fly.
Validating and auditing AI-generated code reduces code errors and ensures that code is compliant.