sqlc

https://docs.sqlc.dev/
Admin
sqlc generates fully type-safe idiomatic Go code from SQL queries, eliminating the need for...
Tokens:64,291
Snippets:559
Trust Score:8.6
Update:1 day ago
Show doc for...
Tokens:
Raw
### Usage example with database/sql driver (github/lib/pq)

Source: https://docs.sqlc.dev/en/latest/howto/transactions.html

Shows how to use WithTx with a *sql.DB from the database/sql package. It begins a transaction, defers rollback, gets a record, updates its counter, and commits. Uses the github/lib/pq driver.

```go
// Using `github/lib/pq` as the driver.
func bumpCounter(ctx context.Context, db *sql.DB, queries *tutorial.Queries, id int32) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	qtx := queries.WithTx(tx)
	r, err := qtx.GetRecord(ctx, id)
	if err != nil {
		return err
	}
	if err := qtx.UpdateRecord(ctx, tutorial.UpdateRecordParams{
		ID:      r.ID,
		Counter: r.Counter + 1,
	}); err != nil {
		return err
	}
	return tx.Commit()
}
```

--------------------------------

### Run examples tests with examples tag

Source: https://docs.sqlc.dev/en/latest/guides/development.html

Runs tests in the examples folder, which is a separate Go module. Must be run from inside the examples directory with the examples build tag.

```bash
cd examples && go test --tags=examples ./...
```

--------------------------------

### Install sqlc on Ubuntu with Snap

Source: https://docs.sqlc.dev/en/latest/overview/install.html

Use Snap to install sqlc on Ubuntu.

```bash
sudo snap install sqlc
```

--------------------------------

### Install sqlc with go install

Source: https://docs.sqlc.dev/en/latest/overview/install.html

Install sqlc using Go's install command. Requires Go 1.21+.

```bash
go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest
```

--------------------------------

### Usage example with pgx driver (github.com/jackc/pgx/v5)

Source: https://docs.sqlc.dev/en/latest/howto/transactions.html

Shows how to use WithTx with a *pgx.Conn from the pgx driver. It begins a transaction with context, defers rollback, gets a record, updates its counter, and commits with context. Uses the github.com/jackc/pgx/v5 driver.

```go
// Using `github.com/jackc/pgx/v5` as the driver.
func bumpCounter(ctx context.Context, db *pgx.Conn, queries *tutorial.Queries, id int32) error {
	tx, err := db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)
	qtx := queries.WithTx(tx)
	r, err := qtx.GetRecord(ctx, id)
	if err != nil {
		return err
	}
	if err := qtx.UpdateRecord(ctx, tutorial.UpdateRecordParams{
		ID:      r.ID,
		Counter: r.Counter + 1,
	}); err != nil {
		return err
	}
	return tx.Commit(ctx)
}
```

--------------------------------

### Example queries for sqlc with RETURNING

Source: https://docs.sqlc.dev/en/latest/howto/insert.html

SQL schema and queries for the RETURNING example. It creates an authors table with id, name, and bio columns, and includes two queries: CreateAuthor and CreateAuthorAndReturnId.

```sql
-- Example queries for sqlc
CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);
```

--------------------------------

### Install sqlc on macOS with Homebrew

Source: https://docs.sqlc.dev/en/latest/overview/install.html

Use Homebrew to install sqlc on macOS.

```bash
brew install sqlc
```

--------------------------------

### Version 2 Configuration Example

Source: https://docs.sqlc.dev/en/latest/reference/config.html

Example of a version 2 sqlc configuration file with multiple query sets for PostgreSQL and MySQL, including managed database and rules.

```yaml
version: "2"
cloud:
  project: "<PROJECT_ID>"
sql:
- schema: "postgresql/schema.sql"
  queries: "postgresql/query.sql"
  engine: "postgresql"
  gen:
    go: 
      package: "authors"
      out: "postgresql"
  database:
    managed: true
  rules:
    - sqlc/db-prepare
- schema: "mysql/schema.sql"
  queries: "mysql/query.sql"
  engine: "mysql"
  gen:
    go:
      package: "authors"
      out: "mysql"

```

--------------------------------

### Example query.sql for sqlc analyze

Source: https://docs.sqlc.dev/en/latest/howto/analyze.html

A sample query file used in the examples. It selects all columns from authors where id matches the parameter.

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

--------------------------------

### database URI with Environment Variable Example

Source: https://docs.sqlc.dev/en/latest/reference/config.html

Example configuration using a database URI with an environment variable reference (${PG_PASSWORD}) for the password.

```yaml
version: '2'
sql:
- schema: schema.sql
  queries: query.sql
  engine: postgresql
  database:
    uri: postgresql://postgres:${PG_PASSWORD}@localhost:5432/authors
  gen:
    go:
      package: authors
      out: postgresql

```

--------------------------------

### codegen Plugin Configuration Example

Source: https://docs.sqlc.dev/en/latest/reference/config.html

Example configuration for using a custom codegen plugin (py) with options like emit_sync_querier and emit_async_querier.

```yaml
version: '2'
plugins:
- name: py
  wasm:
    url: https://github.com/sqlc-dev/sqlc-gen-python/releases/download/v0.16.0-alpha/sqlc-gen-python.wasm
    sha256: 428476c7408fd4c032da4ec74e8a7344f4fa75e0f98a5a3302f238283b9b95f2
sql:
- schema: "schema.sql"
  queries: "query.sql"
  engine: postgresql
  codegen:
  - out: src/authors
    plugin: py
    options:
      package: authors
      emit_sync_querier: true
      emit_async_querier: true
      query_parameter_limit: 5

```

--------------------------------

### Example query file after sqlc fmt

Source: https://docs.sqlc.dev/en/latest/howto/fmt.html

The formatted output of the example query file. GetAuthor keeps its line breaks, while SearchAuthors breaks open around the comment and preserves the WHERE chain on one line.

```sql
-- name: GetAuthor :one
SELECT id, name, bio
FROM authors
WHERE id = ?
LIMIT 1;

-- name: SearchAuthors :many
SELECT
  id, -- the primary key
  name,
  bio,
  created_at
FROM authors
WHERE name LIKE ? AND bio IS NOT NULL AND id > ? AND created_at > ? AND name <> ?
ORDER BY name;
```

--------------------------------

### Example schema.sql for sqlc analyze

Source: https://docs.sqlc.dev/en/latest/howto/analyze.html

A sample schema file used in the examples. It defines an authors table with id, name, and bio columns.

```sql
CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);
```

--------------------------------

### Example lint rules using EXPLAIN output

Source: https://docs.sqlc.dev/en/latest/reference/changelog/v1.20.0.html

Shows example lint rules for PostgreSQL and MySQL that use EXPLAIN output to detect costly queries, sequential scans, and missing primary key usage.

```yaml
...
rules:
- name: postgresql-query-too-costly
  message: "Query cost estimate is too high"
  rule: "postgresql.explain.plan.total_cost > 1.0"
- name: postgresql-no-seq-scan
  message: "Query plan results in a sequential scan"
  rule: "postgresql.explain.plan.node_type == 'Seq Scan'"
- name: mysql-query-too-costly
  message: "Query cost estimate is too high"
  rule: "has(mysql.explain.query_block.cost_info) && double(mysql.explain.query_block.cost_info.query_cost) > 2.0"
- name: mysql-must-use-primary-key
  message: "Query plan doesn't use primary key"
  rule: "has(mysql.explain.query_block.table.key) && mysql.explain.query_block.table.key != 'PRIMARY'"
```

--------------------------------

### SQLCEXPERIMENT examples: enable and disable experiments

Source: https://docs.sqlc.dev/en/latest/reference/environment-variables.html

Shows how to set the SQLCEXPERIMENT environment variable to enable or disable experiments. Use 'no' prefix to explicitly disable an experiment. The examples demonstrate enabling foo and bar, disabling foo, and enabling foo while disabling bar.

```bash
SQLCEXPERIMENT=foo,bar      # enable foo and bar experiments
SQLCEXPERIMENT=nofoo        # explicitly disable foo experiment
SQLCEXPERIMENT=foo,nobar    # enable foo, disable bar
```

--------------------------------

### Install sqlc v1.20.0 with new import path

Source: https://docs.sqlc.dev/en/latest/reference/changelog/v1.20.0.html

Shows the correct go install command for v1.20.0 using the new sqlc-dev/sqlc repository. The old import path is incorrect and will not work for this version.

```bash
# INCORRECT: old import path
go install github.com/kyleconroy/sqlc/cmd/sqlc@v1.20.0

# CORRECT: new import path
go install github.com/sqlc-dev/sqlc/cmd/sqlc@v1.20.0
```

--------------------------------

### Example query file before sqlc fmt

Source: https://docs.sqlc.dev/en/latest/howto/fmt.html

A sample query file with two queries: GetAuthor and SearchAuthors. It contains inconsistent spacing and a comment. This is the input to sqlc fmt.

```sql
-- name: GetAuthor :one
select  id,name , bio
from   authors
where id =  ? limit 1;

-- name: SearchAuthors :many
SELECT id, -- the primary key
       name, bio, created_at FROM authors WHERE name LIKE ? AND bio IS NOT NULL AND id > ? AND created_at > ? AND name <> ? ORDER BY name;
```

--------------------------------

### Build sqlc binary with alias sqlc-dev

Source: https://docs.sqlc.dev/en/latest/guides/development.html

Builds the sqlc binary and installs it as sqlc-dev in ~/go/bin. Use this for local development to avoid conflicts with a globally installed sqlc.

```bash
go build -o ~/go/bin/sqlc-dev ./cmd/sqlc
```

--------------------------------

### Start test databases with Docker Compose

Source: https://docs.sqlc.dev/en/latest/guides/development.html

Starts the required database instances for tests using Docker Compose. These databases must be running locally before running the tests.

```bash
docker compose up -d
```

--------------------------------

### sqlc version 1 configuration example

Source: https://docs.sqlc.dev/en/latest/reference/config.html

Example YAML configuration for sqlc version 1, defining a package named 'db' with PostgreSQL engine and various code generation options.

```yaml
version: "1"
packages:
  - name: "db"
    path: "internal/db"
    queries: "./sql/query/"
    schema: "./sql/schema/"
    engine: "postgresql"
    emit_db_tags: false
    emit_prepared_queries: true
    emit_interface: false
    emit_exact_table_names: false
    emit_empty_slices: false
    emit_exported_queries: false
    emit_json_tags: true
    emit_result_struct_pointers: false
    emit_params_struct_pointers: false
    emit_methods_with_db_argument: false
    emit_pointers_for_null_types: false
    emit_enum_valid_method: false
    emit_all_enum_values: false
    build_tags: "some_tag"
    json_tags_case_style: "camel"
    omit_unused_structs: false
    output_batch_file_name: "batch.go"
    output_db_file_name: "db.go"
    output_models_file_name: "models.go"
    output_querier_file_name: "querier.go"
    output_copyfrom_file_name: "copyfrom.go"
    query_parameter_limit: 1

```

--------------------------------

### Build sqlc-gen-json binary

Source: https://docs.sqlc.dev/en/latest/guides/development.html

Builds the sqlc-gen-json binary and installs it to ~/go/bin. Required to avoid test failures.

```bash
go build -o ~/go/bin/sqlc-gen-json ./cmd/sqlc-gen-json
```

--------------------------------

### golang-migrate up migration for post table

Source: https://docs.sqlc.dev/en/latest/howto/ddl.html

Example of a golang-migrate up migration file (20060102.up.sql) that creates a post table.

```sql
CREATE TABLE post (
    id    int NOT NULL,
    title text,
    body  text,
    PRIMARY KEY(id)
);
```

--------------------------------

### Example trace output showing execution times

Source: https://docs.sqlc.dev/en/latest/reference/environment-variables.html

Sample log from the trace output showing execution time for each package, including regions for generate, package, parse, codegen, and writefiles.

```text
0.000043897 	 .         	1 	task sqlc (id 1, parent 0) created
0.000144923 	 .   101026 	1 	region generate started (duration: 47.619781ms)
0.001048975 	 .   904052 	1 	region package started (duration: 14.588456ms)
0.001054616 	 .     5641 	1 	name=authors dir=/Users/kyle/projects/sqlc/examples/python language=python
0.001071257 	 .    16641 	1 	region parse started (duration: 7.966549ms)
0.009043960 	 .  7972703 	1 	region codegen started (duration: 6.587086ms)
0.009171704 	 .   127744 	1 	new goroutine 35: text/template/parse.lex·dwrap·1
0.010361654 	 .  1189950 	1 	new goroutine 36: text/template/parse.lex·dwrap·1
0.015641815 	 .  5280161 	1 	region package started (duration: 10.904938ms)
0.015644943 	 .     3128 	1 	name=booktest dir=/Users/kyle/projects/sqlc/examples/python language=python
0.015647431 	 .     2488 	1 	region parse started (duration: 4.207749ms)
0.019860308 	 .  4212877 	1 	region codegen started (duration: 6.681624ms)
0.020028488 	 .   168180 	1 	new goroutine 37: text/template/parse.lex·dwrap·1
0.021020310 	 .   991822 	1 	new goroutine 8: text/template/parse.lex·dwrap·1
0.026551163 	 .  5530853 	1 	region package started (duration: 9.217294ms)
0.026554368 	 .     3205 	1 	name=jets dir=/Users/kyle/projects/sqlc/examples/python language=python
0.026556804 	 .     2436 	1 	region parse started (duration: 3.491005ms)
0.030051911 	 .  3495107 	1 	region codegen started (duration: 5.711931ms)
0.030213937 	 .   162026 	1 	new goroutine 20: text/template/parse.lex·dwrap·1
0.031099938 	 .   886001 	1 	new goroutine 38: text/template/parse.lex·dwrap·1
0.035772637 	 .  4672699 	1 	region package started (duration: 10.267039ms)
0.035775688 	 .     3051 	1 	name=ondeck dir=/Users/kyle/projects/sqlc/examples/python language=python
0.035778150 	 .     2462 	1 	region parse started (duration: 4.094518ms)
0.039877181 	 .  4099031 	1 	region codegen started (duration: 6.156341ms)
0.040010771 	 .   133590 	1 	new goroutine 39: text/template/parse.lex·dwrap·1
0.040894567 	 .   883796 	1 	new goroutine 40: text/template/parse.lex·dwrap·1
0.046042779 	 .  5148212 	1 	region writefiles started (duration: 1.718259ms)
0.047767781 	 .  1725002 	1 	task end

```

--------------------------------

### Create users and user_actions tables

Source: https://docs.sqlc.dev/en/latest/howto/verify.html

Example production schema with two tables: users and user_actions. Used to illustrate how a schema change can break existing queries.

```sql
CREATE TABLE users (
  id UUID PRIMARY KEY
);

CREATE TABLE user_actions (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  action TEXT,
  created_at TIMESTAMP
);
```

--------------------------------

### sql-migrate migration with up and down

Source: https://docs.sqlc.dev/en/latest/howto/ddl.html

Example of a sql-migrate migration file with -- +migrate Up and -- +migrate Down sections. sqlc ignores the down migration when parsing.

```sql
-- +migrate Up
-- SQL in section 'Up' is executed when this migration is applied
CREATE TABLE people (id int);


-- +migrate Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE people;
```

--------------------------------

### dbmate migration with up and down

Source: https://docs.sqlc.dev/en/latest/howto/ddl.html

Example of a dbmate migration file with --migrate:up and --migrate:down sections. sqlc ignores the down migration when parsing.

```sql
-- migrate:up
CREATE TABLE foo (bar INT NOT NULL);

-- migrate:down
DROP TABLE foo;
```

--------------------------------

### sqlc configuration with go_type map

Source: https://docs.sqlc.dev/en/latest/howto/overrides.html

Example sqlc configuration using the `go_type` map to specify a custom type with import, package, type, and pointer options.

```yaml
version: "2"
sql:
- schema: "postgresql/schema.sql"
  queries: "postgresql/query.sql"
  engine: "postgresql"
  gen:
    go:
      package: "authors"
      out: "db"
      sql_package: "pgx/v5"
      overrides:
        - db_type: "uuid"
          go_type:
            import: "a/b/v2"
            package: "b"
            type: "MyType"
            pointer: true
```

--------------------------------

### golang-migrate filename ordering fix

Source: https://docs.sqlc.dev/en/latest/howto/ddl.html

Example showing that prepending zeroes to migration filenames ensures lexicographic order matches numeric order, so 010_bar.up.sql is parsed after 009_foo.up.sql.

```text
001_initial.up.sql
...
009_foo.up.sql
010_bar.up.sql
```

--------------------------------

### sqlc vet database connectivity configuration

Source: https://docs.sqlc.dev/en/latest/reference/changelog/v1.19.0.html

Example sqlc configuration enabling the sqlc/db-prepare rule with a database URI. This rule attempts to prepare each query against the connected database and reports failures.

```yaml
version: 2
sql:
  - schema: "query.sql"
    queries: "query.sql"
    engine: "postgresql"
    gen:
      go:
        package: "authors"
        out: "db"
    database:
      uri: "postgresql://postgres:password@localhost:5432/postgres"
    rules:
      - sqlc/db-prepare
```

--------------------------------

### goose filename ordering fix

Source: https://docs.sqlc.dev/en/latest/howto/ddl.html

Example showing that prepending zeroes to migration filenames ensures lexicographic order matches numeric order, so 010_bar.sql is parsed after 009_foo.sql.

```text
001_initial.sql
...
009_foo.sql
010_bar.sql
```

--------------------------------

### Example dumpast output showing SQL AST

Source: https://docs.sqlc.dev/en/latest/reference/environment-variables.html

Sample output from the dumpast command, showing the generic SQL AST structure for a catalog with a table named 'authors'.

```text
([]interface {}) (len=1 cap=1) {
 (*catalog.Catalog)(0xc0004f48c0)({
  Comment: (string) "",
  DefaultSchema: (string) (len=6) "public",
  Name: (string) "",
  Schemas: ([]*catalog.Schema) (len=3 cap=4) {
   (*catalog.Schema)(0xc0004f4930)({
    Name: (string) (len=6) "public",
    Tables: ([]*catalog.Table) (len=1 cap=1) {
     (*catalog.Table)(0xc00052ff20)({
      Rel: (*ast.TableName)(0xc00052fda0)({
       Catalog: (string) "",
       Schema: (string) "",
       Name: (string) (len=7) "authors"
      }),

```

--------------------------------

### sqlc.arg macro expansion example

Source: https://docs.sqlc.dev/en/latest/reference/macros.html

Shows a SQL query using sqlc.arg(name) and its expansion to a placeholder '?'. The macro attaches a name to a parameter and expands to an engine-specific placeholder.

```sql
-- name: GetAuthorByName :one
SELECT *
FROM authors
WHERE lower(name) = sqlc.arg(name);

-- >>> EXPANDS TO >>>

-- name: GetAuthorByName :one
SELECT *
FROM authors
WHERE lower(name) = ?;
```

--------------------------------

### Atlas migration SQL for post table

Source: https://docs.sqlc.dev/en/latest/howto/ddl.html

Example of an atlas migration file that creates a 'post' table. sqlc parses this to generate the corresponding Go struct.

```sql
-- Create "post" table
CREATE TABLE "public"."post" ("id" integer NOT NULL, "title" text NULL, "body" text NULL, PRIMARY KEY ("id"));
```

--------------------------------

### Initialize Go module

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html

Run this command in the sqlc-tutorial directory to initialize a new Go module named tutorial.sqlc.dev/app.

```bash
go mod init tutorial.sqlc.dev/app
```

--------------------------------

### Using generated code with pgx connection

Source: https://docs.sqlc.dev/en/latest/guides/using-go-and-pgx.html

Example main program that connects to PostgreSQL using pgx.Connect, creates a db instance, and calls the generated GetAuthor method. Requires DATABASE_URL environment variable.

```go
package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5"

	"example.com/sqlc-tutorial/db"
)

func main() {
	// urlExample := "postgres://username:password@localhost:5432/database_name"
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(context.Background())

	q := db.New(conn)

	author, err := q.GetAuthor(context.Background(), 1)
	if err != nil {
		fmt.Fprintf(os.Stderr, "GetAuthor failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(author.Name)
}

```

--------------------------------

### tutorial.go - using generated code

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-sqlite.html

Create a tutorial.go file with this Go program that uses the generated tutorial package to list authors, create an author, and fetch it back. It uses an in-memory SQLite database and embeds the schema.sql file.

```go
package main

import (
	"context"
	"database/sql"
	_ "embed"
	"log"
	"reflect"

	_ "modernc.org/sqlite"

	"tutorial.sqlc.dev/app/tutorial"
)

//go:embed schema.sql
var ddl string

func run() error {
	ctx := context.Background()

	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		return err
	}

	// create tables
	if _, err := db.ExecContext(ctx, ddl); err != nil {
		return err
	}

	queries := tutorial.New(db)

	// list all authors
	authors, err := queries.ListAuthors(ctx)
	if err != nil {
		return err
	}
	log.Println(authors)

	// create an author
	insertedAuthor, err := queries.CreateAuthor(ctx, tutorial.CreateAuthorParams{
		Name: "Brian Kernighan",
		Bio:  sql.NullString{String: "Co-author of The C Programming Language and The Go Programming Language", Valid: true},
	})
	if err != nil {
		return err
	}
	log.Println(insertedAuthor)

	// get the author we just inserted
	fetchedAuthor, err := queries.GetAuthor(ctx, insertedAuthor.ID)
	if err != nil {
		return err
	}

	// prints true
	log.Println(reflect.DeepEqual(insertedAuthor, fetchedAuthor))
	return nil
}

func main() {
	if err := run(); err != nil {
		log.Fatal(err)
	}
}
```

--------------------------------

### sqlc.slice macro expansion example

Source: https://docs.sqlc.dev/en/latest/reference/macros.html

Shows a SQL query using sqlc.slice("ages") and its expansion with a /*SLICE:ages*/ placeholder. This macro generates a dynamic query at runtime and cannot be used with prepared statements.

```sql
/* name: SelectStudents :many */
SELECT * FROM students 
WHERE age IN (sqlc.slice("ages"))

-- >>> EXPANDS TO >>>

/* name: SelectStudents :many */
SELECT id, name, age FROM authors 
WHERE age IN (/*SLICE:ages*/?)
```

--------------------------------

### Go program using generated tutorial package

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html

Example Go program that connects to PostgreSQL using pgx/v5, uses the generated tutorial package to list authors, create an author, and fetch it back. Requires replacing connection parameters and having the authors table.

```go
package main

import (
	"context"
	"log"
	"reflect"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgtype"

	"tutorial.sqlc.dev/app/tutorial"
)

func run() error {
	ctx := context.Background()

	conn, err := pgx.Connect(ctx, "user=pqgotest dbname=pqgotest sslmode=verify-full")
	if err != nil {
		return err
	}
	defer conn.Close(ctx)

	queries := tutorial.New(conn)

	// list all authors
	authors, err := queries.ListAuthors(ctx)
	if err != nil {
		return err
	}
	log.Println(authors)

	// create an author
	insertedAuthor, err := queries.CreateAuthor(ctx, tutorial.CreateAuthorParams{
		Name: "Brian Kernighan",
		Bio:  pgtype.Text{String: "Co-author of The C Programming Language and The Go Programming Language", Valid: true},
	})
	if err != nil {
		return err
	}
	log.Println(insertedAuthor)

	// get the author we just inserted
	fetchedAuthor, err := queries.GetAuthor(ctx, insertedAuthor.ID)
	if err != nil {
		return err
	}

	// prints true
	log.Println(reflect.DeepEqual(insertedAuthor, fetchedAuthor))
	return nil
}

func main() {
	if err := run(); err != nil {
		log.Fatal(err)
	}
}
```

--------------------------------

### sqlc push --tag tutorial

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html

Publishes queries to sqlc Cloud with the tag 'tutorial'. The command is run after setting the auth token.

```bash
$ sqlc push --tag tutorial
```

--------------------------------

### Go program using generated tutorial package

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-mysql.html

A complete Go program that uses the generated tutorial package to list authors, create an author, and fetch the inserted author. It requires a MySQL database with the authors table and correct connection parameters.

```go
package main

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

	_ "github.com/go-sql-driver/mysql"

	"tutorial.sqlc.dev/app/tutorial"
)

func run() error {
	ctx := context.Background()

	db, err := sql.Open("mysql", "user:password@/dbname?parseTime=true")
	if err != nil {
		return err
	}

	queries := tutorial.New(db)

	// list all authors
	authors, err := queries.ListAuthors(ctx)
	if err != nil {
		return err
	}
	log.Println(authors)

	// create an author
	result, err := queries.CreateAuthor(ctx, tutorial.CreateAuthorParams{
		Name: "Brian Kernighan",
		Bio:  sql.NullString{String: "Co-author of The C Programming Language and The Go Programming Language", Valid: true},
	})
	if err != nil {
		return err
	}

	insertedAuthorID, err := result.LastInsertId()
	if err != nil {
		return err
	}
	log.Println(insertedAuthorID)

	// get the author we just inserted
	fetchedAuthor, err := queries.GetAuthor(ctx, insertedAuthorID)
	if err != nil {
		return err
	}

	// prints true
	log.Println(reflect.DeepEqual(insertedAuthorID, fetchedAuthor.ID))
	return nil
}

func main() {
	if err := run(); err != nil {
		log.Fatal(err)
	}
}
```

--------------------------------

### :execresult query annotation example

Source: https://docs.sqlc.dev/en/latest/reference/query-annotations.html

Example of a query annotated with `:execresult`. The generated method returns the `sql.Result` from `ExecContext`.

```sql
-- name: DeleteAllAuthors :execresult
DELETE FROM authors;
```

--------------------------------

### :exec query annotation example

Source: https://docs.sqlc.dev/en/latest/reference/query-annotations.html

Example of a query annotated with `:exec`. The generated method returns the error from `ExecContext`.

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

--------------------------------

### sqlc.embed schema example

Source: https://docs.sqlc.dev/en/latest/reference/macros.html

Schema for students and test_scores tables used in the sqlc.embed example. This schema is a prerequisite for the embedding query.

```sql
CREATE TABLE students (
  id   bigserial PRIMARY KEY,
  name text,
  age  integer
);

CREATE TABLE test_scores (
  student_id bigint,
  score integer,
  grade text
);
```

--------------------------------

### :execrows query annotation example

Source: https://docs.sqlc.dev/en/latest/reference/query-annotations.html

Example of a query annotated with `:execrows`. The generated method returns the number of affected rows from the result.

```sql
-- name: DeleteAllAuthors :execrows
DELETE FROM authors;
```

--------------------------------

### Fetch SQLite driver and build

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-sqlite.html

Run these commands to fetch the modernc.org/sqlite driver and build the Go program.

```bash
go get modernc.org/sqlite
go build .
```

--------------------------------

### Fetch MySQL driver and build

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-mysql.html

Commands to fetch the MySQL driver and build the Go program. Run these before compiling the program.

```bash
go get github.com/go-sql-driver/mysql
go build .
```

--------------------------------

### sqlc verify --against tutorial

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html

Verifies that previously published queries (tagged 'tutorial') continue to work against the updated database schema.

```bash
$ sqlc verify --against tutorial
```

--------------------------------

### Fetch pgx/v5 driver and build

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html

Commands to fetch the pgx/v5 PostgreSQL driver and build the Go program. Required before the program will compile.

```bash
go get github.com/jackc/pgx/v5
go build .
```

--------------------------------

### query.sql with five queries

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html

Create a query.sql file with these five queries: GetAuthor, ListAuthors, CreateAuthor, UpdateAuthor, and DeleteAuthor.

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

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

-- 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;
```

--------------------------------

### sqlc vet rules using EXPLAIN output

Source: https://docs.sqlc.dev/en/latest/howto/vet.html

Example sqlc configuration rules that use postgresql.explain and mysql.explain CEL variables to enforce query cost and scan constraints. Requires a configured database connection.

```yaml
...
rules:
- name: postgresql-query-too-costly
  message: "Query cost estimate is too high"
  rule: "postgresql.explain.plan.total_cost > 1.0"
- name: postgresql-no-seq-scan
  message: "Query plan results in a sequential scan"
  rule: "postgresql.explain.plan.node_type == 'Seq Scan'"
- name: mysql-query-too-costly
  message: "Query cost estimate is too high"
  rule: "has(mysql.explain.query_block.cost_info) && double(mysql.explain.query_block.cost_info.query_cost) > 2.0"
- name: mysql-must-use-primary-key
  message: "Query plan doesn't use primary key"
  rule: "has(mysql.explain.query_block.table.key) && mysql.explain.query_block.table.key != 'PRIMARY'"

```

--------------------------------

### query.sql - five queries

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-sqlite.html

Create a query.sql file with these five queries (GetAuthor, ListAuthors, CreateAuthor, UpdateAuthor, DeleteAuthor) that sqlc will use to generate Go code.

```sql
-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = ? LIMIT 1;

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

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

-- name: UpdateAuthor :exec
UPDATE authors
set name = ?,
bio = ?
WHERE id = ?;

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = ?;
```

--------------------------------

### sqlc config rename mapping example

Source: https://docs.sqlc.dev/en/latest/reference/config.html

Example configuration using the rename mapping to override the generated field name for spotify_url to SpotifyURL. The keys are column names and values are the struct field names.

```yaml
version: "1"
packages: [...]
rename:
  spotify_url: "SpotifyURL"

```

--------------------------------

### Column name to struct field name conversion examples

Source: https://docs.sqlc.dev/en/latest/reference/config.html

Shows the default algorithm: split column name on underscores and capitalize the first letter of each part. Examples: account -> Account, spotify_url -> SpotifyUrl, app_id -> AppID.

```text
account     -> Account
spotify_url -> SpotifyUrl
app_id      -> AppID

```

--------------------------------

### golang-migrate down migration for post table

Source: https://docs.sqlc.dev/en/latest/howto/ddl.html

Example of a golang-migrate down migration file (20060102.down.sql) that drops the post table.

```sql
DROP TABLE post;
```

--------------------------------

### Schema for students and test_scores tables

Source: https://docs.sqlc.dev/en/latest/howto/embedding.html

SQL schema defining the students and test_scores tables used in the embedding examples.

```sql
CREATE TABLE students (
  id   bigserial PRIMARY KEY,
  name text NOT NULL,
  age  integer NOT NULL
);

CREATE TABLE test_scores (
  student_id bigint NOT NULL,
  score      integer NOT NULL,
  grade      text NOT NULL
);
```

--------------------------------

### Process plugin configuration example

Source: https://docs.sqlc.dev/en/latest/guides/plugins.html

Defines a process-based plugin named 'jsonb' that runs the command 'sqlc-gen-json'. Process plugins offer minimal security and should only be used if trusted. Options are serialized to JSON and passed to the plugin.

```yaml
version: '2'
plugins:
- name: jsonb
  process:
    cmd: sqlc-gen-json
sql:
- schema: schema.sql
  queries: query.sql
  engine: postgresql
  codegen:
  - out: gen
    plugin: jsonb
    options:
      indent: "  "
      filename: codegen.json
```

--------------------------------

### Create table with JSONB column

Source: https://docs.sqlc.dev/en/latest/reference/datatypes.html

SQL statement to create a table with a jsonb column, used in the JSON override example.

```sql
CREATE TABLE books (
  data jsonb
);

```

--------------------------------

### GitHub Actions workflow for sqlc vet

Source: https://docs.sqlc.dev/en/latest/howto/ci-cd.html

Runs sqlc vet on every push. Starts a PostgreSQL server using the action-setup-postgres action and sets the POSTGRESQL_SERVER_URI environment variable with sslmode=disable. A database connection is required if the configuration references the sqlc/db-prepare lint rule.

```yaml
name: sqlc
on: [push]
jobs:
  vet:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - uses: sqlc-dev/setup-sqlc@v3
      with:
        sqlc-version: '1.31.1'
    # Start a PostgreSQL server
    - uses: sqlc-dev/action-setup-postgres@master
      with:
        postgres-version: "16"
      id: postgres
    - run: sqlc vet
      env:
        POSTGRESQL_SERVER_URI: ${{ steps.postgres.outputs.connection-uri }}?sslmode=disable
```

--------------------------------

### Create authors table schema

Source: https://docs.sqlc.dev/en/latest/howto/select.html

SQL schema for the authors table used in the examples. Defines id, bio, and birth_year columns.

```sql
CREATE TABLE authors (
  id         SERIAL PRIMARY KEY,
  bio        text   NOT NULL,
  birth_year int    NOT NULL
);
```

--------------------------------

### Run sqlc generate with Docker on Unix-like systems

Source: https://docs.sqlc.dev/en/latest/overview/install.html

Run sqlc using Docker on Unix-like systems, mounting the current directory and setting the working directory.

```bash
docker run --rm -v $(pwd):/src -w /src sqlc/sqlc generate
```

--------------------------------

### goose migration with up and down

Source: https://docs.sqlc.dev/en/latest/howto/ddl.html

Example of a goose migration file with -- +goose Up and -- +goose Down sections. sqlc ignores the down migration when parsing.

```sql
-- +goose Up
CREATE TABLE post (
    id    int NOT NULL,
    title text,
    body  text,
    PRIMARY KEY(id)
);

-- +goose Down
DROP TABLE post;
```

--------------------------------

### Rename field mapping in sqlc v2 config

Source: https://docs.sqlc.dev/en/latest/howto/rename.html

Configuration example for renaming a field using the rename mapping in sqlc version 2.

```yaml
version: "2"
sql:
- schema: "postgresql/schema.sql"
  queries: "postgresql/query.sql"
  engine: "postgresql"
  gen:
    go: 
      package: "authors"
      out: "postgresql"
      rename:
        spotify_url: "SpotifyURL"
```

--------------------------------

### query.sql

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-mysql.html

Create this file with four queries: GetAuthor, ListAuthors, CreateAuthor, and DeleteAuthor. These queries are used by sqlc to generate Go code.

```sql
-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = ? LIMIT 1;

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

-- name: CreateAuthor :execresult
INSERT INTO authors (
  name, bio
) VALUES (
  ?, ?
);

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = ?;
```

--------------------------------

### schema.sql - authors table

Source: https://docs.sqlc.dev/en/latest/tutorials/getting-started-sqlite.html

Create a schema.sql file with this SQL to define the authors table used in the tutorial.

```sql
CREATE TABLE authors (
  id   INTEGER PRIMARY KEY,
  name text    NOT NULL,
  bio  text
);
```

--------------------------------

### Default field name generation examples

Source: https://docs.sqlc.dev/en/latest/howto/rename.html

Shows how column names are converted to struct field names: split on underscores and capitalize each part.

```text
account     -> Account
spotify_url -> SpotifyUrl
app_id      -> AppID
```

--------------------------------

### Example dumpcatalog output showing entire catalog

Source: https://docs.sqlc.dev/en/latest/reference/environment-variables.html

Sample output from the dumpcatalog command, showing the entire catalog structure with a table named 'authors'.

```text
([]interface {}) (len=1 cap=1) {
 (*catalog.Catalog)(0xc00050d1f0)({
  Comment: (string) "",
  DefaultSchema: (string) (len=6) "public",
  Name: (string) "",
  Schemas: ([]*catalog.Schema) (len=3 cap=4) {
   (*catalog.Schema)(0xc00050d260)({
    Name: (string) (len=6) "public",
    Tables: ([]*catalog.Table) (len=1 cap=1) {
     (*catalog.Table)(0xc0000c0840)({
      Rel: (*ast.TableName)(0xc0000c06c0)({
       Catalog: (string) "",
       Schema: (string) "",
       Name: (string) (len=7) "authors"
      }),

```

--------------------------------

### sqlc.narg macro expansion example

Source: https://docs.sqlc.dev/en/latest/reference/macros.html

Shows a SQL query using sqlc.narg(name) and its expansion to a placeholder '?'. The macro always marks the parameter as nullable.

```sql
-- name: GetAuthorByName :one
SELECT *
FROM authors
WHERE lower(name) = sqlc.narg(name);

-- >>> EXPANDS TO >>>

-- name: GetAuthorByName :one
SELECT *
FROM authors
WHERE LOWER(name) = ?;
```
Tokens:
Raw