sqlc: An Introduction to SQL Compilation

sqlc is a powerful tool that lets you write SQL queries in your preferred programming language. It works by parsing your SQL queries into Go code, which you can then compile and run just like any other Go program.

In this article, I will give you a quick introduction to sqlc and show you how to use it in your own projects.

Install sqlc using the following command:

go install `sqlc`.dev/`sqlc`/cmd/`sqlc`@latest

Once you have sqlc installed, you can create a new project by running the following command:

`sqlc` init

This will create a new sqlc project with a sample schema and query file. You can edit these files to fit your needs.

The first thing you’ll want to do is create a schema for your database. This is done by creating a .sql file in the db directory of your project. Here’s an example schema file:

-- name: create_users
-- up
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

-- name: create_posts
-- up
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    title TEXT NOT NULL,
    body TEXT NOT NULL
);

-- name: drop_users
-- down
DROP TABLE users;

-- name: drop_posts
-- down
DROP TABLE posts;

This schema defines two tables, users and posts, with some basic columns.

Once you have a schema, you can start writing queries to interact with your database. Queries are written in .sql files in the query directory of your project.

Here’s an example query file:

-- name: get_user_by_email
-- statement: select id, name, email from users where email = $1
SELECT id, name, email FROM users WHERE email = $1;

This query retrieves a user from the users table based on their email address.

Once you’ve written your schema and queries, you can compile them using sqlc. This will generate Go code that you can use in your own projects.

To compile your queries, run the following command:

`sqlc` generate

This will generate Go code in the db directory of your project. Here’s an example of what the generated code looks like:

package db

import (
	"context"
	"fmt"

	"github.com/jackc/pgx/v4/pgxpool"
)

type DB struct {
	*pgxpool.Pool
}

func NewDB(pool *pgxpool.Pool) *DB {
	return &DB{
		pool,
	}
}

func (db *DB) GetUserByEmail(ctx context.Context, email string) (User, error) {
	var user User
	err := db.QueryRow(ctx, `SELECT id, name, email FROM users WHERE email = $1`, email).Scan(&user.ID, &user.Name, &user.Email)
	if err != nil {
		return User{}, fmt.Errorf("error getting user by email: %w", err)
	}
	return user, nil
}

This generated code lets you interact with your database using type-safe Go functions.

Now that you have your queries compiled into Go code, you can use them in your own projects.

Here’s an example of how you can use the generated code to retrieve a user from the database:

package main

import (
	"context"
	"database/sql"
	"fmt"

	_ "github.com/jackc/pgx/v4/stdlib"
	"github.com/myproject/db"
)

func main() {
	// Connect to the database
	connStr := "postgres://user:password@localhost:5432/mydatabase?sslmode=disable"
	db, err := sql.Open("pgx", connStr)
	if err != nil {
		fmt.Println("Error connecting to the database:", err)
		return
	}
	defer db.Close()

	// Create a new DB object
	dbConn := db.NewDB(db)

	// Retrieve a user from the database
	user, err := dbConn.GetUserByEmail(context.Background(), "example@example.com")
	if err != nil {
		fmt.Println("Error retrieving user:", err)
		return
	}

	fmt.Println("User:", user)
}

This code connects to a PostgreSQL database and retrieves a user from the users table using the GetUserByEmail function generated by sqlc.

sqlc is a powerful tool that lets you write SQL queries in your preferred programming language. With sqlc, you can write type-safe SQL queries in Go and easily integrate them into your projects.

In this article, I’ve shown you how to get started with sqlc by creating a schema, writing queries, compiling them into Go code, and using that code in your own projects.

I hope this article has been helpful in getting you started with sqlc. Happy coding!