Complete Guide: Using `sqlc` with Your Go Project

🚀 What is sqlc?

sqlc is a code generation tool that converts your SQL queries into type-safe Go functions. It eliminates manual boilerplate code and avoids ORMs.

---

✅ Benefits

• Type-safe queries
• No ORM overhead
• Native SQL with full control
• Auto-generated Go models & methods

---

📦 Installation

CLI

brew install sqlc  # macOS
# or
go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

---

📁 Recommended Project Structure

myapp/
├── db/
│   ├── migrations/
│   ├── queries/
│   │   ├── users.sql
│   └── schema.sql
├── sqlc.yaml
├── go.mod
└── main.go

---

⚙️ sqlc.yaml Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: "db/queries/"
    schema: "db/migrations/"
    gen:
      go:
        package: "db"
        out: "db/sqlc"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_interface: true

---

✍️ Writing SQL Queries

db/queries/users.sql

-- name: CreateUser :one
INSERT INTO users (username, email)
VALUES ($1, $2)
RETURNING id, username, email;

-- name: GetUser :one
SELECT id, username, email FROM users WHERE id = $1;

-- name: ListUsers :many
SELECT id, username, email FROM users ORDER BY id DESC;

---

🔖 Query Types in sqlc

Tag	Purpose	Go Return Type
:one	Returns a single row. Fails if no row or more than one is returned. Use it for SELECT with unique constraint or primary key filters.	(Model, error)
:many	Returns multiple rows as a slice. Use for general SELECT queries that return 0 to many rows.	([]Model, error)
:exec	Executes query with no result rows. Use for INSERT, UPDATE, or DELETE that don't return rows.	(error)
:execrows	Executes and also returns the number of rows affected. Useful when you care how many rows were impacted by UPDATE or DELETE.	(int64, error)
:copyfrom	Generates a method using PostgreSQL's COPY FROM. Use for fast bulk insert operations.	(int64, error)

---

📄 Example Migration (db/migrations/init.up.sql)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

---

🛠️ Generating Go Code

sqlc generate

Creates Go code in db/sqlc with models and query methods.

---

🧑‍💻 Using in Go Code

import (
  "context"
  "database/sql"
  "fmt"
  _ "github.com/lib/pq"
  "myapp/db/sqlc"
)

dbConn, _ := sql.Open("postgres", "postgresql://root:root@localhost:5432/simple_bank?sslmode=disable")
q := sqlc.New(dbConn)

user, _ := q.CreateUser(context.Background(), "esha", "[e-mail]")
fmt.Println(user)

---

🧹 Handling Errors

If you see:

error: Dirty database version -1. Fix and force version.

Use:

migrate ... force 0

Then:

migrate ... up

---

✅ Tips

• Use pgx/v5 for performance
• Never edit applied migrations
• Use .PHONY Makefile targets to run sqlc generate, migrate, etc.

---

More
These annotations like :one, :many, :exec, etc., are special sqlc query tags that tell sqlc:

• What kind of Go function to generate
• What kind of return value to expect

They appear in SQL comments just before each query:

-- name: MyFunctionName :<type>
<SQL statement>

Let’s break them down:

---

🔖 sqlc Query Types Explained

Tag	Purpose	Return Type (Go)	Use When...
:one	Return exactly one row	(Type, error)	SELECT that should return a single record
:many	Return multiple rows	([]Type, error)	SELECT with multiple rows expected
:exec	Execute query, no result rows	(error)	INSERT/UPDATE/DELETE without returning
:execrows	Like :exec but also returns rows affected	(int64, error)	UPDATE/DELETE where you want affected row count
:copyfrom	Use COPY FROM PostgreSQL bulk import	(int64, error)	For high-speed bulk insert

---

🧠 Examples

---

✅ :one

-- name: GetUserByID :one
SELECT id, username, email FROM users WHERE id = $1;

Generates:

func (q *Queries) GetUserByID(ctx context.Context, id int32) (User, error)

---

✅ :many

-- name: ListUsers :many
SELECT id, username, email FROM users ORDER BY id DESC LIMIT 10;

Generates:

func (q *Queries) ListUsers(ctx context.Context) ([]User, error)

---

✅ :exec

-- name: DeleteUser :exec
DELETE FROM users WHERE id = $1;

Generates:

func (q *Queries) DeleteUser(ctx context.Context, id int32) error

---

✅ :execrows

-- name: UpdateEmail :execrows
UPDATE users SET email = $2 WHERE id = $1;

Generates:

func (q *Queries) UpdateEmail(ctx context.Context, id int32, email string) (int64, error)

---

✅ :copyfrom

-- name: CopyUsers :copyfrom
COPY users (username, email) FROM STDIN;

Generates:

func (q *Queries) CopyUsers(ctx context.Context, r io.Reader) (int64, error)

Used for efficient bulk data import with a CSV reader or similar.

---

🧠 Summary Cheat Sheet

You want to...	Use this
SELECT 1 row	:one
SELECT many rows	:many
INSERT/UPDATE/DELETE (no rows)	:exec
UPDATE and get row count	:execrows
COPY FROM for bulk insert	:copyfrom

---