GORM has runtime overhead and magic. Raw SQL has no type safety. Use sqlc to get compile-time verified SQL with generated Go code.
# sqlc.yaml
version: "2"
sql:
- engine: "postgresql"
queries: "query.sql"
schema: "schema.sql"
gen:
go:
package: "db"
out: "internal/db"-- schema.sql
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
bio TEXT
);
CREATE TABLE books (
id BIGSERIAL PRIMARY KEY,
author_id BIGINT REFERENCES authors(id),
title TEXT NOT NULL,
year INT NOT NULL
);-- query.sql
-- name: GetAuthor :one
SELECT * FROM authors WHERE id = $1;
-- name: ListAuthors :many
SELECT * FROM authors ORDER BY name LIMIT $1 OFFSET $2;
-- name: CreateAuthor :one
INSERT INTO authors (name, bio) VALUES ($1, $2)
RETURNING *;
-- name: UpdateAuthor :exec
UPDATE authors SET name = $2, bio = $3 WHERE id = $1;
-- name: DeleteAuthor :exec
DELETE FROM authors WHERE id = $1;
-- name: GetAuthorBooks :many
SELECT b.* FROM books b
JOIN authors a ON b.author_id = a.id
WHERE a.id = $1;package main
import (
"context"
"database/sql"
"yourapp/internal/db"
_ "github.com/lib/pq"
)
func main() {
conn, _ := sql.Open("postgres", "...")
queries := db.New(conn)
ctx := context.Background()
// Create author
author, err := queries.CreateAuthor(ctx, db.CreateAuthorParams{
Name: "George Orwell",
Bio: sql.NullString{String: "English novelist", Valid: true},
})
// List with pagination
authors, err := queries.ListAuthors(ctx, db.ListAuthorsParams{
Limit: 10,
Offset: 0,
})
// Get single author
author, err := queries.GetAuthor(ctx, 1)
}func CreateAuthorWithBook(ctx context.Context, db *sql.DB) error {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
queries := db.New(tx) // Pass tx instead of db
author, err := queries.CreateAuthor(ctx, db.CreateAuthorParams{
Name: "New Author",
})
if err != nil {
return err
}
_, err = queries.CreateBook(ctx, db.CreateBookParams{
AuthorID: author.ID,
Title: "New Book",
Year: 2024,
})
if err != nil {
return err
}
return tx.Commit()
}