DEV Community

Huseyn
Huseyn

Posted on

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

📁 Recommended Project Structure 

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

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

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

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

🛠️ Generating Go Code 

sqlc generate
Enter fullscreen mode Exit fullscreen mode

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", "esha@email.com") fmt.Println(user)
Enter fullscreen mode Exit fullscreen mode

🧹 Handling Errors

If you see:

error: Dirty database version -1. Fix and force version.
Enter fullscreen mode Exit fullscreen mode

Use:

migrate ... force 0
Enter fullscreen mode Exit fullscreen mode

Then:

migrate ... up
Enter fullscreen mode Exit fullscreen mode

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

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

Generates:

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

✅ :many 

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

Generates:

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

✅ :exec 

-- name: DeleteUser :exec DELETE FROM users WHERE id = $1;
Enter fullscreen mode Exit fullscreen mode

Generates:

func (q *Queries) DeleteUser(ctx context.Context, id int32) error
Enter fullscreen mode Exit fullscreen mode

✅ :execrows 

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

Generates:

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

✅ :copyfrom 

-- name: CopyUsers :copyfrom COPY users (username, email) FROM STDIN;
Enter fullscreen mode Exit fullscreen mode

Generates:

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

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

Top comments (0)