### Install sqlc using go install

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

Install the latest version of sqlc using Go 1.21+.

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

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

### Start database instances with Docker Compose

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

Launch the required database instances (PostgreSQL and MySQL) for running tests. Ensure Docker is installed and running.

```bash
docker compose up -d

```

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

### Run sqlc example tests

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

Execute tests specifically located in the examples folder. This requires the 'examples' tag to be enabled.

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

```

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

### Install sqlc using go install

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

Install recent versions of sqlc using Go 1.21+. Ensure you have Go installed and configured.

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

```

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

### Run tests with examples tag

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

Run tests specifically tagged with `examples`, which require database instances.

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

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

### dbmate Migration Example

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

A dbmate migration example showing 'up' and 'down' directives for a 'foo' table.

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

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

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

### Install sqlc on Ubuntu using Snap

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

Use this command to install sqlc on Ubuntu using the Snap package manager.

```bash
sudo snap install sqlc
```

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

### 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 install command.

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

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

### SQLC Configuration Example

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

A comprehensive example of a SQLC configuration file, demonstrating various settings for package generation, query and schema paths, database engine, and 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

```

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

### sqlc/db-prepare rule with Managed Database

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

Example configuration for the sqlc/db-prepare rule using a managed database for automatic schema setup.

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

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

### SQL Catalog Output Example

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

Example output when the dumpcatalog debug flag is enabled, showing the database catalog structure.

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

```

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

### Version 2 Configuration Example

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

A basic configuration file for version 2, demonstrating settings for PostgreSQL and MySQL.

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

```

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

### PostgreSQL EXPLAIN Rule Example

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

Example of a rule checking PostgreSQL EXPLAIN output for high cost.

```yaml
rules:
- name: postgresql-query-too-costly
  message: "Query cost estimate is too high"
  rule: "postgresql.explain.plan.total_cost > 1.0"
```

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

### Atlas Migration Example

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

An example of a CREATE TABLE statement for the 'post' table using Atlas migration format.

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

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

### SQL AST Output Example

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

Example output when the dumpast debug flag is enabled, 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"
      }),

```

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

### Install sqlc-gen-json

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

Build and install the `sqlc-gen-json` tool, which is required to prevent test failures.

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

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

### Connect to PostgreSQL using pgx

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

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

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

### MySQL EXPLAIN Rule Example

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

Example of a rule checking MySQL EXPLAIN output for high cost.

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

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

### Install sqlc on macOS using Homebrew

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

Use this command to install sqlc on macOS if you have Homebrew installed.

```bash
brew install sqlc
```

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

### Example Production Tables

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

These SQL statements define the initial table structure for 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
);

```

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

### SQLC Trace Log Example

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

An example of SQLC trace output, showing execution times for different tasks and packages.

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

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

### Tern Migration Example

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

A Tern migration example showing 'create above' and 'drop below' directives for the 'comment' table.

```sql
CREATE TABLE comment (id int NOT NULL, text text NOT NULL);
----
DROP TABLE comment;
```

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

### PostgreSQL EXPLAIN Rule Example (Sequential Scan)

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

Example of a rule checking PostgreSQL EXPLAIN output for sequential scans.

```yaml
rules:
- name: postgresql-no-seq-scan
  message: "Query plan results in a sequential scan"
  rule: "postgresql.explain.plan.node_type == 'Seq Scan'"
```

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

### Golang-migrate Up Migration

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

An example of an 'up' migration file for the 'post' table using the YYYYMMDD format.

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

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

### Example Application Query

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

This SQL query is used by the application to retrieve user actions.

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

```

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

### MySQL EXPLAIN Rule Example (Primary Key)

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

Example of a rule checking MySQL EXPLAIN output to ensure primary key usage.

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

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

### Start databases with Docker Compose

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

Use Docker Compose to start the necessary database instances in detached mode for running tests.

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

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

### Example Production Tables

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

These SQL statements define the initial table structures for `users` and `user_actions` in a production database.

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

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

### Custom Rule Definition and Usage

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

Example demonstrating how to define and enable custom rules for sqlc vet.

```yaml
version: 2
sql:
  - schema: "query.sql"
    queries: "query.sql"
    engine: "postgresql"
    gen:
      go:
        package: "authors"
        out: "db"
    rules:
      - no-delete
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\")"
```

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

### 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, ordered by creation time. It is intended to be used with the initial schema.

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

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

### Configuring Schema and Migration Directory

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

Example sqlc configuration to specify a schema file and a directory for SQL migrations.

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

```

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

### PostgreSQL Lint Rule Examples

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

Examples of PostgreSQL lint rules that utilize EXPLAIN output to check query cost and plan types.

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

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

### sqlc Configuration for PostgreSQL and pgx/v5

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

Example sqlc configuration file specifying PostgreSQL as the engine, using query.sql for schema and queries, and generating Go code with pgx/v5.

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

```

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

### Configure Process Plugin

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

Example configuration for a process-based plugin named 'jsonb', specifying the command to execute and how to use it in the codegen section with custom options.

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

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

### sqlc Lint Rule Examples for PostgreSQL and MySQL

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

Examples of specific lint rules for PostgreSQL and MySQL that leverage EXPLAIN output to check query costs and plan details.

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

```

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

### Golang-migrate Down Migration

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

An example of a 'down' migration file for the 'post' table using the YYYYMMDD format.

```sql
DROP TABLE post;
```

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

### Define PostgreSQL Schema and Queries

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

Example SQL file containing table definitions and named queries for use with sqlc.

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

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

### Build sqlc with an alias

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

Build the sqlc project locally and install it under the `sqlc-dev` alias for development purposes.

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

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

### Plugin Configuration with WASM and Environment Variables

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

Configure a plugin to use a WebAssembly binary from a URL and specify environment variables. This example shows how to set up the sqlc-gen-python plugin.

```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"
- name: "js"
  env:
  - PATH
  process: 
    cmd: "sqlc-gen-json"

```

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

### Example SQL Queries

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

Define SQL queries for CRUD operations on an authors table.

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

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

### Create and Drop Table Statement

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

Example of a CREATE TABLE statement followed by its corresponding DROP TABLE statement.

```sql
CREATE TABLE comment (id int NOT NULL, text text NOT NULL);
---- create above / drop below ----
DROP TABLE comment;
```

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

### Original Package Configuration (YAML)

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

Example of the previous package configuration in sqlc.yaml using the deprecated built-in Python generator.

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

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

### Original Package Configuration (JSON)

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

Example of the previous package configuration in sqlc.json using the deprecated built-in Python generator.

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

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

### MySQL LOAD DATA Configuration

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

Example sqlc configuration for using the go-sql-driver/mysql with MySQL LOAD DATA.

```yaml
version: "2"
sql:
  - engine: "mysql"
    queries: "query.sql"
    schema: "query.sql"
    gen:
      go:
        package: "db"
        sql_package: "database/sql"
        sql_driver: "github.com/go-sql-driver/mysql"
        out: "db"
```

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

### Configure WASM Plugin

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

Example configuration for a WASM plugin named 'greeter', specifying its URL and SHA256 hash, and how to use it in the codegen section.

```yaml
version: '2'
plugins:
- name: greeter
  wasm:
    url: https://github.com/sqlc-dev/sqlc-gen-greeter/releases/download/v0.1.0/sqlc-gen-greeter.wasm
    sha256: afc486dac2068d741d7a4110146559d12a013fd0286f42a2fc7dcd802424ad07
sql:
- schema: schema.sql
  queries: query.sql
  engine: postgresql
  codegen:
  - out: gen
    plugin: greeter
    options:
      lang: en-US
```

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

### Database Connection with Environment Variable

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

Example of configuring a database connection using a URI that includes an environment variable for the password. This allows for dynamic credential management.

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

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

### Configure Process Plugin

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

Example of configuring a process-based plugin in sqlc. The `cmd` field specifies the command to execute, and `options` are passed as a JSON string.

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

```

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

### Fetch MySQL Driver and Build Project

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

Fetches the necessary MySQL driver for Go and builds the project. Ensure database connection parameters in `sql.Open()` are correctly set.

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

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

### Configure Plugin with Environment Variables

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

Example configuration for a plugin named 'test' that requires access to the 'PATH' environment variable. It also shows the WASM plugin details.

```yaml
version: '2'
sql:
- schema: schema.sql
  queries: query.sql
  engine: postgresql
  codegen:
  - out: gen
    plugin: test
plugins:
- name: test
  env:
  - PATH
  wasm:
    url: https://github.com/sqlc-dev/sqlc-gen-test/releases/download/v0.1.0/sqlc-gen-test.wasm
    sha256: 138220eae508d4b65a5a8cea555edd155eb2290daf576b7a8b96949acfeb3790
```

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

### Connect to PostgreSQL using pgxpool

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

Demonstrates how to establish a connection pool using pgxpool for production applications and create a db.Querier.

```go
import (
    "github.com/jackc/pgx/v5/pgxpool"
    "example.com/sqlc-tutorial/db"
)

func main() {
    pool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
        os.Exit(1)
    }
    defer pool.Close()

    q := db.New(pool)
    // Use q the same way as with single connections
}
```

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

### Example Table and Generated Structs (Default)

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

Defines sample SQL tables and shows the default Go structs generated by sqlc, illustrating default struct naming conventions.

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

CREATE TABLE book_publishers (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL
);
```

```go
package db

import (
	"database/sql"
)

type Author struct {
	ID   int64
	Name string
	Bio  sql.NullString
}

type Publisher struct {
	ID   int64
	Name string
}
```

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

### Example sqlc vet rules using EXPLAIN output

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

YAML configuration for sqlc vet rules that leverage EXPLAIN output from PostgreSQL and MySQL to enforce query performance and structure.

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

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

### 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 is run in the project's root directory.

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

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

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

```

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

### sqlc Vet Configuration with Database Connectivity

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

Configure sqlc vet to connect to a live database for query preparation. This example uses the built-in `sqlc/db-prepare` rule and specifies a PostgreSQL database URI.

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

```

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

### MySQL Lint Rule Examples

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

Examples of MySQL lint rules that utilize EXPLAIN output to check query cost and 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'"
```

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

### Go Code for Prepared Queries with pgx/v5

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

This Go code demonstrates how to use SQLC-generated prepared queries. It includes the `DBTX` interface for database operations, the `Queries` struct, and methods for preparing and executing queries. This example is relevant when using drivers like `pgx/v5` that have implicit support for prepared statements.

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

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

### SQL Schema for Students and Test Scores

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

Defines the 'students' and 'test_scores' tables used in the examples. This schema is a prerequisite for the subsequent query 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
);
```

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

### Example Rename Mapping

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

Use the `rename` mapping to customize struct field names generated from column names. This example renames the `spotify_url` column to `SpotifyURL`.

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

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

### Fetch SQLite Driver and Build Project

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

Fetches the necessary SQLite driver for Go and builds the project. This is required before running the Go program.

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

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

### Example: Overriding UUID and Date Types

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

This example demonstrates how to use both `db_type` and `column` overrides. It maps PostgreSQL's `uuid` type to `github.com/google/uuid.UUID` and a specific column `users.birthday` to `time.Time`. Note the `nullable: true` for the `uuid` override.

```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"
          nullable: true
          go_type:
            import: "github.com/google/uuid"
            type: "UUID"
        - column: "users.birthday"
          go_type: "time.Time"

```

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

### Goose Up Migration

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

An example of a 'goose Up' migration for the 'post' table.

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

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

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

### SQL Table Definitions

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

Example SQL schema definitions for `authors` and `book_publishers` tables.

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

CREATE TABLE book_publishers (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL
);
```

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

### SQL-migrate Up Migration

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

An example of an 'Up' migration for the 'people' table using sql-migrate directives.

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

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

### Connect to PostgreSQL using pgx

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

Connect to a PostgreSQL database using a connection URL and create a new db.Querier instance.

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

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

### Create Authors Table

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

Defines the 'authors' table schema used in the delete examples.

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

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

### Go Program Using Generated Code

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

A Go program demonstrating how to use the sqlc-generated 'tutorial' package to interact with a PostgreSQL database. Ensure the database connection string is correctly configured.

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

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

### MySQL CAST Support Example

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

This SQL snippet demonstrates the use of CAST in a MySQL query, which sqlc now understands.

```sql
CREATE TABLE foo (bar BOOLEAN NOT NULL);

-- name: SelectColumnCast :many
SELECT CAST(bar AS BIGINT) FROM foo;
```

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

### Run all tests

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

Execute all tests within the project using the `go test` command.

```bash
go test ./...
```

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

### Configure sqlc Lint Rule with EXPLAIN Output

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

Example of a sqlc configuration file defining a lint rule that uses EXPLAIN output to check for index usage in PostgreSQL SELECT queries.

```yaml
version: 2
sql:
  - schema: "query.sql"
    queries: "query.sql"
    engine: "postgresql"
    database:
      uri: "postgresql://postgres:postgres@localhost:5432/postgres"
    gen:
      go:
        package: "db"
        out: "db"
    rules:
      - has-index
rules:
- name: has-index
  rule: >
    query.sql.startsWith("SELECT") &&
    !(postgresql.explain.plan.plans.all(p, has(p.index_name) || p.plans.all(p, has(p.index_name))))

```

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

### sqlc/db-prepare rule with PostgreSQL URI

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

Example configuration for the sqlc/db-prepare rule using a PostgreSQL database URI to validate queries.

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

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

### Example sqlc configuration with lint rules

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

This YAML configuration defines a PostgreSQL project with specific lint rules applied. It shows how to reference rule names in the `sqlc` configuration.

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

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

### sqlc CLI Usage

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

Displays the general usage instructions for the sqlc CLI, listing available commands and global flags.

```bash
Usage:
  sqlc [command]

Available Commands:
  compile     Statically check SQL for syntax and type errors
  completion  Generate the autocompletion script for the specified shell
  createdb    Create an ephemeral database
  diff        Compare the generated files to the existing files
  generate    Generate source code from SQL
  help        Help about any command
  init        Create an empty sqlc.yaml settings file
  push        Push the schema, queries, and configuration for this project
  verify      Verify schema, queries, and configuration for this project
  version     Print the sqlc version number
  vet         Vet examines queries

Flags:
  -f, --file string    specify an alternate config file (default: sqlc.yaml)
  -h, --help           help for sqlc
      --no-database    disable database connections (default: false)

Use "sqlc [command] --help" for more information about a command.
```

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

### Verify Database Migrations with GitHub Actions

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

This snippet demonstrates a GitHub Actions workflow to verify database migrations using sqlc. It checks out code, sets up sqlc and PostgreSQL, and runs `sqlc verify`.

```yaml
name: sqlc
on: [push]
jobs:
  verify:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - uses: sqlc-dev/setup-sqlc@v3
      with:
        sqlc-version: '1.31.1'
    - uses: sqlc-dev/action-setup-postgres@master
      with:
        postgres-version: "16"
      id: postgres
    - run: sqlc verify
      env:
        POSTGRESQL_SERVER_URI: ${{ steps.postgres.outputs.connection-uri }}?sslmode=disable
        SQLC_AUTH_TOKEN: ${{ secrets.SQLC_AUTH_TOKEN }}
  push:
    runs-on: ubuntu-latest
    if: ${{ github.ref == 'refs/heads/main' }}
    steps:
    - uses: sqlc-dev/setup-sqlc@v3
      with:
        sqlc-version: '1.31.1'
    - run: sqlc push
      env:
        SQLC_AUTH_TOKEN: ${{ secrets.SQLC_AUTH_TOKEN }}
```