sqlc

sqlc is a SQL compiler that generates fully type-safe idiomatic Go code from your SQL queries. This eliminates the need to write boilerplate SQL querying code manually.

The workflow consists of three steps:

1. Write SQL queries: Define your database schema and queries in SQL.
2. Run sqlc: Execute the compiler to generate Go code that provides type-safe interfaces for those queries.
3. Write application code: Call the methods generated by sqlc within your Go application.

By default, sqlc generates Go structs based on your database tables. If a table name is pluralized (e.g., authors), sqlc will attempt to use the singular form of that name for the struct name (e.g., Author).

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

package db

// Struct names use the singular form of table names
type Author struct {
	ID   int
	Name string
}

sqlc is a SQL compiler that generates type-safe code from your SQL queries. The workflow consists of three steps:

1. Write SQL: Define your database schema and write your queries in standard SQL.
2. Run sqlc: Execute the sqlc compiler to generate type-safe interfaces and code based on those queries.
3. Use generated code: Write your application logic by calling the generated functions, ensuring type safety between your application and the database.

For nullable columns in a struct, sqlc uses the appropriate null types from the database/sql or pgx packages (e.g., sql.NullString for nullable TEXT).

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

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

sqlc uses github.com/google/uuid for UUID support. If you are using the pgx/v5 driver, it uses pgtype.UUID.

MySQL UUID Workaround: MySQL does not have a native uuid type. If you store UUIDs as BINARY(16) using UUID_TO_BIN, sqlc defaults to mapping them to sql.NullString. To map these to uuid.UUID automatically, you must use an overrides configuration in your sqlc.json file.

{
  "overrides": [
    {
      "column": "*.uuid",
      "go_type": "github.com/google/uuid.UUID"
    }
  ]
}

Every SQL query in sqlc must be preceded by a comment annotation that specifies the name of the generated Go method and the command type. The required format is:

-- name: <name> <command>

The WithTx method is a part of the Queries struct generated by sqlc. It allows for dependency injection of a transaction into the query runner.

Internally, sqlc generates a DBTX interface that both *sql.DB (or connection pools) and *sql.Tx (transactions) satisfy. When you call WithTx(tx), the returned Queries struct uses the transaction as its underlying db field, ensuring all subsequent method calls on that instance are executed within the context of that specific transaction.

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

func (q *Queries) WithTx(tx DBTX) *Queries {
	return &Queries{
		db: tx,
	}
}

sqlc generates code by parsing CREATE TABLE and ALTER TABLE statements. It tracks changes to the schema (adding columns, dropping columns, or renaming tables) to produce the correct corresponding Go structs and types.

CREATE TABLE authors (
  id          SERIAL PRIMARY KEY,
  birth_year  int    NOT NULL
);

ALTER TABLE authors ADD COLUMN bio text NOT NULL;
ALTER TABLE authors DROP COLUMN birth_year;
ALTER TABLE authors RENAME TO writers;