SQLC — A different approach to working with databases in Go

Sqlc is a command line tool for safely generating native Go sql query code and it has been gaining momentum recently.

While ORM libraries like Gorm are great for working with databases in most Golang applications, raw Go sql query code is unmatchable in terms of efficiency. On the other hand, using the standard library sql package alone can be tedious and prone to typing errors. Sqlc is a command line tool that allows you to generate type-safe Go code from the SQL syntax you already know!

Installation:

go get github.com/kyleconroy/sqlc/cmd/sqlc

Sqlc can also be downloaded through your common package managers.

Mac:

brew install sqlc

Ubuntu:

sudo snap install sqlc

Or download binaries from the repository:

https://github.com/kyleconroy/sqlc/releases

Example:

Let’s first initialize your Go application!

Go mod init [your project name]

Then run the command at the top-level of your project’s directory to generate the config file.

sqlc init

Here are the basic config details that should be in that file:

version: 1
packages:
  - path: "tutorial"
    name: "tutorial"
    engine: "mysql"
    schema: "schema.sql"
    queries: "query.sql"

Sqlc currently supports PostgreSQL, and more recently, MySQL. The maintainers also plan to add support for other programming languages in the future, but we will only be working with Go in this article. Specify your db under “engine” in the config.

It’s a good idea to keep the project clean as your data migration and sql query files grow, so we should group the db-related files in subdirectories:

version: 1
packages:
  - name: "db"
    path: "./db/sqlc"
    queries: "./db/query"
    schema: "./db/migration"
    engine: "postgresql"

The sql query files will be located in the “query” directory.

The db migration files will be stored in the “migration” directory.

The generated code will be located in the “sqlc” directory.

You can also specify further configs in the yaml to change generated comments, etc. (That’s for another day)

The project structure might look something like this:

Now let’s see how it works.

First, let’s add the db schema to build the db.

Paste the following in a sql file called table.sql in the migration directory. Other migration sql files can be added here later:

CREATE TABLE authors (
    id BIGSERIAL PRIMARY KEY,
    name text NOT NULL,
    bio text
);

Now switch over to the query directory. Let’s create an author.sql file for all queries related to that table. Now you can write all the crud, aggregate, and other queries that you’ll use:

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
-- name: CreateAuthor :one
INSERT INTO authors (
name, bio
) VALUES (
$1, $2
)
RETURNING *;
-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

The — name: CreateAuthor :one is an annotation that Sqlc looks at to generate comments for the functions.

Command Line:
You can run sqlc compile to type-check your sql code syntax

sqlc compile

and then sqlc generate to generate the go code.

sqlc generate

Now take a look in the sqlc directory (or whichever directory you specified in the path). You can now import and use these CRUD methods in your app however you like!

Now of course, MySQL and PostgreSQL have some rather obscure features and the project is still relatively new. There could be some small cases where type-checking fails, but 99%+ of your sql combinations should work. We also need help expanding this tool to Kotlin and Python, and add support for CockroachDB, so consider contributing to this cool project!

https://github.com/kyleconroy/sqlc