Generate CRUD Golang code from SQL | Compare db/sql, gorm, sqlx, sqlc

Welcome back to the backend master class!

In the last lecture, we have learned how to write migration script to create the database schema for our simple bank project.

Today we will learn how to write golang codes to perform CRUD operations on the database.

Here's:

• Link to the full series playlist on Youtube
• And its Github repository

What is CRUD?

Well, they are 4 basic operations:

• C stands for Create, or insert new records to the database.
• R is for Read, which means retrieving the records from the database.
• U is Update, to change the content of the records in the database
• And D is Delete, to remove the records from the database.

Which library to use?

There are several ways to implement CRUD operations in golang.

Standard database/sql package

The 1st one is to use the low-level standard library database/sql package.

As you can see in this example, we just use the QueryRowContext() function, pass in the raw SQL query and some parameters. Then we scan the result into target variables.

The main advantage of this approach is it runs very fast, and writing codes is pretty straightforward.

However, its downside is we have to manually map the SQL fields to variables, which is quite boring and easy to make mistakes. If somehow the order of variables doesn’t match, or if we forget to pass some arguments to the function call, the errors will only show up at runtime.

Gorm

Another approach is to use Gorm, which is a high-level object-relational-mapping library for Golang.

It is super convenient to use because all CRUD operations are already implemented. So our production code will be very short, as we only need to declare the models and call the functions that gorm provided.

As you can see in these example codes, we have the NewRecord() and Create() function for record creation. And several functions for retrieving the data, such as First(), Take(), Last(), Find().

It looks pretty cool, but the problem is: we must learn how to write queries using gorm’s provided functions. It will be annoying if we don’t know which functions to use.

Especially when we have some complex queries that require joining tables, we have to learn how to declare associations tags to make gorm understand the relationships between tables, so that it can generate the correct SQL query.

For me, I prefer writing the SQL query myself. It’s more flexible, and I have a complete control of what I want the database to do.

One major concern when using gorm is that it runs very slowly when the traffic is high. There are some benchmarks on the internet which shows that gorm can runs 3-5 times slower than the standard library.

Sqlx

Because of this, many people are switching to a middle-way approach, which is using sqlx library.

It runs nearly as fast as the standard library, and it’s very easy to use. The fields mapping are done via either the query text or struct tags.

It provides some functions like Select() or StructScan(), which will automatically scan the result into the struct fields, so we don’t have to do the mapping manually like in the database/sql package. This will help to shorten the codes, and reduce potential mistakes.

However, the code that we have to write is still quite long. And any errors in the query will only be caught at runtime.

So is there any better way?

Sqlc

The answer is sqlc!

It runs very fast, just like database/sql. It’s super easy to use. And the most exciting thing is, we just need to write SQL queries, then golang CRUD codes will be automatically generated for us.

As you can see in this example, we simply pass the db schema and SQL queries to sqlc. Each query have 1 comment on top of it to tell sqlc to generate the correct function signature.

Then sqlc will generate idiomatic Golang codes, which uses the standard database/sql library.

And becauses sqlc parses the SQL queries to understand what it does in order to generate the codes for us, so any errors will be caught and report right away. Sounds amazing, right?

The only issue that I found in sqlc is that, at the moment, it only fully supports Postgres. MySQL is still experimental. So if you’re using Postgres in your project, I think sqlc is the right tool to use. Otherwise, I would suggest to stick with sqlx.

Install Sqlc

Alright, now I’m gonna show you how to install and use sqlc to generate CRUD codes for our simple bank project.

First we open its github page, then search for "installation".

I’m on a mac, so I will use Homebrew. Let’s copy this brew install command and run it in the terminal:

brew install kyleconroy/sqlc/sqlc

OK, sqlc is now installed!

We can run sqlc version to see what version it is running. In my case, it’s version 1.4.0

Let’s run sqlc help to learn how to use it.

• First we have the compile command to check the SQL syntax and type errors.
• Then the most important command is generate. It will do both error checking and generating golang codes from SQL queries for us.
• We also have the init command to create an empty slqc.yaml settings file.

Write a setting file

Now I’m gonna go to the simple bank project folder that we’ve been working on in previous lectures. Run:

sqlc init

And open it with visual studio code. We can see the sqlc.yaml file. Right now, it’s kind of empty. So let’s go back to the sqlc github page, select branch with tag v1.4.0, and search for settings.

Let's copy the list of settings and paste it to the sqlc.yaml file.

We can tell sqlc to generate multiple Go packages. But to be simple, I’m just gonna use 1 single package for now.

version: "1"
packages:
  - name: "db"
    path: "./db/sqlc"
    queries: "./db/query/"
    schema: "./db/migration/"
    engine: "postgresql"
    emit_json_tags: true
    emit_prepared_queries: false
    emit_interface: false
    emit_exact_table_names: false

• The name option here is to tell sqlc what is the name of the Go package that will be generated. I think db is a good package name.
• Next, we have to specify the path to the folder to store the generated golang code files. I’m gonna create a new folder sqlc inside the db folder, and change this path string to ./db/sqlc.
• Then we have the queries option to tell sqlc where to look for the SQL query files. Let’s create a new folder query inside the db folder. Then change this value to ./db/query.
• Similarly, this schema option should point to the folder containing the database schema or migration files. In our case, it is ./db/migration.
• The next option is engine to tell sqlc what database engine we would like to use. We’re using Postgresql for our simple bank project. If you want to experiment with MySQL, you can change this value to mysql instead.
• Here we set the emit_json_tags to true because we want sqlc to add JSON tags to the generated structs.
• The emit_prepared_queries tells sqlc to generate codes that work with prepared statement. At the moment, we don’t need to optimize performance yet, so let’s set this to false to make it simple.
• Then the emit_interface option to tell sqlc to generate Querier interface for the generated package. It might be useful later if we want to mock the db for testing higher-level functions. For now let’s just set it to false.
• The final option is emit_exact_table_names. By default, this value is false. Sqlc will try to singularize the table name to use as the model struct name. For example accounts table will become Account struct. If you set this option to true, the struct name will be Accounts instead. I think singular name is better because 1 object of type Accounts in plural form might be confused as multiple objects.

Run sqlc generate command

OK now let’s open the terminal and run

sqlc generate

We have an error because there are no queries in the query folder yet.

We will write the queries in a moment. For now, let’s add a new sqlc command to the Makefile. It will help our team mates to easily find all commands that can be used for development in one place.

...

sqlc:
    sqlc generate

.PHONY: postgres createdb dropdb migrateup migratedown sqlc

CREATE operation

Now let’s write our first SQL query to CREATE an account. I’m gonna create a new account.sql file inside the db/query folder.

Then go back to the sqlc github page and search for getting started.

Write SQL query to create account

Here we see a few examples of how the SQL query should look like. Let’s copy the CreateAuthor command and paste it to our account.sql file.

It’s just a basic INSERT query. The only special thing is the comment on top of it. This comment will instruct sqlc how to generate the Golang function signature for this query.

In our case, the name of the function will be CreateAccount. And it should return 1 single Account object, so we have the :one label here.

-- name: CreateAccount :one
INSERT INTO accounts (
  owner,
  balance,
  currency
) VALUES (
  $1, $2, $3
) RETURNING *;

We don’t need to provide the id because it’s an auto increment column. Every time a new record is inserted, the database will automatically increase the account id sequence number, and use it as the value of the id column.

The created_at column will also be automatically filled with the default value, which is the time when the record is created.

So, we only need to provide values for the owner, balance, and currency. There are 3 columns, so we need to pass 3 arguments into the VALUES clause.

Finally the RETURNING * clause is used to tell Postgres to return the value of all columns after inserting the record into accounts table (including id and created_at). This is very important, because after the account is created, we will always want to return its ID to the client.

Generate Go code to create account

Alright, now let’s open the terminal and run make sqlc.

Then get back to visual studio code. In the db/sqlc folder, we can see 3 new generated files.

The 1st one is models.go. This file contains the struct definition of 3 models: Account, Entry and Transfer.

// Code generated by sqlc. DO NOT EDIT.

package db

import (
  "time"
)

type Account struct {
  ID        int64     `json:"id"`
  Owner     string    `json:"owner"`
  Balance   int64     `json:"balance"`
  Currency  string    `json:"currency"`
  CreatedAt time.Time `json:"created_at"`
}

type Entry struct {
  ID        int64 `json:"id"`
  AccountID int64 `json:"account_id"`
  // can be negative or positive
  Amount    int64     `json:"amount"`
  CreatedAt time.Time `json:"created_at"`
}

type Transfer struct {
  ID            int64 `json:"id"`
  FromAccountID int64 `json:"from_account_id"`
  ToAccountID   int64 `json:"to_account_id"`
  // must be positive
  Amount    int64     `json:"amount"`
  CreatedAt time.Time `json:"created_at"`
}

They all have JSON tags because we’re setting emit_json_tags to true in sqlc.yaml. The Amount field of Entry and Transfer struct also has a comment on top because we added them in the database schema definition in previous lecture.

The 2nd file is db.go. This file contains the DBTX interface. It defines 4 common methods that both sql.DB and sql.Tx object has. This allows us to freely use either a db or a transaction to execute a query.

// Code generated by sqlc. DO NOT EDIT.

package db

import (
  "context"
  "database/sql"
)

type DBTX interface {
  ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
  PrepareContext(context.Context, string) (*sql.Stmt, error)
  QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
  QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

func New(db DBTX) *Queries {
  return &Queries{db: db}
}

type Queries struct {
  db DBTX
}

func (q *Queries) WithTx(tx *sql.Tx) *Queries {
  return &Queries{
    db: tx,
  }
}

As you can see here, the New() function takes a DBTX as input and returns a Queries object. So we can pass in either a sql.DB or sql.Tx object depends on whether we want to execute just 1 single query, or a set of multiple queries within a transaction.

There’s also a method WithTx(), which allows a Queries instance to be associated with a transaction. We will learn more about this in another lecture about transaction.

The 3rd file is account.sql.go file.

// Code generated by sqlc. DO NOT EDIT.
// source: account.sql

package db

import (
  "context"
)

const createAccount = `-- name: CreateAccount :one
INSERT INTO accounts (
  owner,
  balance,
  currency
) VALUES (
  $1, $2, $3
) RETURNING id, owner, balance, currency, created_at
`

type CreateAccountParams struct {
  Owner    string `json:"owner"`
  Balance  int64  `json:"balance"`
  Currency string `json:"currency"`
}

func (q *Queries) CreateAccount(ctx context.Context, arg CreateAccountParams) (Account, error) {
  row := q.db.QueryRowContext(ctx, createAccount, arg.Owner, arg.Balance, arg.Currency)
  var i Account
  err := row.Scan(
    &i.ID,
    &i.Owner,
    &i.Balance,
    &i.Currency,
    &i.CreatedAt,
  )
  return i, err
}

The package name is db as we defined in the sqlc.yaml file.

At the top, we can see the create account SQL query. It looks almost the same as the one we’ve written in the account.sql file, except for the RETURN clause. Sqlc has replaced RETURN * with the name of all columns explicitly. This makes the query clearer and avoid scanning values in incorrect order.

Then we have the CreateAccountParams struct, which contains all columns that we want to set when we create a new account: owner, balance, currency.

The CreateAccount() function is defined as a method of the Queries object. It has this name because we have instructed sqlc with the comment in our SQL query. This function takes a context and a CreateAccountParams object as input, and it returns an Account model object or an error.

Visual studio code is showing some red lines here because we haven’t initialise the module for our project yet.

Let’s open the terminal and run:

go mod init github.com/techschool/simplebank

Our module name is github.com/techschool/simplebank. Now the go.mod file is generated for us. Let’s run following command to install any dependencies.

go mod tidy

Alright, now get back to the account.sql.go file. All the red lines are gone.

In the CreateAccount() function, we call QueryRowContext() to execute the create-account SQL query. This function belongs to the DBTX interface that we’ve seen before. We pass in the context, the query, and 3 arguments: owner, balance, and currency.

The function returns a row object that we can use to scan the value of each column into correct variables. This is the basic code that we often have to write manually if we use the standard database/sql library. But how cool it is to have it automatically generated for us! Awesome, right?

One more amazing thing about sqlc is: it checks the SQL query syntax before generating the codes. So here if I try to remove the 3rd argument in the query

-- name: CreateAccount :one
INSERT INTO accounts (
  owner,
  balance,
  currency
) VALUES (
  $1, $2, $3
) RETURNING *;

and run make sqlc again, an error is reported: INSERT has more target columns than expressions.

Because of this, if sqlc successfully generates the codes, we can be confident that there’s no silly mistake in our SQL queries.

One important thing when working with sqlc is we should not modify the content of the generated files, because everytime we run make sqlc, all of those files will be regenerated, and our changes will be lost. So make sure to create new files if you want to add more codes to the db package.

Alright, now we know how to create records in the database.

READ operations (GET/LIST)

Let’s move to the next operation: READ.

In this example, there are 2 basic data retrieval queries: Get and List. Let’s copy them to our account.sql file.

Write SQL query to get/list accounts

The get query is used to get 1 account record by id. So I’m gonna change its name to GetAccount. And the query will be:

-- name: GetAccount :one
SELECT * FROM accounts
WHERE id = $1 LIMIT 1;

We use LIMIT 1 here because we just want to select 1 single record.

The next operation is ListAccounts. It will return multiple accounts records, so we use the :many label here.

-- name: ListAccounts :many
SELECT * FROM accounts
ORDER BY id
LIMIT $1
OFFSET $2;

Similar to the GetAccount query, we select from accounts table, then order the records by their IDs.

Since there can be a lot of accounts in the database, we should not select all of them at once. Instead, we will do pagination. So we use LIMIT to set the number of rows we want to get, and use OFFSET to tell Postgres to skip this many rows before starting to return the result.

And that’s it!

Generate Go code to get/list account

Now let’s run make sqlc to regenerate the codes, and open the account.sql.go file.

Here we go, the GetAccount() and ListAccounts() functions are generated. Just like before, sqlc has replaced SELECT * with explicit column names for us.

const getAccount = `-- name: GetAccount :one
SELECT id, owner, balance, currency, created_at FROM accounts
WHERE id = $1 LIMIT 1
`

func (q *Queries) GetAccount(ctx context.Context, id int64) (Account, error) {
  row := q.db.QueryRowContext(ctx, getAccount, id)
  var i Account
  err := row.Scan(
    &i.ID,
    &i.Owner,
    &i.Balance,
    &i.Currency,
    &i.CreatedAt,
    )
  return i, err
}

The GetAccount() function just take a context and an account ID as input. And inside, it just calls QueryRowContext() with the raw SQL query and the account ID. It scans the row into an account object and return it to the caller. Pretty simple!

The ListAccounts function is a little bit more complex. It takes a context, a limit and offset parameters as input and returns a list of Account objects.

const listAccounts = `-- name: ListAccounts :many
SELECT id, owner, balance, currency, created_at FROM accounts
ORDER BY id
LIMIT $1
OFFSET $2
`

type ListAccountsParams struct {
  Limit  int32 `json:"limit"`
  Offset int32 `json:"offset"`
}

func (q *Queries) ListAccounts(ctx context.Context, arg ListAccountsParams) ([]Account, error) {
  rows, err := q.db.QueryContext(ctx, listAccounts, arg.Limit, arg.Offset)
  if err != nil {
    return nil, err
  }
  defer rows.Close()
  var items []Account
  for rows.Next() {
    var i Account
    if err := rows.Scan(
      &i.ID,
      &i.Owner,
      &i.Balance,
      &i.Currency,
      &i.CreatedAt,
    ); err != nil {
      return nil, err
    }
    items = append(items, i)
  }
  if err := rows.Close(); err != nil {
    return nil, err
  }
  if err := rows.Err(); err != nil {
    return nil, err
  }
  return items, nil
}

Inside, it calls QueryContext(), pass in the list accounts query together with the limit and offset.

This function returns a rows object. It works like an iterator, which allows us to run through the records one by one, and scan each record into an account object and append it to the list of items.

Finally it closes the rows to avoid leaking db connection. It also checks if there are any errors or not before returning the items to the caller.

The codes look quite long, but easy to understand. The bottom line is: it runs very fast! And we don’t have to worry about making silly mistakes in the code because sqlc already guarantees that the generated codes will work perfectly.

UPDATE operation

OK, let’s move to the next operation: UPDATE.

Write SQL query to update account

Let’s copy this code to our account.sql file and change the function name to UpdateAccount().

-- name: UpdateAccount :exec
UPDATE accounts
SET balance = $2
WHERE id = $1;

Here we use a new label :exec because this command doesn’t return any data, it just updates 1 row in the database.

Let’s say we only allow updating the account balance. The account owner and currency should not be changed.

We use the WHERE clause to specify the id of the account we want to update. And that’s it!

Generate Go code to update account

Now run make sqlc in the terminal to regenerate the codes. And voilà, we have the UpdateAccount() function.

const updateAccount = `-- name: UpdateAccount :exec
UPDATE accounts
SET balance = $2
WHERE id = $1
`

type UpdateAccountParams struct {
  ID      int64 `json:"id"`
  Balance int64 `json:"balance"`
}

func (q *Queries) UpdateAccount(ctx context.Context, arg UpdateAccountParams) error {
  _, err := q.db.ExecContext(ctx, updateAccount, arg.ID, arg.Balance)
  return err
}

It takes a context, the account id and balance parameters as input. All it does is calling ExecContext() with the query and input arguments, then return the error to the caller.

Return updated row

Sometimes, it is useful to have the updated account object being returned as well. In that case, we can change the :exec label to :one, and add RETURNING * at the end of this update query:

-- name: UpdateAccount :one
UPDATE accounts
SET balance = $2
WHERE id = $1
RETURNING *;

Then regenerate the code.

const updateAccount = `-- name: UpdateAccount :one
UPDATE accounts
SET balance = $2
WHERE id = $1
RETURNING id, owner, balance, currency, created_at
`

type UpdateAccountParams struct {
  ID      int64 `json:"id"`
  Balance int64 `json:"balance"`
}

func (q *Queries) UpdateAccount(ctx context.Context, arg UpdateAccountParams) (Account, error) {
  row := q.db.QueryRowContext(ctx, updateAccount, arg.ID, arg.Balance)
  var i Account
  err := row.Scan(
    &i.ID,
    &i.Owner,
    &i.Balance,
    &i.Currency,
    &i.CreatedAt,
  )
  return i, err
}

Now the SQL query has changed, and the UpdateAccount() function is returning the updated Account together with the error. Cool!

DELETE operation

The last operation is DELETE. It’s even simpler than update.

Write SQL query to delete account

Let’s copy this example query and change the function name to DeleteAccount. I don’t want to postgres to return the deleted record, so let’s use :exec label.

-- name: DeleteAccount :exec
DELETE FROM accounts
WHERE id = $1;

Generate Go code to delete account

Let's run make sqlc to regenerate the code. And now we have the DeleteAccount() function in the code.

const deleteAccount = `-- name: DeleteAccount :exec
DELETE FROM accounts
WHERE id = $1
`

func (q *Queries) DeleteAccount(ctx context.Context, id int64) error {
  _, err := q.db.ExecContext(ctx, deleteAccount, id)
  return err
}

So basically we have learned how to generate a full CRUD operations for our accounts table. You can try to do the same thing for the 2 remaining tables: entries and transfers by yourself as an exercise.

I will push the code to github at this repository so that you can have a reference in case you want to take a look.

And that’s it for today’s lecture. Thanks a lot for reading and see you guys in the next one!

If you like the article, please subscribe to our Youtube channel and follow us on Twitter for more tutorials in the future.

If you want to join me on my current amazing team at Voodoo, check out our job openings here. Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.