dev-resources.site

for different kinds of informations.

Supercharge Your Go Database Layer with sqlc: Type-Safe SQL Made Easy

Published at

1/9/2025

What is sqlc?

sqlc is a tool that generates type-safe Go code from SQL queries. It analyzes your SQL queries at compile time and generates corresponding Go code, giving you:

Type safety for your database queries
Better performance than ORMs
Full SQL features without compromise
Compile-time query validation

Getting Started

First, let's set up a new Go project with sqlc and PostgreSQL support:

mkdir go-sqlc-demo
cd go-sqlc-demo
go mod init go-sqlc-demo
go get github.com/lib/pq

Install sqlc:

go install github.com/kyleconroy/sqlc/cmd/sqlc@latest

Project Structure

Create the following directory structure:

.
├── db
│   ├── query/
│   │   └── author.sql
│   ├── schema/
│   │   └── schema.sql
│   └── sqlc.yaml
├── go.mod
└── main.go

Setting up the Database Schema

Create db/schema/schema.sql:

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name text NOT NULL,
    bio text,
    created_at timestamp NOT NULL DEFAULT NOW()
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    author_id integer NOT NULL REFERENCES authors(id),
    title text NOT NULL,
    published_year integer NOT NULL,
    created_at timestamp NOT NULL DEFAULT NOW()
);

Writing SQL Queries

Create db/query/author.sql:

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

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

-- name: CreateAuthor :one
INSERT INTO authors (
    name,
    bio
) VALUES (
    $1, $2
) RETURNING *;

-- name: UpdateAuthor :one
UPDATE authors
SET name = $2,
    bio = $3
WHERE id = $1
RETURNING *;

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

-- name: GetAuthorBooks :many
SELECT b.* FROM books b
JOIN authors a ON a.id = b.author_id
WHERE a.id = $1;

Configuring sqlc

Create db/sqlc.yaml:

version: "2"
sql:
  - schema: "schema/*.sql"
    queries: "query/*.sql"
    engine: "postgresql"
    gen:
      go:
        package: "db"
        out: "sqlc"
        emit_json_tags: true
        emit_prepared_queries: true
        emit_interface: true
        emit_exact_table_names: false

Generate the Code

Run sqlc to generate the Go code:

sqlc generate

This will create a new db/sqlc directory with your generated code.

Using the Generated Code

Here's how to use the generated code in your application:

package main

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

    "go-sqlc-demo/db"
    _ "github.com/lib/pq"
)

func main() {
    // Connect to database
    conn, err := sql.Open("postgres", "postgresql://postgres:postgres@localhost:5432/bookstore?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close()

    // Create a Queries instance
    queries := db.New(conn)

    // Create a new author
    author, err := queries.CreateAuthor(context.Background(), db.CreateAuthorParams{
        Name: "George Orwell",
        Bio:  sql.NullString{String: "English novelist and essayist", Valid: true},
    })
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Created author: %+v\n", author)

    // List all authors
    authors, err := queries.ListAuthors(context.Background())
    if err != nil {
        log.Fatal(err)
    }
    for _, a := range authors {
        fmt.Printf("Author: %s\n", a.Name)
    }
}

Benefits of Using sqlc

1. Type Safety

The generated code includes proper types for all your columns and query parameters. This means you'll catch type-related errors at compile time rather than runtime.

2. IDE Support

Because sqlc generates Go code, you get full IDE support including autocomplete and refactoring tools.

3. Performance

sqlc generates code that uses the standard database/sql package, so there's no overhead compared to writing raw SQL.

4. SQL-First

You write regular SQL queries, which means you can use all of PostgreSQL's features without limitation.

Best Practices

Version Control: Always commit both your SQL files and generated code to version control.
Database Migrations: Use a separate tool like golang-migrate for database migrations.
Query Organization: Group related queries in separate .sql files for better organization.
Transactions: Use the generated interfaces with *sql.Tx for transaction support.

Working with Transactions

Here's an example of using transactions with sqlc:

func transferBooks(ctx context.Context, db *sql.DB, fromAuthorID, toAuthorID int32) error {
    queries := db.New(db)

    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    qtx := queries.WithTx(tx)

    // Update all books from one author to another
    err = qtx.UpdateBookAuthor(ctx, db.UpdateBookAuthorParams{
        OldAuthorID: fromAuthorID,
        NewAuthorID: toAuthorID,
    })
    if err != nil {
        return err
    }

    return tx.Commit()
}

Conclusion

sqlc provides a fantastic middle ground between raw SQL and ORMs. You get the full power of SQL with the safety and convenience of generated Go code. It's particularly well-suited for applications that:

Need type safety and compile-time query validation
Use complex SQL queries
Require high performance
Want to leverage PostgreSQL-specific features

The next time you start a new Go project that involves a database, consider using sqlc. It might just be the perfect balance of safety, performance, and developer experience that you're looking for.

Resources

Remember to star the sqlc repository if you find it useful! Happy coding! 🚀

sql Article's

30 articles in total