Generate CRUD Golang code from SQL | Compare db/sql, gorm, sqlx, sqlc
Tags: go, database, tutorial, beginners
Published: 2020-07-13 12:04:59

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.

{% youtube prh0hTyI1sU %}

Here's:

- Link to the [full series playlist on Youtube](https://bit.ly/backendmaster)
- And its [Github repository](https://github.com/techschool/simplebank)

## What is CRUD?

![crud](https://dev-to-uploads.s3.amazonaws.com/i/z7doutqmb34hjq5ti443.png)

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.

![libraries](https://dev-to-uploads.s3.amazonaws.com/i/pkh1m4vcoz6h9hg5e9cs.png)

### Standard `database/sql` package

The 1st one is to use the low-level standard library [database/sql](https://golang.org/pkg/database/sql/) package.

![db/sql](https://dev-to-uploads.s3.amazonaws.com/i/izf8sfj623r5cv929lhn.png)

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](https://gorm.io/), 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.

![gorm-cread](https://dev-to-uploads.s3.amazonaws.com/i/1vdsmfk3x9rdkzgiekez.png)

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.

![gorm-association](https://dev-to-uploads.s3.amazonaws.com/i/f7e1efr7azczcp3nurb2.png)

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](https://github.com/jmoiron/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.

![sqlx](https://dev-to-uploads.s3.amazonaws.com/i/w8fgfe6fekx18wpbwbic.png)

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](https://sqlc.dev/)!

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.

![sqlc](https://dev-to-uploads.s3.amazonaws.com/i/sb8pli6s508eir0tz015.png)

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.

![sqlc-generated](https://dev-to-uploads.s3.amazonaws.com/i/nwrxune88yw17y29akl4.png)

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](https://github.com/kyleconroy/sqlc), 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:

```bash
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.

![sqlc-help](https://dev-to-uploads.s3.amazonaws.com/i/6w7xxw3s2iyxuqkocy5m.png)

- 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:

```bash
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.

![sqlc-setting](https://dev-to-uploads.s3.amazonaws.com/i/blm6rrooxkuu3mug37n3.png)

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.

```yaml
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

```bash
sqlc generate
```

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

![sql-gen-error](https://dev-to-uploads.s3.amazonaws.com/i/gu70v2x90fdhzt8ff09v.png)

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.

```Makefile
...

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`.

![getting-started](https://dev-to-uploads.s3.amazonaws.com/i/87d4dlxqmrk9udr06fb2.png)

### 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.

```sql
-- 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`.

```go
// 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.

```go
// 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.

```go
// 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`.

![red-lines](https://dev-to-uploads.s3.amazonaws.com/i/szs6eorij3wxmjhoehix.png)

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:

```bash
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.

```bash
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

```sql
-- 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`.

![insert-error](https://dev-to-uploads.s3.amazonaws.com/i/skpsygxdjbrf5ev9lzsu.png)

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`.

![read-ops](https://dev-to-uploads.s3.amazonaws.com/i/6qbhtn9i43u4dpy1uvks.png)

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:

```sql
-- 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.

```sql
-- 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.

```go
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.

```go
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`.

<!-- image -->

### Write SQL query to update account

![update](https://dev-to-uploads.s3.amazonaws.com/i/5pn0xg3vk2k3f5vf5d4g.png)

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

```sql
-- 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.

```go
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:

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

Then regenerate the code.

```go
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

![delete](https://dev-to-uploads.s3.amazonaws.com/i/app6y1r9et6hjuj6chmc.png)

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.

```sql
-- 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.

```go
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](https://github.com/techschool/simplebank) 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](http://bit.ly/techschoolguru) and [follow us on Twitter](https://twitter.com/techschoolguru) 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](https://bit.ly/voodoojobs). Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.