Using sqlc Library for Golang Projects Will Make Your Life Easier
Tags: go, api, database, backend
Published: 2023-05-24 10:06:57

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

![Linus Torvalds quote](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vydqr6mz0lbc6p05cvhb.jpg)

Here it is :


```SQL
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;
```

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


![sqlc supported languages and DBs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ad7rjvj7jxxo46k4qhn4.png)


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