# rok-sqlc
Write SQL, get typed Rust. rok-sqlc reads your PostgreSQL migrations and
annotated query files and generates plain [SQLx](https://github.com/launchbadge/sqlx)
code: row structs, typed ids, CRUD, typed search, relationship loaders, and
one function per query you write. The generated code is ordinary Rust you
commit and read. There is no runtime crate and no macro magic, and you don't
need a database to generate it.
**SQL in:**
```sql
-- name: ActiveProductsInCategory :many
SELECT id, sku, name, price
FROM products
WHERE category_id = $1 AND status = 'active'
ORDER BY name, id;
```
**Rust out** (in `src/db/queries.rs`):
```rust
/// A row returned by `active_products_in_category`.
#[derive(Debug, Clone, PartialEq, sqlx::FromRow)]
pub struct ActiveProductsInCategoryRow {
/// The `id` column.
pub id: ProductId,
/// The `sku` column.
pub sku: String,
/// The `name` column.
pub name: String,
/// The `price` column.
pub price: rust_decimal::Decimal,
}
/// Runs the `ActiveProductsInCategory` query (`:many`).
pub async fn active_products_in_category<'e, E>(
executor: E,
category_id: CategoryId,
) -> Result<Vec<ActiveProductsInCategoryRow>, sqlx::Error>
where
E: sqlx::PgExecutor<'e>,
```
From the schema alone, every table also gets a typed `get_by_id`, unique-key
lookups, `list`, `count`, `create`, partial `update`, `delete`, a typed
`search` with keyset pagination, and loaders for the relationships its
foreign keys imply.
Every code sample in this README was generated by rok-sqlc from the example
crates in [`docs/examples/`](docs/examples/), and a test keeps the two in
sync.
> **Status:** pre-release (0.1.0 in progress), PostgreSQL and SQLx 0.9 only.
> See [Limitations and roadmap](#limitations-and-roadmap).
## Contents
- [Install](#install)
- [Quickstart](#quickstart)
- [What gets generated](#what-gets-generated)
- [Writing query files](#writing-query-files)
- [Conventions and the `rok` preset](#conventions-and-the-rok-preset)
- [Configuration](#configuration)
- [Using it from `build.rs`](#using-it-from-buildrs)
- [CI with `--check`](#ci-with---check)
- [Supported SQL and type mapping](#supported-sql-and-type-mapping)
- [Limitations and roadmap](#limitations-and-roadmap)
- [Contributing](#contributing)
- [License](#license)
## Install
```sh
cargo install rok-sqlc
```
To track unreleased changes, install from the repository instead:
`cargo install --git https://github.com/ateeq1999/rok-sqlc rok-sqlc`.
There are two Rust version floors:
- **The tool** needs Rust 1.88 or newer.
- **The generated code** targets SQLx 0.9, so the crate that uses it needs
Rust 1.94 or newer (SQLx 0.9's minimum).
The generated `db/mod.rs` lists the dependencies your crate needs. It names
only the SQLx features and crates the schema actually uses:
```rust
// The generated code requires these dependencies in the consuming crate
// (SQLx 0.9 needs Rust 1.94 or newer):
// sqlx = { version = "0.9", features = ["runtime-tokio", "postgres", "derive", "uuid", "chrono", "rust_decimal"] }
// uuid = "1"
// chrono = "0.4"
// rust_decimal = "1"
```
So the quickstart crate's `Cargo.toml` has:
```toml
[dependencies]
sqlx = { version = "0.9", features = ["runtime-tokio", "postgres", "derive", "uuid", "chrono", "rust_decimal"] }
uuid = "1"
chrono = "0.4"
rust_decimal = "1"
```
`serde_json` (for `JSON`/`JSONB` columns) and `serde` (with
[`derive = ["serde"]`](#configuration)) show up in the list when they are
needed.
## Quickstart
The complete crate is in [`docs/examples/quickstart`](docs/examples/quickstart).
**1. Write migrations.** A directory of migrations is read in filename order,
and `*.down.sql` files are skipped. Later migrations can `ALTER` earlier
tables:
```sql
CREATE TYPE product_status AS ENUM ('draft', 'active', 'archived');
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
category_id BIGINT NOT NULL REFERENCES categories (id),
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price NUMERIC(12, 2) NOT NULL,
status product_status NOT NULL DEFAULT 'draft',
tags TEXT[] NOT NULL DEFAULT '{}',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```
```sql
ALTER TABLE products ADD COLUMN description TEXT;
```
rok-sqlc never runs your migrations. Apply them with whatever tool you
already use, such as `sqlx migrate run` or `psql`.
**2. Write queries** for anything the generated CRUD doesn't cover. See
[`queries.sql`](docs/examples/quickstart/queries.sql) and
[Writing query files](#writing-query-files).
**3. Point rok-sqlc at them** with a `rok-sqlc.toml` (optional; flags work too):
```toml
schema = ["migrations/"]
queries = ["queries.sql"]
output = "src/db"
```
**4. Generate:**
```sh
rok-sqlc g
```
That is the same as
`rok-sqlc generate --schema migrations/ --queries queries.sql --output src/db`.
It writes:
```text
db/
├── categories/
│ ├── filter.rs
│ ├── mod.rs
│ ├── models.rs
│ ├── read.rs
│ ├── relationships.rs
│ └── write.rs
├── mod.rs
├── products/
│ ├── filter.rs
│ ├── mod.rs
│ ├── models.rs
│ ├── read.rs
│ ├── relationships.rs
│ └── write.rs
├── queries.rs
├── schema.rs
├── search.rs
└── types.rs
```
**5. Call it.** Declare the module (`pub mod db;`) and pass an executor
first: `&pool`, or `&mut *tx` to run several calls in one transaction. The
transaction boundary is always yours.
```rust
// Every generated function takes an executor first: here, the pool.
let tools = db::categories::write::create(
pool,
NewCategory {
name: "Tools".into(),
created_at: None,
},
)
.await?;
// Several calls in one transaction: pass `&mut *tx` instead.
let mut tx = pool.begin().await?;
let hammer = db::products::write::create(
&mut *tx,
NewProduct {
id: None,
category_id: tools.id,
sku: "HAM-1".into(),
name: "Claw hammer".into(),
price: Decimal::new(1999, 2),
status: Some(ProductStatus::Active),
tags: None,
created_at: None,
description: None,
},
)
.await?;
let patch = UpdateProduct {
price: Some(Decimal::new(1799, 2)),
description: Some(Some("16 oz, fibreglass handle".into())),
..UpdateProduct::default()
};
db::products::write::update(&mut *tx, hammer.id, patch).await?;
tx.commit().await?;
```
Commit the generated code. Regenerate whenever a migration or query file
changes, and check for drift in CI (see [CI with `--check`](#ci-with---check)).
## What gets generated
Each generated file starts with `// @generated by rok-sqlc. Do not edit by hand.`
rok-sqlc overwrites only files that carry that header. It refuses to replace
any other file unless you pass `-f/--force`. When a table, query file or
other source disappears, a later run removes its generated files (the stale
files).
### Per table: a resource module `db/<table>/`
| `models.rs` | The typed id (`ProductId`) or key struct, the row struct (`Product`), the create input (`NewProduct`), and the update patch (`UpdateProduct`). |
| `read.rs` | `get_by_id`, `exists_by_id`, a `get_by_<columns>` lookup per unique key, `list` and `list_page` (ordered by primary key), `count`, `search`, `search_after`. |
| `write.rs` | `create`, `update`, `delete` (and `restore`/`delete_permanently` with soft delete, `attach`/`detach`/`sync` on join tables). |
| `filter.rs` | The search filter (`ProductFilter`) and sort keys (`ProductSortKey`). |
| `relationships.rs` | A `load_<name>` loader per relationship, and `get_with_<name>` joined reads for to-one relationships. |
A table without a primary key gets `create`, `list` and `count` only. `search`
needs at least one sortable column, and `search_after` needs a primary key.
`filter.rs` exists only when one of them does, and `relationships.rs` only
when the table has relationships.
**Typed ids.** A single-column primary key gets its own type, which is used
for that column, for every foreign key that references it, and by every
function that takes the key. Passing a `CategoryId` where a `ProductId` is
expected is a compile error:
```rust
/// A row of the `products` table.
#[derive(Debug, Clone, PartialEq, sqlx::FromRow)]
pub struct Product {
/// The `id` column.
pub id: ProductId,
/// The `category_id` column.
pub category_id: CategoryId,
/// The `sku` column.
pub sku: String,
/// The `name` column.
pub name: String,
/// The `price` column.
pub price: rust_decimal::Decimal,
/// The `status` column.
pub status: types::ProductStatus,
/// The `tags` column.
pub tags: Vec<String>,
/// The `created_at` column.
pub created_at: chrono::DateTime<chrono::Utc>,
/// The `description` column.
pub description: Option<String>,
}
```
A composite primary key gets a key struct instead (`OrderItemKey`, with a
`key()` accessor on the row). A foreign key to a table outside the run keeps
its raw type.
**Create inputs and update patches.** `NewProduct` leaves out generated
columns (serial, identity, `GENERATED`). A column with a default becomes an
`Option`, and `None` lets the database default apply. `UpdateProduct` makes
every column optional: `None` leaves the column unchanged, and for a
nullable column `Some(None)` sets it to `NULL`. An empty patch issues no
`UPDATE` and returns the current row.
```rust
pub async fn update<'e, E>(
executor: E,
id: ProductId,
input: UpdateProduct,
) -> Result<Option<Product>, sqlx::Error>
where
E: sqlx::PgExecutor<'e>,
```
**Lookups.** Each unique key (a unique constraint, or a unique index on plain
columns) gets a lookup. Text keys are borrowed:
```rust
pub async fn get_by_sku<'e, E>(executor: E, sku: &str) -> Result<Option<Product>, sqlx::Error>
where
E: sqlx::PgExecutor<'e>,
```
**Typed search.** The filter has a list of typed conditions per column, and
they are combined with `AND`. Numbers, decimals, dates, times and typed ids
take `Ordered<T>` (`Eq`, `Ne`, `Lt`, `Le`, `Gt`, `Ge`, `Between`, `In`,
`NotIn`). Text columns take `Text` (`Eq`, `Ne`, `In`, `NotIn`, `Contains`,
`StartsWith`, `EndsWith`, matched literally). Booleans, UUIDs and enums take
`Equality<T>`. Each nullable column also gets an `*_is_null` check. Sorting
goes through the `ProductSortKey` allowlist, and every value is bound, so no
caller string ever reaches the SQL text:
```rust
let filter = ProductFilter {
price: vec![Ordered::Lt(Decimal::new(5000, 2))],
name: vec![Text::Contains("hammer".into())],
..ProductFilter::default()
};
let cheap = db::products::read::search(
pool,
filter,
Sort::asc(ProductSortKey::Price),
Page::first(20),
)
.await?;
```
`search` breaks ties by primary key, so pages are stable. `Page` and `Limit`
clamp page sizes to `1..=MAX_PAGE_SIZE` (500). `search_after(executor, filter,
after, limit)` does keyset pagination in primary-key order: pass the last key
you saw.
**Relationships.** Each foreign key between two tables generated together
gives a belongs-to on the child (`category_id` gives `category`) and a has-many
on the parent, named after the child module (`products`). If the foreign key
column is unique, the parent gets a has-one instead. A `parent_id`
self-reference gives `parent` and `children`. A join table (two `NOT NULL`
foreign keys that together form a unique key) relates its two tables
many-to-many, and its `write.rs` gains `attach`, `detach` and `sync`.
A loader takes rows you already fetched and loads one relationship for all of
them in a single `= ANY($1)` query, which avoids the N+1 pattern:
```rust
pub async fn load_category<'e, E>(
executor: E,
rows: &[Product],
) -> Result<HashMap<CategoryId, Category>, sqlx::Error>
```
```rust
let categories = db::products::relationships::load_category(pool, &cheap).await?;
for product in &cheap {
let category = &categories[&product.category_id];
println!("{} ({})", product.name, category.name);
}
```
To-many loaders return a `Vec` for every input key, empty when nothing is
related. A to-one relationship also gets a joined read that fetches the row
and its related row in one query:
```rust
pub async fn get_with_category<'e, E>(
executor: E,
id: ProductId,
) -> Result<Option<ProductWithCategory>, sqlx::Error>
```
The search filter gets a field per relationship, which is rendered as an
`EXISTS` subquery, so rows never repeat. For a to-many relationship it means
"at least one related row matches":
```rust
/// Matches rows with at least one of their `products` (`products` rows) matching this filter.
pub products: Option<Box<ProductFilter>>,
```
To rename or opt out of an inferred relationship, use
[`[relationships]`](#configuration). Two relationships of one table with the
same name fail generation, and the error names both.
### Shared modules
| `mod.rs` | Declares every module and lists the dependencies to add. Holds `UpdateOutcome` when a table has a version column. |
| `schema.rs` | `SCHEMA_SQL`: a verbatim snapshot of the input schema. It is never executed. |
| `search.rs` | `Page`, `Limit`, `Sort`, `Direction`, the condition types, and `MAX_RELATIONSHIP_DEPTH`. |
| `types.rs` | A Rust enum for each enum type and each value-list column, keeping its exact database labels. |
| `session.rs` | `set_tenant` and `set_user`, when the conventions name a setting. |
| `queries.rs` or `queries/` | One function per annotated query. |
```rust
/// The `product_status` enum type.
#[derive(Debug, Clone, Copy, PartialEq, Eq, Hash, sqlx::Type)]
#[sqlx(type_name = "product_status")]
pub enum ProductStatus {
```
`schema`, `search`, `session` and `types` are reserved module names, as is
`queries` when you pass query files. A table that would generate one of
those modules fails with an error that says so.
**Code quality.** Every public item is documented. Generated code passes
`#![deny(missing_docs)]`, `cargo clippy -- -D warnings` and a fixed set of
pedantic lints (see [ADR 0013](docs/adr/0013-generated-code-quality-bar.md)),
and it is rustfmt-clean. It never forces your crate to relax its lints.
**Serde.** With `derive = ["serde"]` (or `-d serde`), rows, typed ids
(transparent), key structs, enums (by database label), inputs, patches,
joined-read structs (`ProductWithCategory`), and query rows and parameters
derive `Serialize` and `Deserialize`. Without it, generated code doesn't
mention serde.
## Writing query files
A query file holds SQL statements, each starting with a `-- name:` line:
```text
-- name: <Name> :<mode>
-- param: $<n> <name> <RustType> (optional, repeatable)
-- column: <name> <RustType> (optional, repeatable)
<one SQL statement>
```
The name becomes the function name in snake_case. Other full-line comments
are dropped.
| `:one` | `fetch_one` | the row; no row is `sqlx::Error::RowNotFound` |
| `:maybe_one` | `fetch_optional` | `Option<row>` |
| `:many` | `fetch_all` | `Vec<row>` |
| `:exec` | `execute` | `()` |
| `:execrows` | `execute` | `u64`, the number of affected rows |
**Parameter types are inferred only from positions that settle them.** These
are:
- a comparison, `IN`, `BETWEEN` or `LIKE` against a known column;
- `= ANY($n)`, which takes a slice;
- an `INSERT … VALUES` position, an `UPDATE … SET` target, or
`ON CONFLICT … DO UPDATE SET`;
- `LIMIT`/`OFFSET` (`i64`);
- an explicit cast to a supported type.
A placeholder anywhere else fails generation. The error names the fix, a
`-- param:` override.
**Keys keep their typed ids.** A parameter typed by a primary key or foreign
key column takes that table's typed id, and so does a result column taken
straight from one, as in `ActiveProductsInCategory` at the top
(`category_id: CategoryId`, `id: ProductId`). A parameter compared with two
different ids falls back to the plain type, and so does any computed
expression.
Text parameters are taken as `&str` (`&CurrencyId` for a text key) and
arrays as slices. A query with more than three parameters takes a
`{Name}Params` struct instead:
```sql
-- name: UpsertProductPrice :one
INSERT INTO products (category_id, sku, name, price)
VALUES ($1, $2, $3, $4)
ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price
RETURNING *;
```
```rust
pub async fn upsert_product_price<'e, E>(
executor: E,
params: UpsertProductPriceParams,
) -> Result<super::products::models::Product, sqlx::Error>
```
**Result columns are resolved against the schema.** rok-sqlc infers `*`,
`t.*`, plain and qualified columns, aliases, and `COUNT(…)` (`i64`). The
outer side of a `LEFT`/`RIGHT`/`FULL` join becomes `Option`. A result that is
exactly a table's columns, such as the `RETURNING *` above, reuses that
table's row struct. A single column decodes as a scalar. Anything else gets
a `{Name}Row` struct.
Any other expression needs an alias and a `-- column:` override, and a
parameter rok-sqlc can't type needs a `-- param:` override:
```sql
-- name: SearchProductNames :many
-- param: $1 pattern String
-- column: display_name String
SELECT sku, upper(name) AS display_name
FROM products
WHERE lower(name) LIKE lower($1)
ORDER BY sku;
```
```rust
pub async fn search_product_names<'e, E>(
executor: E,
pattern: &str,
) -> Result<Vec<SearchProductNamesRow>, sqlx::Error>
```
**More rules:**
- `WITH` queries work: each CTE is treated as a table.
- An `ON CONFLICT (cols)` target must be one of the table's unique keys, and
`ON CONFLICT ON CONSTRAINT name` must name its primary key or a unique
constraint: the declared name, or PostgreSQL's default for an unnamed one
(`products_pkey`, `products_sku_key`). A unique index is no constraint, so
PostgreSQL doesn't accept its name there.
- A query is exactly one statement. To run several, call several functions
on `&mut *tx`.
- The SQL is embedded verbatim and bound positionally. It is not checked
against a database.
- One query file generates `db/queries.rs`. A directory (its `*.sql` files)
or several `--queries` inputs generate `db/queries/<file stem>.rs`, one
module per file. Stems must be snake_case Rust identifiers.
- Conventions never rewrite query files: your SQL runs exactly as written.
## Conventions and the `rok` preset
A convention says what a column means in every table that has it. All
conventions are **off by default**. Turn them on in the `[conventions]`
section of `rok-sqlc.toml` or the JSON payload; they have no flags. The
complete example is in [`docs/examples/conventions`](docs/examples/conventions):
```toml
[conventions]
preset = "rok"
```
| `tenant_column` | `organization_id` | Every function takes the tenant right after the executor and adds `organization_id = $tenant` to every statement. The column is left out of inputs and patches. |
| `tenant_setting` | `app.organization_id` | Generates `db::session::set_tenant`, for row-level security policies. |
| `user_setting` | `app.user_id` | Generates `db::session::set_user`, for audit triggers. |
| `soft_delete_column` | `deleted_at` | `delete` sets it, `restore` clears it, and `delete_permanently` removes the row. Reads skip deleted rows, and filters get `include_deleted`. It must be a nullable `TIMESTAMP`/`TIMESTAMPTZ`. |
| `managed_columns` | `["updated_at", "row_version"]` | Left out of create inputs and update patches, but kept in rows. The database maintains them. |
| `version_column` | `row_version` | `update` takes `expected_version` and returns `UpdateOutcome` (optimistic concurrency). The column is always managed. |
Each explicit key overrides the preset's value. An empty string turns a
single-column convention off (`version_column = ""`).
With the preset, the inputs leave out the tenant, managed and soft-delete
columns:
```rust
pub struct NewInvoice {
/// The `id` column.
/// `None` applies the column default.
pub id: Option<InvoiceId>,
/// The `number` column.
pub number: String,
/// The `total` column.
pub total: rust_decimal::Decimal,
/// The `status` column.
/// `None` applies the column default.
pub status: Option<types::InvoiceStatus>,
}
```
`update` is scoped to the tenant and versioned:
```rust
pub async fn update<'e, E>(
executor: E,
organization_id: OrganizationId,
id: InvoiceId,
expected_version: i64,
input: UpdateInvoice,
) -> Result<UpdateOutcome<Invoice>, sqlx::Error>
```
```rust
let mut tx = pool.begin().await?;
// For row-level security policies and audit triggers.
db::session::set_tenant(&mut tx, org).await?;
db::session::set_user(&mut tx, user).await?;
// The tenant comes right after the executor, on every function.
let invoice = db::invoices::write::create(
&mut *tx,
org,
NewInvoice {
id: None,
number: "INV-001".into(),
total: Decimal::new(25_000, 2),
status: None,
},
)
.await?;
// Optimistic concurrency: the update applies only at the version read.
let patch = UpdateInvoice {
status: Some(InvoiceStatus::Paid),
..UpdateInvoice::default()
};
let outcome =
db::invoices::write::update(&mut *tx, org, invoice.id, invoice.row_version, patch).await?;
match outcome {
UpdateOutcome::Updated(paid) => assert_eq!(paid.row_version, invoice.row_version + 1),
UpdateOutcome::NotFound => println!("deleted meanwhile"),
UpdateOutcome::VersionConflict => println!("changed meanwhile; reload and retry"),
}
// Soft delete: reads skip the row until it is restored.
db::invoices::write::delete(&mut *tx, org, invoice.id).await?;
let hidden = db::invoices::read::get_by_number(&mut *tx, org, "INV-001").await?;
assert!(hidden.is_none());
db::invoices::write::restore(&mut *tx, org, invoice.id).await?;
tx.commit().await
```
The database keeps managed columns up to date: in the example, a
[trigger](docs/examples/conventions/migrations/0002_touch_row.sql) sets
`updated_at` and bumps `row_version`. A unique key that includes the tenant
column drops it from the lookup's name, so `UNIQUE (organization_id, number)`
gives `get_by_number`. A tenant column's foreign key is never treated as a
relationship.
## Configuration
Options come from four layers. The most specific wins:
**flags > JSON payload (`-j`) > `rok-sqlc.toml` > defaults**. A list (such as
`schema`, `queries` or `derive`) replaces the lower layer's list whole and is
never appended to. `[conventions]` keys and `[relationships]` entries merge
one by one.
### `rok-sqlc.toml`
Without `-c`, rok-sqlc looks for `rok-sqlc.toml` in the current directory and
then in each parent, like Cargo does. Paths in the file are relative to the
file. Unknown keys are errors, so a typo can't silently do nothing. This is
every key:
```toml
schema = ["migrations/"] # a path or a list: SQL files or migration directories
queries = ["queries/"] # a path or a list: query files or directories
output = "src/db" # the default
resource = "products" # generate one table; rarely useful in a file
derive = ["serde"] # extra derives for generated types; "serde" is the only one
[conventions] # all off unless set; see "Conventions"
preset = "rok"
tenant_column = "organization_id"
tenant_setting = "app.organization_id"
user_setting = "app.user_id"
soft_delete_column = "deleted_at"
managed_columns = ["updated_at", "row_version"]
version_column = "row_version"
[relationships.invoices] # by table, then by inferred relationship name
created_by_user = "author" # rename: load_author, get_with_author, filter field author
approved_by_user = false # opt out, together with the other side
```
`[relationships]` entries are checked against the whole schema. An unknown
table or relationship, an invalid name, or a rename that collides fails with
the entry's name (`relationships.invoices.created_by_user`). `true` is an
error, not a no-op.
### JSON payload
`-j/--json` takes the same keys as JSON. Pass it inline
(`-j '{"output":"src/db"}'`), from a file (`-j @config.json`), or from stdin
(`-j -`). Its paths are relative to the current directory. The payload
layers over `rok-sqlc.toml` and doesn't turn discovery off.
```json
{
"schema": ["migrations/"],
"output": "src/db",
"derive": ["serde"],
"conventions": { "preset": "rok", "version_column": "" }
}
```
### Flags
The command is `generate`, or `g` for short (`rok-sqlc g`). The `help`
command prints the help (`rok-sqlc help generate`).
| `-s`, `--schema <PATH>` | A schema file, or a directory of migrations read in filename order (`*.down.sql` skipped). Repeat it to combine inputs. |
| `-q`, `--queries <PATH>` | A query file (`db/queries.rs`) or a directory (`db/queries/<stem>.rs`). Repeatable. |
| `-o`, `--output <DIR>` | The output directory. Default: `src/db`. |
| `-r`, `--resource <TABLE>` | Generate only this table (SQL or module name). Tables already generated in the output directory are regenerated too, so `db/mod.rs` stays complete. |
| `-c`, `--config <FILE>` | Read this config file instead of discovering `rok-sqlc.toml`. |
| `-j`, `--json <PAYLOAD>` | The JSON payload: inline, `@FILE`, or `-` for stdin. |
| `-d`, `--derive <NAME>` | Add a derive to generated types (`serde`). Repeatable. |
| `-n`, `--dry-run` | Print what would be written, removed or refused, and touch nothing. |
| `-k`, `--check` | Write nothing. List missing, changed and stale files, and exit 1 if there are any. |
| `-f`, `--force` | Overwrite existing files that rok-sqlc did not generate. |
| `-l`, `--lenient` | Skip unsupported tables and queries with warnings instead of failing. |
| `-h`, `--help` | Print help and exit 0. |
| `-V`, `--version` | Print the version and exit 0. |
`--dry-run`, `--check`, `--force` and `--lenient` are flags only, because
they describe a run rather than a project. A run with no schema (no
`--schema` and no `schema` key) is a usage error. Errors exit with code 1.
## Using it from `build.rs`
The library runs exactly what the CLI runs: `rok_sqlc::generate(&Config) ->
Result<Report>`. Add `rok-sqlc` under `[build-dependencies]`:
```rust
fn main() -> Result<(), Box<dyn Error>> {
println!("cargo::rerun-if-changed=migrations");
println!("cargo::rerun-if-changed=queries.sql");
// Paths are relative to the package root, where Cargo runs build scripts.
let mut config = rok_sqlc::Config::default();
config.schema = vec!["migrations".into()];
config.queries = vec!["queries.sql".into()];
config.output = "src/db".into();
match rok_sqlc::generate(&config) {
Ok(_report) => Ok(()),
Err(error) => {
// The report says which tables and queries failed, and where.
for failure in &error.report().table_failures {
println!("cargo::warning={failure}");
}
for failure in &error.report().query_failures {
println!("cargo::warning={failure}");
}
Err(error.into())
}
}
}
```
The library never reads `rok-sqlc.toml` and never prints: a build script gets
exactly the `Config` it built. Set `config.conventions`
(`rok_sqlc::Conventions::rok()`), `config.derive`
(`rok_sqlc::Derive::Serde`) and `config.relationships`
(`rok_sqlc::RelationshipChange`) to match the config file's sections.
`Config` is `#[non_exhaustive]`, so start from `Config::default()` and set
fields. The `Report` lists written, unchanged, refused and stale files and
any skipped tables or queries.
The stable library API is `generate`, `Config`, `Conventions`, `Derive`,
`RelationshipChange`, `Report` (with `RefusedFile`, `TableFailure` and
`QueryFailure`), and `Error`. Changing the *generated* API counts as a
breaking change too (see [ADR 0012](docs/adr/0012-library-api-and-stability.md)).
## CI with `--check`
`--check` regenerates in memory and compares the result with the files on
disk. It exits 1 and lists every missing, changed or stale file when they
differ, and it writes nothing:
```sh
rok-sqlc g --check
```
Run it in CI after installing rok-sqlc to catch migrations or queries that
were changed without regenerating.
Line endings don't count as drift. rok-sqlc reads migrations and query
files with CRLF as LF and writes `\n`, and it compares generated files the
same way. A checkout with CRLF line endings (Windows, `core.autocrlf`)
therefore generates the same code and passes `--check`.
## Supported SQL and type mapping
rok-sqlc reads PostgreSQL DDL with a SQL parser. It never connects to a
database.
| `SMALLINT`, `INT2`, `SMALLSERIAL` | `i16` |
| `INTEGER`, `INT`, `INT4`, `SERIAL` | `i32` |
| `BIGINT`, `INT8`, `BIGSERIAL` | `i64` |
| `REAL`, `FLOAT4` | `f32` |
| `DOUBLE PRECISION`, `FLOAT8`, `FLOAT` | `f64` |
| `BOOLEAN` | `bool` |
| `TEXT`, `VARCHAR`, `CHAR` | `String` (`&str` for parameters) |
| `UUID` | `uuid::Uuid` |
| `DATE` | `chrono::NaiveDate` |
| `TIME` | `chrono::NaiveTime` |
| `TIMESTAMP` | `chrono::NaiveDateTime` |
| `TIMESTAMPTZ` | `chrono::DateTime<chrono::Utc>` |
| `NUMERIC`, `DECIMAL` (at most 28 digits) | `rust_decimal::Decimal` |
| `JSON`, `JSONB` | `serde_json::Value` |
| `T[]` | `Vec<T>` |
| `CREATE TYPE … AS ENUM` | an enum in `db::types` |
| text column with `CHECK (col IN ('a', …))` | an enum in `db::types` (`InvoiceStatus`) |
A nullable column is wrapped in `Option`, and a primary key column uses its
typed id.
**Statements rok-sqlc reads:**
- `CREATE TABLE`: columns, `DEFAULT`, identity and `GENERATED` columns,
inline and table-level `PRIMARY KEY`, `UNIQUE`, `REFERENCES`/`FOREIGN KEY`,
and value-list `CHECK`s. Other `CHECK`s are ignored.
- `ALTER TABLE`: `ADD COLUMN`, `DROP COLUMN`, `RENAME COLUMN`,
`ADD CONSTRAINT` (primary key, unique, foreign key, value-list check;
other `CHECK`s are ignored, as in `CREATE TABLE`), and
`ENABLE`/`DISABLE ROW LEVEL SECURITY`.
- `CREATE UNIQUE INDEX` on plain columns, which becomes a unique key with a
lookup. A partial index over live rows (`WHERE deleted_at IS NULL`) counts
under the soft-delete convention. Expression indexes are ignored.
- `CREATE TYPE … AS ENUM`, and `ALTER TYPE … ADD VALUE`/`RENAME VALUE`.
- `DROP TABLE`, which removes the table, so its generated files become stale
and are removed. Foreign keys other tables hold to it go too, as with
`CASCADE`: those columns keep their raw type and lose the relationship.
- `DROP TYPE` of an enum. With `CASCADE`, the columns using it are dropped.
Without it, a table that still uses the type fails.
All other statements (functions, triggers, policies, views, grants, and so
on) are skipped without comment. Schema qualifiers are
kept (`catalog.products`), and `public.` is the same as no qualifier.
**What is rejected, and why.** Generation is strict by default
([ADR 0001](docs/adr/0001-strict-generation-by-default.md)): if a table or
query can't be generated correctly, the run writes nothing and exits 1, and
the error is reported as `path:line:`. A silently incomplete `db` module is a
worse failure than a loud one. `--lenient` skips the offending tables and
queries with warnings instead. If every table is unsupported, the run still
fails. With `--resource`, only that table's problems count.
| Column types not in the table above (`INTERVAL`, `BYTEA`, `TIME WITH TIME ZONE`, network types, …) | There is no chosen Rust mapping yet, and rok-sqlc doesn't guess one. |
| `NUMERIC(p, s)` with `p` > 28 | `rust_decimal` holds 28 significant digits, so the value would fail to decode at runtime. |
| Other `ALTER TABLE` clauses (`ALTER COLUMN … TYPE`/`SET NOT NULL`, `RENAME TO`, …) | The table's shape after the change is unknown, so the table fails. |
| Other `ALTER TYPE` clauses | The tables that use the type fail. |
| A table whose module name is reserved or already taken | Its module would collide with a generated one. |
| Columns whose filter or sort-key names collide (`weight` next to `weight_is_null`) | The generated code wouldn't compile. |
| Two relationships of one table with the same name, or a relationship named like a filter field | Rename one in `[relationships]`. |
| A soft-delete column that is not a nullable timestamp | `delete` couldn't record the time. |
| A query placeholder or result expression rok-sqlc can't type | Add a `-- param:` or `-- column:` override. |
| Unknown tables or columns in a query, more than one statement, `WITH RECURSIVE`, data-modifying CTEs | They can't be analyzed (yet). |
| An `ON CONFLICT (cols)` target that is not a unique key | It would fail at runtime. The error lists the table's unique keys. |
| An `ON CONFLICT ON CONSTRAINT name` that names no primary key or unique constraint | It would fail at runtime. The error lists the table's constraint names. |
## Limitations and roadmap
**Known limitations in 0.1:**
- `INTERVAL`, `BYTEA`, `TIME WITH TIME ZONE` and other unmapped types are
unsupported, in tables and in query casts (`$1::interval`).
- Casts to enum types in query files (`$1::product_status`) are rejected.
Compare against the enum column instead (`status = $1`); that infers
`types::ProductStatus`.
- Partial-index `ON CONFLICT` targets are rejected, including the
soft-delete convention's `WHERE deleted_at IS NULL` unique indexes,
because PostgreSQL infers a partial index only from a target that repeats
its condition, which the query parser can't read.
- Filters and search types don't get serde derives.
- A serde-deserialized update patch can't set a column to `NULL`: `None` and
`Some(None)` both serialize to `null`.
- Relationship filters nest at most `MAX_RELATIONSHIP_DEPTH` (4) deep. Deeper
filters make `search` fail with `sqlx::Error::InvalidArgument`.
- Keyset pagination (`search_after`) follows primary-key order only. Array
and JSON columns get only null checks in filters.
- `WITH RECURSIVE` and data-modifying CTEs are rejected in query files.
- `ALTER COLUMN` changes fail the table.
- Typed ids and relationships cover only the tables generated together. A
foreign key into another migration set keeps its raw type and makes no
relationship.
- A versioned `update` of a row that a concurrent transaction deletes while
the `UPDATE` waits for its lock reports `VersionConflict`. A retry reports
`NotFound`.
**Not planned for 0.1** (possible later):
- Full-text search, pgvector, and embedding persistence.
- A compile-time-checked `query!` mode.
- Declared has-many-through and polymorphic relationships, beyond renaming
and opting out of inferred ones.
- Nested writes (creating a row together with its children).
- Machine-readable output (`--format json`).
- Prebuilt binaries (cargo-dist).
- SQLx versions other than 0.9, a user-facing module-directory input layout,
and databases other than PostgreSQL.
- JSONB operators in typed filters.
## Contributing
```sh
cargo test # unit, CLI, and generated-code suites
cargo clippy --all-targets -- -D warnings
cargo fmt --all -- --check
```
- **Two toolchains.** The tool builds on Rust 1.88 (so no `if let` guards in
`match`, for example). The suites that compile generated code
(`tests/fixture_compile.rs`, `tests/module_fixtures.rs`, and
`the_examples_compile` in `tests/readme.rs`) need Rust 1.94. Below that,
they print a skip notice and pass. CI runs 1.88, 1.94 and stable.
- **PostgreSQL suite.** Set `ROK_SQLC_TEST_DATABASE_URL` to a throwaway
database (for example `postgres://postgres:postgres@localhost:5432/postgres`)
to run the generated code in `tests/fixture-app` against PostgreSQL and to
smoke-run the module fixtures in `modules/`. The module run creates
databases, so the role needs `CREATEDB`. Without the variable, those tests
print "skipping".
- **This README.** `tests/readme.rs` checks that every flag, alias and config
key in the code appears here (and nothing else does), that every config
sample is accepted, and that every code sample is copied verbatim from
[`docs/examples/`](docs/examples/). It also checks that those examples have
no drift and that they compile. After changing the generator, run
`rok-sqlc g` in each example directory and update the samples here.
- **Decisions** are recorded as ADRs in [`docs/adr/`](docs/adr/). Read the
ones for the area you touch, and add one for a decision that is hard to
reverse. [`docs/design.md`](docs/design.md) is the original design
walkthrough, kept as historical background.
- **Terms** follow [`GLOSSARY.md`](GLOSSARY.md): table vs. resource, query
file, return mode, query override, convention, and so on.
- **Changes** go in [`CHANGELOG.md`](CHANGELOG.md). Changes to the generated
API count as breaking.
## License
Licensed under either of [Apache License, Version 2.0](LICENSE-APACHE) or
[MIT license](LICENSE-MIT), at your option.