SQL in Go with SQLBoiler

Getting Started #

SQLBoiler is a package that introspects your database schema and generates a static ORM client. In this article I am going to give you a taste of SQLBoiler, this is not an in-depth article on SQLBoiler (that will come later).

Below is what the workflow looks like with SQLBoiler.

Go SQLBoiler workflow

I highly recommend you to follow the article and replicate everything on your local machine. The code for this article is available in this GitHub repository, please go ahead and clone it.

This is a fairly information packaged article 📦 so give some good amount of time to read it.

Installing SQLBoiler CLI tool #

To generate the static ORM client you need the SQLBoiler cli, install it using go install.

go install github.com/volatiletech/sqlboiler/v4@latest

SQLBoiler requires you to install a driver based on the database you are using (list of supported databases by SQLBoiler).

We will be using PostgreSQL for this article, install the postgres driver.

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

CLI part is all done ✅ let's move on to the project.

Project Setup #

SQLBoiler reads the schema directly from your database. We need a database with a schema. I am not going to spend time on schema creation.

I have already provided a sample schema in the GitHub repository of this article, all you need is Docker installed on your machine. Make sure you have cloned this repository.

Setting up database #

Once the project is cloned, run docker compose up in the project folder and you will have the database ready to go.

Here are the create table queries that run behind the scenes.

CREATE TABLE author(
  id serial primary key,
  email varchar not null,
  name varchar not null
);

CREATE TABLE article(
  id serial primary key,
  title varchar not null,
  body text,
  created_at timestamp default now(),
  author_id int not null,
  constraint fk_author_id foreign key(author_id) references author(id)
);

You have got two tables, author and article, the relationship between them is pretty self explanatory.

Go SQLBoiler Schema

Setting up go project #

Go ahead a create a new go project and initialize it with go mod init as we will be installing a handful of packages.

Install the following packages in the project.

go get github.com/lib/pq
go get github.com/volatiletech/sqlboiler/v4
go get github.com/volatiletech/null/v8

github.com/lib/pq is the go postgres driver, if you have ever wrote SQL in Go, you know you need a database driver.
github.com/volatiletech/sqlboiler/v4 is the SQLBoiler package, the generated ORM code relies on this package.
github.com/volatiletech/null/v8 is package for better handling of nil types in Go.

Next, create the main.go file.

package main

import (
	_ "github.com/lib/pq"
)

func main() {
}
main.go

Generating Code with SQLBoiler #

Before we generate the ORM client, we need to tell SQLBoiler how to connect to our database i.e. we need a configuration file, SQLBoiler stores it configuration in file called sqlboiler.toml.

Creating SQLBoiler configuration file #

Create a new file called sqlboiler.toml. I have documented what each property means in the code below.

# Directory in which the generated static code is to be kept.
output = "db/models" 

# Each time new code is generated, completely wipe out the old one.
wipe = true

# The `go` package name used in the generated code.
pkgname = "dbmodels"

# Adds global variant of the generated functions, more on this later.
add-global-variants = true

# Generates code for enum types as well.
add-enum-types = true

# This is the postgres config, which should be self explanatory.
[psql]
dbname = "postgres"
host = "localhost"
port = 2345
user = "postgres"
pass = "postgres"
sslmode = "disable"
sqlboiler.toml

Generating the static ORM #

Now that cli is installed and the configuration file is setup, you can generate the ORM client.

sqlboiler psql

This will generate the static code in db/models directory 🎉. You can take a look at the generated code here.

Using generated code #

Now is the juciy part 😋 let's start uing the generate code.

First of all let's write some initialization code.

package main

import (
	"database/sql"

	_ "github.com/lib/pq"
	"github.com/volatiletech/sqlboiler/v4/boil"
)

func main() {
	db := connectDB()

	boil.SetDB(db)
}

func connectDB() *sql.DB {
	db, err := sql.Open("postgres", "postgres://postgres:postgres@localhost:2345/postgres?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	return db
}
main.go

connectDB is a function that relies on Go's inbuilt sql package to connect to the postgres database.

Global variants: boil.SetDB(db) is an interesting one, so SQLBoiler can generate global variants of the functions, these functions are appended with a G at the end of the function name. For example the Insert function will have a InsertG variant, the difference is that when using Insert function you will each time have to pass the db instance to it, whereas InsertG will use the db instance set using boil.SetDB(db), freeing you up from the hassle of passing db around the functions.

Creating Author #

Write a function that creates a new author and insert it into the database.

package main

import (
	"context"
	"database/sql"
	"log"

	dbmodels "github.com/gurleensethi/go-sql-boiler-example/db/models"
	_ "github.com/lib/pq"
	"github.com/volatiletech/sqlboiler/v4/boil"
)

func main() {
	ctx := context.Background()
	db := connectDB()

	boil.SetDB(db)

	author := createAuthor(ctx)
}

// 👇 function that creates a new author
func createAuthor(ctx context.Context) dbmodels.Author {
	author := dbmodels.Author{
		Name:  "John Doe",
		Email: "[email protected]",
	}

	err := author.InsertG(ctx, boil.Infer()) // 👈 insert author
	if err != nil {
		log.Fatal(err)
	}

	return author
}
main.go

Run the code using go run main.go.

SQLBoiler generates a lot of useful types, it will generate a type for each table which has many functions on it, one of them is InsertG (global variant) which is used to insert data into db.

Database Log

If you running the database provided in the example repository of this article, I have enabled logging on the database so you can see exactly what queries are being run my SQLBoiler.

LOG:  execute <unnamed>: INSERT INTO "author" ("email","name") VALUES ($1,$2) RETURNING "id"
DETAIL:  parameters: $1 = '[email protected]', $2 = 'John Doe'

Creating Article #

Write a function that creates a new article and insert it into the database.

package main

import (
	"context"
	"database/sql"
	"log"

	dbmodels "github.com/gurleensethi/go-sql-boiler-example/db/models"
	_ "github.com/lib/pq"
	"github.com/volatiletech/null/v8"
	"github.com/volatiletech/sqlboiler/v4/boil"
)

func main() {
	ctx := context.Background()
	db := connectDB()

	boil.SetDB(db)

	author := createAuthor(ctx)
	createArticle(ctx, author) // 👈 new article
	createArticle(ctx, author) // 👈 new article
}

// 👇 function that creates a new article
func createArticle(ctx context.Context, author dbmodels.Author) dbmodels.Article {
	article := dbmodels.Article{
		Title:    "Hello World",
		Body:     null.StringFrom("Hello world, this is an article."),
		AuthorID: author.ID,
	}

	err := article.InsertG(ctx, boil.Infer()) // 👈 insert article
	if err != nil {
		log.Fatal(err)
	}

	return article
}
main.go

Database query log:

LOG:  execute <unnamed>: INSERT INTO "article" ("title","body","created_at","author_id") VALUES ($1,$2,$3,$4) RETURNING "id"
DETAIL:  parameters: $1 = 'Hello World', $2 = 'Hello world, this is an article.', $3 = '2022-08-06 20:11:49.012108', $4 = '1'
LOG:  execute <unnamed>: INSERT INTO "article" ("title","body","created_at","author_id") VALUES ($1,$2,$3,$4) RETURNING "id"
DETAIL:  parameters: $1 = 'Hello World', $2 = 'Hello world, this is an article.', $3 = '2022-08-06 20:11:49.016963', $4 = '1'

Selecting Author and Article #

In this piece of code, we are selecting an Author by ID and then selecting all the Articles created by that author.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	dbmodels "github.com/gurleensethi/go-sql-boiler-example/db/models"
	_ "github.com/lib/pq"
	"github.com/volatiletech/null/v8"
	"github.com/volatiletech/sqlboiler/v4/boil"
)

func main() {
	ctx := context.Background()
	db := connectDB()

	boil.SetDB(db)

	author := createAuthor(ctx)
	createArticle(ctx, author)
	createArticle(ctx, author)
	selectAuthorWithArticle(ctx, author.ID)
}

// 👇 function that selects author and articles.
func selectAuthorWithArticle(ctx context.Context, authorID int) {
	author, err := dbmodels.Authors(dbmodels.AuthorWhere.ID.EQ(authorID)).OneG(ctx)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("Author: \n\tID:%d \n\tName:%s \n\tEmail:%s\n", author.ID, author.Name, author.Email)

	articles, err := author.Articles().AllG(ctx)
	if err != nil {
		log.Fatal(err)
	}

	for _, a := range articles {
		fmt.Printf("Article: \n\tID:%d \n\tTitle:%s \n\tBody:%s \n\tCreatedAt:%v\n", a.ID, a.Title, a.Body.String, a.CreatedAt.Time)
	}
}
main.go

Databae query log:

LOG:  execute <unnamed>: SELECT "author".* FROM "author" WHERE ("author"."id" = $1) LIMIT 1;
DETAIL:  parameters: $1 = '1'
LOG:  execute <unnamed>: SELECT "article".* FROM "article" WHERE ("article"."author_id"=$1);
DETAIL:  parameters: $1 = '1'

Eager loading articles #

SQLBoiler allows for earger loading of data, in our case we can load the articles of an author when we are fetching the articles. Update the selectAuthorWithArticle to have eager loading.

package main

import (
	// ...other imports
	"github.com/volatiletech/sqlboiler/v4/queries/qm"
)

// ...other code

func selectAuthorWithArticle(ctx context.Context, authorID int) {
	author, err := dbmodels.Authors(
		dbmodels.AuthorWhere.ID.EQ(authorID),
		qm.Load(dbmodels.AuthorRels.Articles), // 👈 eager loading articles
	).OneG(ctx)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("Author: \n\tID:%d \n\tName:%s \n\tEmail:%s\n", author.ID, author.Name, author.Email)

	for _, a := range author.R.Articles {
		fmt.Printf("Article: \n\tID:%d \n\tTitle:%s \n\tBody:%s \n\tCreatedAt:%v\n", a.ID, a.Title, a.Body.String, a.CreatedAt.Time)
	}
}
main.go

Eagerly loaded relations are stored in the .R property, so here it is author.R.Articles.

Database query log:

LOG:  execute <unnamed>: SELECT "author".* FROM "author" WHERE ("author"."id" = $1) LIMIT 1;
DETAIL:  parameters: $1 = '2'
LOG:  execute <unnamed>: SELECT * FROM "article" WHERE ("article"."author_id" IN ($1));
DETAIL:  parameters: $1 = '2'

Joining Tables #

If you observe the database query logs, eager loading is still sending out two queries, what if we want to fetch this data in a single query, well we can do that using using a join.

Let's use the NewQuery function to create a custom query. Updating the selectAuthorWithArticle to fetch data using a join.

package main

import (
	// ...other imports
	"github.com/volatiletech/sqlboiler/v4/queries/qm"
)

// ...other code

func selectAuthorWithArticle(ctx context.Context, authorID int) {
	// since each database row will contain info for author and article.
	type AuthorAndArticle struct {
		Article dbmodels.Article `boil:"articles,bind"`
		Author  dbmodels.Author  `boil:"author,bind"`
	}

	authorAndArticles := make([]AuthorAndArticle, 0)

	err := dbmodels.NewQuery(
		qm.Select("*"),
		qm.From(dbmodels.TableNames.Author),
		qm.InnerJoin("articles on articles.author_id = author.id"),
		dbmodels.AuthorWhere.ID.EQ(authorID),
	).BindG(ctx, &authorAndArticles)
	if err != nil {
		log.Fatal(err)
	}

	for _, authorAndArticle := range authorAndArticles {
		author := authorAndArticle.Author
		a := authorAndArticle.Article

		fmt.Printf("Author: \n\tID:%d \n\tName:%s \n\tEmail:%s\n", author.ID, author.Name, author.Email)
		fmt.Printf("Article: \n\tID:%d \n\tTitle:%s \n\tBody:%s \n\tCreatedAt:%v\n", a.ID, a.Title, a.Body.String, a.CreatedAt.Time)
	}
}
main.go

Database query log:

LOG:  execute <unnamed>: SELECT * FROM "author" INNER JOIN article on article.author_id = author.id WHERE ("author"."id" = $1);
DETAIL:  parameters: $1 = '4'

As you can observe, only one query is being sent to the database.

Ending #

In my opinion SQLBoiler has a lot of potential to be the go-to SQL package in Go, there are still some small quircks in the library but once addressed it will go 🚀 🌕. Definitely visit the SQLBoiler's official documentation and dive deep into available functionality.

Thank you for reading this article 🙏🏻 if you learned something new consider joining the mailing list to get notified about articles likes these 😀.

TheDeveloperCafe

TheDeveloperCafe

SQL in Go with SQLBoiler

Getting Started #

Installing SQLBoiler CLI tool #

Project Setup #

Setting up database #

Setting up go project #

Generating Code with SQLBoiler #

Creating SQLBoiler configuration file #

Generating the static ORM #

Using generated code #

Creating Author #

Creating Article #

Selecting Author and Article #

Eager loading articles #

Joining Tables #

Ending #

TheDeveloperCafe

TheDeveloperCafe

SQL in Go with SQLBoiler

Subscribe

Getting Started #

Installing SQLBoiler CLI tool #

Project Setup #

Setting up database #

Setting up go project #

Generating Code with SQLBoiler #

Creating SQLBoiler configuration file #

Generating the static ORM #

Using generated code #

Creating Author #

Creating Article #

Selecting Author and Article #

Eager loading articles #

Joining Tables #

Ending #

Subscribe