What SQL database library to use for your Golang projects ?

There are several popular options :

1. database/sql package
2. ORM, for example gorm
3. sqlx library

Each of the above options has tradeoff, because no library or tool is perfect for every use cases. But, I will give you my opinion why sqlc is the best for most use cases after using it in my own project.

Let's iterate over the above options.

1. database/sql package is great. It is very performant and no complex hidden abstractions. But, because of this low-level nature, you must handle everything manually, from Scanning to struct, making sure the query syntax is correct, and updating when you make changes to the query. This manual coding can often lead to bugs and error in runtime.

2. Using Gorm can shorten your code and handle errors in compile time. But you have to learn its methods first and go back and forth to its documentation. Also, it can run very slowly in high load, compared to database/sql package.

3. sqlx package is kind of in between database/sql and Gorm. It is quite performant and provides methods to bridge between Golang and SQL code, so that you don't have to dabble with Scan anymore. Its learning curve also is not steep. Only one weakness though, SQL syntax failure will not be recognized until runtime.

You may think : so, sqlx seems like a perfect choice, why should I use sqlc ?

Why use sqlc

Because using sqlc is basically cheating. No, I mean it.

You write your schema and queries in SQL. You write some YAML config. sqlc generates all Golang code with database/sql for you. As easy as that.

You don't have to learn more ORM syntax, no more manual Scan-ing, you get best performance with database/sql, and every SQL syntax and schema errors will be thrown at compile time. When I experience all those, I basically fall in love with this library.

But, as The Great Linux Torvalds said :

Here it is :

CREATE TABLE authors (
  id         SERIAL PRIMARY KEY,
  bio        text   NOT NULL,
  birth_year int    NOT NULL
);


-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY id;

// This is the Go code that is generated from the above SQL query.
package db

import (
    "context"
    "database/sql"
)

type Author struct {
    ID        int
    Bio       string
    BirthYear int
}

type DBTX interface {
    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
}

const getAuthor = `-- name: GetAuthor :one
SELECT id, bio, birth_year FROM authors
WHERE id = $1
`

func (q *Queries) GetAuthor(ctx context.Context, id int) (Author, error) {
    row := q.db.QueryRowContext(ctx, getAuthor, id)
    var i Author
    err := row.Scan(&i.ID, &i.Bio, &i.BirthYear)
    return i, err
}

const listAuthors = `-- name: ListAuthors :many
SELECT id, bio, birth_year FROM authors
ORDER BY id
`

func (q *Queries) ListAuthors(ctx context.Context) ([]Author, error) {
    rows, err := q.db.QueryContext(ctx, listAuthors)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var items []Author
    for rows.Next() {
        var i Author
        if err := rows.Scan(&i.ID, &i.Bio, &i.BirthYear); 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
}

So many codes generated for just a few lines of query. sqlc will also handle corner cases such as sql.Null values and other possibility of errors right off the bat.

For more code examples you can jump straight to its documentation : https://docs.sqlc.dev/en/stable/howto/update.html

Supported Databases and Languages

As the time I write this article, sqlc can support PostgreSQL and MySQL while support for SQLite is still in Beta. On side note, sqlc can also support Phyton and Kotlin but it's still in Beta stage. The maintainers also plan to support C# and Typescript in the future.

TL;DR

sqlc is a powerful tool that can make your life easier when working with SQL and Go. It's type-safe, performant, easy to use, and open source. If you're using Postgres or MySQL and looking for a way to make your SQL development more enjoyable, more efficient and reliable, then I highly recommend giving sqlc a try.

See you !

Connect with me on Linkedin : https://www.linkedin.com/in/fadhil-radhian
PS : I also provide Technical Writing service. Contact me if you're interested!