### Run example tests

Source: https://docs.sqlc.dev/en/latest/_sources/guides/development.md.txt

Executes tests located in the examples folder using the examples build tag.

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

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

### Install sqlc with Snap on Ubuntu

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

Use Snap to install the sqlc CLI on Ubuntu.

```bash
sudo snap install sqlc

```

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

### Run Example Tests

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

Run tests specifically located in the examples folder using the `examples` tag. These tests often require database connections.

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

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

### Install sqlc with Go

Source: https://docs.sqlc.dev/en/latest/_sources/overview/install.md.txt

Install the latest version of sqlc using `go install`. Requires Go 1.21+.

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

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

### sql-migrate Up Migration Example

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

Example of a sql-migrate 'Up' migration file for the 'people' table.

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



```

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

### Install sqlc with go install

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

Install the latest version of sqlc using Go's install command. Requires Go 1.21+.

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

```

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

### golang-migrate Up Migration Example

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

Example of a golang-migrate 'up' migration file for the 'post' table. Ensure filenames are zero-padded for correct lexicographical ordering if numeric.

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


```

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

### SQLC Package Configuration Example

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

This example demonstrates a basic configuration for the `packages` setting in sqlc, specifying output paths, SQL file locations, and database engine.

```yaml
version: "1"
packages:
  - name: "my_package"
    path: "./internal/my_package"
    schema: "./schema/schema.sql"
    queries: "./query/query.sql"
    engine: "postgresql"
    sql_package: "pgx/v5"

```

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

### Install dependencies and build

Source: https://docs.sqlc.dev/en/latest/_sources/tutorials/getting-started-postgresql.md.txt

Fetch the pgx driver and build the Go application.

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

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

### golang-migrate Down Migration Example

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

Example of a golang-migrate 'down' migration file for the 'post' table.

```sql
DROP TABLE post;

```

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

### Install MySQL driver and build

Source: https://docs.sqlc.dev/en/latest/_sources/tutorials/getting-started-mysql.md.txt

Fetches the required MySQL driver and builds the Go application.

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

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

### Install PostgreSQL Driver for Go

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

These commands fetch the necessary pgx/v5 PostgreSQL driver for Go and build your project. Ensure you have Go installed and configured.

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

```

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

### Start database containers

Source: https://docs.sqlc.dev/en/latest/_sources/guides/development.md.txt

Launches required database instances using Docker Compose.

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

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

### Install sqlc with Homebrew on macOS

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

Use Homebrew to install the sqlc CLI on macOS.

```bash
brew install sqlc

```

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

### Define schema for embedding

Source: https://docs.sqlc.dev/en/latest/_sources/reference/macros.md.txt

Example schema setup for demonstrating struct embedding.

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

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

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

### Install sqlc with Homebrew on macOS

Source: https://docs.sqlc.dev/en/latest/_sources/overview/install.md.txt

Use Homebrew to install the sqlc CLI on macOS. Ensure Homebrew is installed and up-to-date.

```bash
brew install sqlc
```

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

### Install sqlc with Snap on Ubuntu

Source: https://docs.sqlc.dev/en/latest/_sources/overview/install.md.txt

Install the sqlc CLI on Ubuntu using the Snap package manager. This method is suitable for Debian-based systems.

```bash
sudo snap install sqlc
```

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

### Goose Up Migration Example

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

Example of a Goose 'Up' migration file for the 'post' table. Ensure filenames are zero-padded for correct lexicographical ordering if numeric.

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


```

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

### sql-migrate Down Migration Example

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

Example of a sql-migrate 'Down' migration file for the 'people' table.

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

```

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

### Connect to PostgreSQL using pgx

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

Example Go code demonstrating how to connect to a PostgreSQL database using `pgx.Connect` and execute a query.

```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)
}
```

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

### Atlas SQL Migration Example

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

An example of a SQL migration file for the 'post' table using the Atlas migration format.

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

```

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

### Install sqlc with New Import Path

Source: https://docs.sqlc.dev/en/latest/_sources/reference/changelog.md.txt

Use the new import path to install sqlc v1.20.0 and future versions using the go tool.

```sh
go install github.com/sqlc-dev/sqlc/cmd/sqlc@v1.20.0
```

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

### Original Package Configuration (JSON)

Source: https://docs.sqlc.dev/en/latest/_sources/guides/migrating-to-sqlc-gen-kotlin.md.txt

Example of the previous package configuration in sqlc.json before migration.

```json
  "sql": [
    {
      "schema": "schema.sql",
      "queries": "query.sql",
      "engine": "postgresql",
      "gen": {
        "kotlin": {
          "out": "src/main/kotlin/com/example/foo",
          "package": "com.example.foo"
        }
      }
    }
  ]
```

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

### Example SQLC Trace Log

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

This is an example log from the Go trace tool, showing execution times for different SQLC tasks and regions.

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

```

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

### Original Package Configuration (YAML)

Source: https://docs.sqlc.dev/en/latest/_sources/guides/migrating-to-sqlc-gen-kotlin.md.txt

Example of the previous package configuration in sqlc.yaml before migration.

```yaml
  sql:
  - schema: "schema.sql"
    queries: "query.sql"
    engine: "postgresql"
    gen:
      kotlin:
        out: "src/main/kotlin/com/example/foo"
        package: "com.example.foo"
```

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

### Implement prepared queries in Go

Source: https://docs.sqlc.dev/en/latest/_sources/howto/prepared_query.md.txt

Example implementation of prepared queries using the sqlc-generated structure and database/sql.

```go
package db

import (
	"context"
	"database/sql"
	"fmt"
)

type Record struct {
	ID int32
}

type DBTX interface {
	PrepareContext(context.Context, string) (*sql.Stmt, error)
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

func Prepare(ctx context.Context, db DBTX) (*Queries, error) {
	q := Queries{db: db}
	var err error
	if q.getRecordStmt, err = db.PrepareContext(ctx, getRecord); err != nil {
		return nil, fmt.Errorf("error preparing query GetRecord: %w", err)
	}
	return &q, nil
}

func (q *Queries) queryRow(ctx context.Context, stmt *sql.Stmt, query string, args ...interface{}) *sql.Row {
	switch {
	case stmt != nil && q.tx != nil:
		return q.tx.StmtContext(ctx, stmt).QueryRowContext(ctx, args...)
	case stmt != nil:
		return stmt.QueryRowContext(ctx, args...)
	default:
		return q.db.QueryRowContext(ctx, query, args...)
	}
}

type Queries struct {
	db            DBTX
	tx            *sql.Tx
	getRecordStmt *sql.Stmt
}

func (q *Queries) WithTx(tx *sql.Tx) *Queries {
	return &Queries{
		db:            tx,
		tx:            tx,
		getRecordStmt: q.getRecordStmt,
	}
}

const getRecord = `-- name: GetRecord :one
SELECT id FROM records
WHERE id = $1
`

func (q *Queries) GetRecord(ctx context.Context, id int32) (int32, error) {
	row := q.queryRow(ctx, q.getRecordStmt, getRecord, id)
	err := row.Scan(&id)
	return id, err
}
```

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

### Goose Down Migration Example

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

Example of a Goose 'Down' migration file for the 'post' table.

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

```

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

### Build sqlc Binary

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

Build the sqlc binary and install it under the `sqlc-dev` alias in your Go bin directory. This is for local development purposes.

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

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

### dbmate SQL Migration Example

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

Example of a dbmate migration file, including 'up' and 'down' directives. sqlc only parses the 'up' migrations.

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

-- migrate:down
DROP TABLE foo;

```

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

### sqlc verify Output Example

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

Example output from `sqlc verify` indicating a failure due to an ambiguous column reference.

```bash
$ sqlc verify
FAIL: app query.sql

=== Failed
=== FAIL: app query.sql GetUserActions
    ERROR: column reference "created_at" is ambiguous (SQLSTATE 42702)

```

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

### SQL AST Output Example

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

This is an example of the output generated when SQLCDEBUG is set to dumpast=1, showing the parsed SQL Abstract Syntax Tree.

```go
([]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"
      }),


```

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

### Database Catalog Output Example

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

This is an example of the output generated when SQLCDEBUG is set to dumpcatalog=1, displaying the database's catalog information.

```go
([]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"
      }),


```

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

### Use generated code in Go

Source: https://docs.sqlc.dev/en/latest/_sources/tutorials/getting-started-mysql.md.txt

Example implementation showing how to use the generated tutorial package to interact with a MySQL database.

```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)
	}
}
```

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

### Original package configuration (YAML)

Source: https://docs.sqlc.dev/en/latest/guides/migrating-to-sqlc-gen-kotlin.html

Example of the previous package configuration in sqlc.yaml before migrating to the codegen array.

```yaml
sql:
- schema: "schema.sql"
  queries: "query.sql"
  engine: "postgresql"
  gen:
    kotlin:
      out: "src/main/kotlin/com/example/foo"
      package: "com.example.foo"

```

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

### Original package configuration (JSON)

Source: https://docs.sqlc.dev/en/latest/guides/migrating-to-sqlc-gen-kotlin.html

Example of the previous package configuration in sqlc.json before migrating to the codegen array.

```json
{
  "schema": "schema.sql",
  "queries": "query.sql",
  "engine": "postgresql",
  "gen": {
    "kotlin": {
      "out": "src/main/kotlin/com/example/foo",
      "package": "com.example.foo"
    }
  }
}

```

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

### Example sqlc configuration with lint rules

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

This example demonstrates how to define custom lint rules within the `sqlc` configuration file. It includes rules for checking the database engine, disallowing DELETE statements, enforcing a single parameter, and preventing 'exec' commands.

```yaml
version: 2
sql:
  - schema: "query.sql"
    queries: "query.sql"
    engine: "postgresql"
    gen:
      go:
        package: "authors"
        out: "db"
    rules:
      - no-pg
      - no-delete
      - only-one-param
      - no-exec
rules:
  - name: no-pg
    message: "invalid engine: postgresql"
    rule: |
      config.engine == "postgresql"
  - name: no-delete
    message: "don't use delete statements"
    rule: |
      query.sql.contains("DELETE")
  - name: only-one-param
    message: "too many parameters"
    rule: |
      query.params.size() > 1
  - name: no-exec
    message: "don't use exec"
    rule: |
      query.cmd == "exec"
```

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

### Original package config (JSON)

Source: https://docs.sqlc.dev/en/latest/guides/migrating-to-sqlc-gen-python.html

This is an example of the previous configuration format using built-in Python support.

```json
  "sql": [
    {
      "schema": "schema.sql",
      "queries": "query.sql",
      "engine": "postgresql",
      "gen": {
        "python": {
          "out": "src",
          "package": "foo",
          "emit_sync_querier": true,
          "emit_async_querier": true,
          "query_parameter_limit": 5
        }
      }
    }
  ]


```

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

### Example PostgreSQL lint rules using EXPLAIN output

Source: https://docs.sqlc.dev/en/latest/_sources/reference/changelog.md.txt

Examples of lint rules that leverage PostgreSQL EXPLAIN output to check query cost and plan type.

```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'"
```

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

### Example Schema Definition

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

Define the initial tables for the database schema.

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

```

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

### Use generated code in Go

Source: https://docs.sqlc.dev/en/latest/_sources/tutorials/getting-started-postgresql.md.txt

Example of using the generated tutorial package to interact with the database.

```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)
	}
}
```

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

### Original package config (YAML)

Source: https://docs.sqlc.dev/en/latest/guides/migrating-to-sqlc-gen-python.html

This is an example of the previous configuration format using built-in Python support.

```yaml
  sql:
  - schema: "schema.sql"
    queries: "query.sql"
    engine: "postgresql"
    gen:
      python:
        out: "src"
        package: "foo"
        emit_sync_querier: true
        emit_async_querier: true
        query_parameter_limit: 5


```

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

### Configure Command Plugin

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

Example of configuring a command-line plugin with environment variables and a command.

```yaml
- name: "js"
  env:
  - PATH
  process: 
    cmd: "sqlc-gen-json"
```

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

### Example MySQL lint rules using EXPLAIN output

Source: https://docs.sqlc.dev/en/latest/_sources/reference/changelog.md.txt

Examples of lint rules that leverage MySQL EXPLAIN output to check query cost and primary key usage.

```yaml
...
rules:
- 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'"
```

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

### Build sqlc binary

Source: https://docs.sqlc.dev/en/latest/_sources/guides/development.md.txt

Compiles the sqlc binary and installs it to the local bin directory as an alias.

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

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

### Create authors table

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

Example SQL for creating an 'authors' table.

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

```

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

### SQLC Trace Log Example

Source: https://docs.sqlc.dev/en/latest/_sources/reference/environment-variables.md.txt

An example of the detailed execution trace output generated by SQLC, showing task creation, region timings, and goroutine information.

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

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

### Define and Migrate Foo Table with dbmate

Source: https://docs.sqlc.dev/en/latest/_sources/howto/ddl.md.txt

Example of up and down migration SQL statements for the 'foo' table using dbmate syntax.

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

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

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

### Example Application Query

Source: https://docs.sqlc.dev/en/latest/_sources/howto/verify.md.txt

This SQL query is used by the application to join user actions with user data.

```sql
-- name: GetUserActions :many
SELECT * FROM users u
JOIN user_actions ua ON u.id = ua.user_id
ORDER BY created_at;
```

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

### Example Query Before Schema Change

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

An initial query that joins user actions with users, ordered by creation timestamp.

```sql
-- name: GetUserActions :many
SELECT * FROM users u
JOIN user_actions ua ON u.id = ua.user_id
ORDER BY created_at;

```

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

### Example Production Tables

Source: https://docs.sqlc.dev/en/latest/_sources/howto/verify.md.txt

These SQL statements define the initial table structures in production.

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

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

### Per-Package Configuration Structure (v1)

Source: https://docs.sqlc.dev/en/latest/_sources/howto/overrides.md.txt

Example of how per-package configurations are structured within the `packages` list in version 1 of the sqlc configuration.

```yaml
version: "1"
packages:
  - name: "db"
    path: "internal/db"
    queries: "./sql/query/"
    schema: "./sql/schema/"
    engine: "postgresql"
    overrides: [...]
```

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

### SQL Schema for Students and Test Scores

Source: https://docs.sqlc.dev/en/latest/_sources/reference/changelog.md.txt

Defines the 'students' and 'test_scores' tables used in the examples. No specific setup is required beyond executing these SQL statements.

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

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

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

### Define User and User Actions Tables (SQL)

Source: https://docs.sqlc.dev/en/latest/_sources/reference/changelog.md.txt

Defines the initial schema for 'users' and 'user_actions' tables. These are the base tables used in the example.

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

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

### Define PostgreSQL Schema and Queries

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

Example SQL schema and queries for generating Go database access methods.

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

-- 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: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;
```

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

### Execute transactional updates in Go

Source: https://docs.sqlc.dev/en/latest/_sources/howto/transactions.md.txt

Examples of using WithTx to perform transactional updates with different database drivers.

```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()
}
```

```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)
}
```

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

### PostgreSQL sqlc Configuration for CopyFrom

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

Example sqlc configuration file (`sqlc.yaml`) specifying PostgreSQL as the engine and `pgx/v5` as the SQL package for `CopyFrom` operations.

```yaml
version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "query.sql"
    gen:
      go:
        package: "db"
        sql_package: "pgx/v5"
        out: "db"


```

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

### SQLC Rule Examples using EXPLAIN Output

Source: https://docs.sqlc.dev/en/latest/_sources/howto/vet.md.txt

YAML configuration demonstrating rules based on PostgreSQL and MySQL EXPLAIN output. These rules check for query cost and specific plan node types or table 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'"
```

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

### Install sqlc-gen-json Plugin

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

Install the `sqlc-gen-json` plugin to prevent test failures. This plugin is required for certain tests to pass.

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

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

### Fetch SQLite Driver and Build Project

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

Commands to fetch the necessary SQLite driver and build the Go project.

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

```

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

### Go Program Using Generated SQLC Code

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

This Go program demonstrates how to use the generated `tutorial` package to interact with a MySQL database. It includes setting up the database connection, listing authors, creating an author, fetching an author, and verifying the inserted data. Ensure your database connection parameters and schema are correctly configured.

```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)
	}
}

```

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

### Update sqlc Installation Path

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

Use the new import path for installing sqlc v1.20.0 and future versions with the go tool.

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

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

### Run SQL Queries with Generated Go Code

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

This Go program demonstrates how to use the sqlc-generated 'tutorial' package to interact with a PostgreSQL database. It includes listing authors, creating a new author, and fetching the created author to verify the operation. Ensure your database connection parameters and schema match the example.

```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)
	}
}

```

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

### Initialize Go module

Source: https://docs.sqlc.dev/en/latest/_sources/tutorials/getting-started-postgresql.md.txt

Initialize a new Go module for the project.

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

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

### Start Database with Docker Compose

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

Start the necessary database instances in detached mode using Docker Compose. This is a prerequisite for running database-dependent tests.

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

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

### Initialize Go Module

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

Initializes a new Go module for your project. This command is run in the project's root directory.

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

```

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

### Go Queries for Basic Inserts

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

Go code for the `db` package, demonstrating how to execute a basic INSERT statement using `ExecContext`.

```go
package db

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
}

func New(db DBTX) *Queries {
	return &Queries{db: db}
}

type Queries struct {
	db DBTX
}

const createAuthor = `-- name: CreateAuthor :exec
INSERT INTO authors (bio) VALUES ($1)
`

func (q *Queries) CreateAuthor(ctx context.Context, bio string) error {
	_, err := q.db.ExecContext(ctx, createAuthor, bio)
	return err
}

```

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

### Initialize Go Module

Source: https://docs.sqlc.dev/en/latest/_sources/tutorials/getting-started-sqlite.md.txt

Initializes a new Go module for the project. This command must be run in the project's root directory.

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

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

### Run Go Program with SQLite and sqlc

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

This Go program demonstrates using sqlc-generated code to interact with a SQLite database. It includes setting up the in-memory database, creating tables, inserting an author, and fetching the inserted author.

```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
	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)
	}
}

```

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

### Configure WASM Plugin

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

Example of configuring a WASM plugin with a URL and SHA256 checksum.

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

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

Defines the 'students' and 'test_scores' tables used in the embedding example.

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

```

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

### Define Database Schema

Source: https://docs.sqlc.dev/en/latest/_sources/howto/embedding.md.txt

Initial schema setup for students and test scores tables.

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

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

### Fetch MySQL Driver and Build Project

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

These commands are necessary to fetch the MySQL driver and build your Go project before it can be compiled and run with the generated sqlc code.

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

```

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

### Define Author Table

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

Defines the 'authors' table schema. This is a prerequisite for the subsequent query examples.

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

```

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

### Define SQL schema for records

Source: https://docs.sqlc.dev/en/latest/_sources/howto/transactions.md.txt

SQL schema definition for the records table used in the examples.

```sql
CREATE TABLE records (
  id SERIAL PRIMARY KEY,
  counter INT NOT NULL
);
```

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

### Initialize Go Module

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

Initializes a new Go module. This command is run once at the beginning of a Go project.

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

```

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

### Define authors table schema

Source: https://docs.sqlc.dev/en/latest/_sources/howto/select.md.txt

SQL schema definition for the authors table used in examples.

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

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

### Default Positional Parameter Generation

Source: https://docs.sqlc.dev/en/latest/_sources/howto/named_parameters.md.txt

Example of default parameter naming when sqlc lacks context.

```sql
-- name: UpsertAuthorName :one
UPDATE author
SET
  name = CASE WHEN $1::bool
    THEN $2::text
    ELSE name
    END
RETURNING *;
```

```go
type UpdateAuthorNameParams struct {
	Column1   bool   `json:""`
	Column2_2 string `json:"_2"`
}
```

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

### sqlc Configuration with Database URI and Environment Variables

Source: https://docs.sqlc.dev/en/latest/_sources/reference/config.md.txt

Configure the database connection using a URI that includes environment variables for sensitive information like passwords. This example uses a PostgreSQL database and specifies Go code generation settings.

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