DEV Community

Indal Kumar
Indal Kumar

Posted on

1

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

If you're working with databases in Go, you've probably used either raw SQL queries or an ORM. While both approaches have their merits, there's a third option that combines the best of both worlds: sqlc. In this guide, I'll show you how to use sqlc to generate type-safe Go code from your SQL queries.

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
Enter fullscreen mode Exit fullscreen mode

Install sqlc:

go install github.com/kyleconroy/sqlc/cmd/sqlc@latest
Enter fullscreen mode Exit fullscreen mode

Project Structure

Create the following directory structure:

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

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()
);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Generate the Code

Run sqlc to generate the Go code:

sqlc generate
Enter fullscreen mode Exit fullscreen mode

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)
    }
}
Enter fullscreen mode Exit fullscreen mode

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

  1. Version Control: Always commit both your SQL files and generated code to version control.

  2. Database Migrations: Use a separate tool like golang-migrate for database migrations.

  3. Query Organization: Group related queries in separate .sql files for better organization.

  4. 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()
}
Enter fullscreen mode Exit fullscreen mode

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

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

Top comments (0)

Imagine monitoring actually built for developers

Billboard image

Join Vercel, CrowdStrike, and thousands of other teams that trust Checkly to streamline monitor creation and configuration with Monitoring as Code.

Start Monitoring

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay