What SQL database library to use for your Golang projects ?
There are several popular options :
-
database/sql
package - ORM, for example
gorm
-
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.
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.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 todatabase/sql
package.sqlx
package is kind of in betweendatabase/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 withScan
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 withdatabase/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!
Top comments (0)