Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion .github/pull_request_template.md
Original file line number Diff line number Diff line change
Expand Up @@ -5,4 +5,4 @@
- [ ] `examples/` is regenerated (`rake sqlc_generate_process`) and committed
- [ ] Tests added or updated (`rake test`, and `rake end2end_*` when the generated code changes)
- [ ] The generated code stays RuboCop-clean (`rake rubocop`) and Steep-clean (`rake steep`)
- [ ] Labelled (`feature`, `bug`, `breaking`, `documentation`) so the release notes are grouped correctly
- [ ] Labelled (`bug`, `enhancement`, `documentation`, or `breaking`) so the release notes are grouped correctly
2 changes: 1 addition & 1 deletion CLAUDE.md
Original file line number Diff line number Diff line change
Expand Up @@ -36,7 +36,7 @@ UPDATE_GOLDEN=1 bundle exec ruby -Itest test/extended_golden_test.rb # rewrite
relative to the config file.

## Git, CI and releases
* Develop on a feature branch, small commits; PRs target `main`. Label PRs (`feature`, `bug`, `breaking`, `documentation`).
* Develop on a feature branch, small commits; PRs target `main`. Label PRs (`bug`, `enhancement`, `documentation`; `breaking` for incompatible changes, see `.github/release.yml`).
* CI: `build.yml` (WASM, lint, `sqlc diff`, Steep, option matrix) and `tests.yml` (unit, Ruby 3.1/3.2/3.4, E2E on real
PostgreSQL/MySQL/SQLite). Both must be green.
* **Never create tags or releases by hand and never add release markers to commit messages.** Releases are started by a
Expand Down
335 changes: 157 additions & 178 deletions README.md

Large diffs are not rendered by default.

4 changes: 2 additions & 2 deletions Steepfile
Original file line number Diff line number Diff line change
Expand Up @@ -2,6 +2,6 @@ target :gen do
check "examples"
signature "examples"

# stdlib signatures for the types the generated code refers to (BigDecimal, Date, Time, IPAddr, Tempfile; Set is core since RBS 4)
library "bigdecimal", "date", "time", "ipaddr", "tempfile"
# stdlib signatures for the types the generated code refers to (BigDecimal, Date, Time, IPAddr, Tempfile, Monitor; Set is core since RBS 4)
library "bigdecimal", "date", "time", "ipaddr", "tempfile", "monitor"
end
12 changes: 8 additions & 4 deletions docs/Architecture.md
Original file line number Diff line number Diff line change
Expand Up @@ -21,14 +21,14 @@ stdout ◄─ Proto (encode) ◄─ GenerateResponse (query_sql.rb, query_sql.rb
| `exe/plugin.rb` | entry point. Errors go to stderr with exit code 1 (sqlc shows them); `SQLC_GEN_RUBY_DEBUG=1` adds a backtrace |
| `proto.rb`, `plugin_pb.rb` | a ~150 line protobuf wire codec and the subset of sqlc's `codegen.proto` we read. Field numbers must match upstream. `google-protobuf` is a C extension and cannot run inside ruby.wasm |
| `options.rb`, `ruby_version.rb` | parse + validate the `options:` JSON (`driver`, `rubyVersion`, `overrides`, `rename`, ...; unknown keys are errors) |
| `generator.rb` | the orchestrator: one file per query file (`query_sql.rb`), plus `models.rb`, `connection.rb`, `Gemfile`; builds the module / class / `Data` skeleton |
| `method_gen.rb` | the code of one query: signature (keyword arguments), SQL constant, command (`:one`, `:many`, `:exec`, `:execrows`, `:execlastid`, `:copyfrom`), `sqlc.slice`, bang finder, and the RBS signature |
| `generator.rb` | the orchestrator: one file per query file (`query_sql.rb`), plus `models.rb`, `connection.rb` (per-connection state and helpers the query classes share), `Gemfile`; builds the module / class / `Data` skeleton |
| `method_gen.rb` | the code of one query: signature (keyword arguments), SQL constant, command (`:one`, `:many`, `:exec`, `:execrows`, `:execlastid`, `:execresult`, `:batchexec` / `:batchone` / `:batchmany`, `:copyfrom`), `sqlc.slice`, bang finder, and the RBS signature |
| `row_layout.rb` | turns result columns into row attributes; `sqlc.embed` collapses a table's columns into one model attribute |
| `type_resolver.rb` | RBS type of a column / parameter: driver type table, arrays, nullability (`T?`), `overrides:` |
| `type_resolver.rb` | RBS type of a column / parameter: driver type table, arrays, nullability (`T?`), `overrides:` (a `db_type` override types array elements too) |
| `catalog.rb` | the schema's tables and enums, and their Ruby names |
| `drivers/base.rb` | the driver interface and what all drivers share: `instrument` logging, `NotFound`, transaction RBS |
| `drivers/{pg,mysql2,sqlite3}.rb` | the per-library parts: type mapping, connecting / pooling, preparing and executing statements, transactions, `:copyfrom` |
| `code.rb` | text helpers: indentation, `snake_case`, `camelize`, Ruby-keyword-safe parameter names |
| `code.rb` | text helpers: indentation, `snake_case`, `camelize`, Ruby-keyword-safe parameter names, `rubocop:disable` pairs for schema names that break a naming cop |

**Rule of thumb:** `MethodGen` / `Generator` decide *what* is generated and are the same for every database; a driver only
supplies *snippets* (how to prepare a statement, how to read the affected rows, ...). If you find a `case driver` in
Expand Down Expand Up @@ -67,6 +67,10 @@ Design decisions worth knowing (the *why* is in the comments of `drivers/base.rb
* **Pool injection.** `QuerySql.new(pool)` accepts anything that responds to `#with`, so an app can share its pool
and tests can pass a fake. `connect(...)` is only a convenience that builds a `connection_pool`.
* **Statements are closed.** SQLite/MySQL statements are closed in `ensure` (an open SQLite statement blocks `COMMIT`).
* **Parameters are encoded by the driver.** `Driver#encode_param` (e.g. `bytea`, `json` and arrays on `pg`) is used by
every command, batches included; add a type's encoding there, not in `MethodGen`.
* **`:copyfrom` is all or nothing** on every driver (a savepoint / transaction around the load), and MySQL's
`LOAD DATA` warnings (skipped duplicates, coerced values) are raised as errors.
* **Nothing from the plugin leaks into the app.** The generated code only requires the driver gem and `connection_pool`.

## Adding things
Expand Down
36 changes: 14 additions & 22 deletions docs/Design.md
Original file line number Diff line number Diff line change
Expand Up @@ -4,30 +4,22 @@ sqlc plugins receive a protobuf `GenerateRequest` on stdin and answer with a `Ge
process or as a WASI module. This plugin is pure Ruby and is distributed as a WASI module (`plugin.wasm`).

## Source layout

| File | Role |
|---|---|
| `exe/plugin.rb` | entry point (stdin → `Generator` → stdout) |
| `lib/sqlc_gen_ruby/proto.rb` | ~150 line protobuf wire codec. `google-protobuf` is a C extension and cannot be loaded inside ruby.wasm, so the plugin brings its own |
| `lib/sqlc_gen_ruby/plugin_pb.rb` | the subset of sqlc's `codegen.proto` the generator needs (field numbers must match upstream) |
| `lib/sqlc_gen_ruby/options.rb`, `ruby_version.rb` | the `options:` JSON, `rubyVersion` parsing / validation (3.1 – 3.4; `Struct` below 3.2, `Data` from 3.2) |
| `lib/sqlc_gen_ruby/generator.rb` | module / class / dataclass (`Data.define`, `Struct.new`) layout, `.rb` / `.rbs` / `Gemfile` files, `models.rb` (tables + enums) |
| `lib/sqlc_gen_ruby/method_gen.rb` | one generated method per query: `:one`, `:many`, `:exec`, `:execrows`, `:execlastid`, `:copyfrom`, `sqlc.slice` |
| `lib/sqlc_gen_ruby/row_layout.rb` | how result columns become `<Query>Row` attributes (`sqlc.embed` expands a table into one model attribute) |
| `lib/sqlc_gen_ruby/type_resolver.rb`, `catalog.rb` | RBS type of a column (DB type mapping + array + nullability + `overrides:`), tables / enums of the catalog |
| `lib/sqlc_gen_ruby/drivers/*.rb` | `Base` + `Pg`, `Mysql2`, `Sqlite3`: column type mapping, statement preparation / execution, pooling, row access |
| `lib/sqlc_gen_ruby/code.rb` | indent / snake_case helpers |

Driver differences are data, not copy/paste: `Base` has `pooling_enabled?` (ConnectionPool vs a single injected
connection), `row_data_type` (`row['col']` vs `row[0]`), `supports_exec_last_id?` (mysql2 only) and a column mapping
table (exact or prefix based matching).
What each file in `lib/sqlc_gen_ruby/` does is in [Architecture.md](Architecture.md). Two constraints shape it:
* `google-protobuf` is a C extension and cannot be loaded inside ruby.wasm, so the plugin brings its own ~150 line
protobuf codec (`proto.rb`) and the subset of sqlc's `codegen.proto` it reads (`plugin_pb.rb`, field numbers must
match upstream).
* Driver differences are data, not copy/paste: `Drivers::Base` defines hooks (how to prepare and run a statement, how
rows are read - `row_data_type` `:hash` for `row['col']` vs `:array` for `row[0]` -, how `:execlastid` /
`:execresult` / `:copyfrom` / transactions work) and a column mapping table; `Pg`, `Mysql2` and `Sqlite3` fill them in.

## The WASM build (`scripts/wasm/build.sh`)

1. downloads the *prebuilt* `ruby-3.3-wasm32-unknown-wasip1-full` release from ruby/ruby.wasm (this avoids compiling
CRuby with a wasi-sdk);
2. strips the ~18 MB of DWARF debug info / name section from the interpreter (`strip_wasm.rb`);
3. stages a slim stdlib (the plugin only uses core + `json`) + `lib/` + `exe/`;
3. stages a slim stdlib (the plugin only uses core + `json`) + `lib/` + `exe/`, plus an empty
`syntax_suggest/core_ext.rb` (the interpreter loads it to format errors and would otherwise print a warning before
the plugin's error message);
4. embeds that filesystem into the interpreter with `rbwasm pack` (wasi-vfs), because sqlc gives plugins no filesystem.

**The argv trick:** sqlc runs the module as `plugin.wasm /plugin.CodegenService/Generate`, so the interpreter treats that
Expand All @@ -52,7 +44,7 @@ transactions, nullability, `overrides`) and Ruby conventions (bang finders, logg
now idiomatic Ruby rather than a copy of the C# one.

## Known limitations
* The SQLite `NUMERIC` affinity can return Integers where `Float` is declared.
* `:copyfrom` for MySQL writes binary (`BLOB`) values through a text CSV; only text-safe values round-trip.
* The generated query classes are not thread-safe for SQLite (a single connection is injected).
* `3.3.x` (a patch version) is rejected as `rubyVersion`; use `3.3`.
See [Limitations in the README](../README.md#limitations). Design choices that look like limitations:
* Overrides only change the RBS types; values are never converted, so the generated code stays a thin layer over the driver.
* SQLite queries do not overlap: a `SQLite3::Database` is one connection, so the generated code takes one lock per
database (held for a whole transaction) to keep threads and fibers from mixing their transactions.
3 changes: 2 additions & 1 deletion docs/Releasing.md
Original file line number Diff line number Diff line change
Expand Up @@ -39,7 +39,8 @@ code and the options**, because that is what users depend on:
| minor | new options, new query features, new drivers; output that only adds API (e.g. a new method) |
| major | existing generated method signatures, constants or options change incompatibly |

Label pull requests (`breaking`, `feature`, `bug`, `documentation`; `skip-changelog` to hide one) so the generated notes
Label pull requests `bug`, `enhancement` or `documentation` (`breaking` for incompatible changes, `skip-changelog` to
hide one; create those two labels the first time they are needed) so the generated notes
are grouped sensibly, and pick the bump from the labels of what is merged since the last release.

## Using a release
Expand Down
9 changes: 6 additions & 3 deletions docs/Testing.md
Original file line number Diff line number Diff line change
Expand Up @@ -28,10 +28,13 @@ They `require` the generated code from `examples/` and run it against a real dat
`rbs_types.rb` additionally checks that returned values match the generated `.rbs` types.
* SQLite needs nothing: `rake end2end_sqlite3`.
* PostgreSQL / MySQL: provide a server, load `examples/authors/<db>/schema.sql`, and set `DB_NAME DB_USER DB_PASS
POSTGRES_HOST MYSQL_HOST` (optionally `*_PORT`). MySQL needs `local_infile=1` for `:copyfrom`.
POSTGRES_HOST MYSQL_HOST` (optionally `*_PORT`). MySQL needs `local_infile=1` for `:copyfrom`, and the MySQL tests
connect without a password (`DB_PASS` is only used for PostgreSQL), so use a user without one.
Or run everything in Docker: `rake run_end2end_tests` (docker-compose).
* They cover every query command, transactions (nested, isolation), pool injection, logging, bang finders, `copyfrom`
with awkward values, Fiber-scheduler concurrency (`async` gem) and statement-leak checks.
* They cover every query command (batches with encoded `bytea` / `json` values too), transactions (nested,
isolation, `break` / `throw`, a rescued failed statement on PostgreSQL, SQLite threads sharing a database), pool
injection, logging, bang finders, `:copyfrom` with awkward and binary values and its all-or-nothing behaviour,
Fiber-scheduler concurrency (`async` gem) and statement-leak checks.

## CI workflows (`.github/workflows/`)
| Workflow | When | What |
Expand Down
6 changes: 6 additions & 0 deletions examples/authors/mysql/query.sql
Original file line number Diff line number Diff line change
Expand Up @@ -110,5 +110,11 @@ SELECT id, `order`, note FROM regressions WHERE id IN (?, ?, ?) ORDER BY id;
-- name: CopyRegressions :copyfrom
INSERT INTO regressions (`order`, note) VALUES (?, ?);

-- name: CopyRegressionRows :copyfrom
INSERT INTO regressions (id, `order`, note, payload) VALUES (?, ?, ?, ?);

-- name: ListRegressionPayloads :many
SELECT id, payload FROM regressions ORDER BY id;

-- name: TruncateRegressions :exec
TRUNCATE TABLE regressions;
3 changes: 2 additions & 1 deletion examples/authors/mysql/schema.sql
Original file line number Diff line number Diff line change
Expand Up @@ -67,5 +67,6 @@ CREATE TABLE extended_bios (
CREATE TABLE regressions (
id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
`order` varchar(255) NOT NULL,
note text
note text,
payload varbinary(16)
);
3 changes: 3 additions & 0 deletions examples/authors/postgresql/query.sql
Original file line number Diff line number Diff line change
Expand Up @@ -155,6 +155,9 @@ INSERT INTO regressions (id, "order") VALUES ($1, $2) ON CONFLICT (id) DO NOTHIN
-- name: CopyRegressions :copyfrom
INSERT INTO regressions (id, "order", payload, doc) VALUES ($1, $2, $3, $4);

-- name: CreateRegressionsBatch :batchexec
INSERT INTO regressions (id, "order", payload, doc) VALUES ($1, $2, $3, $4);

-- name: ListRegressions :many
SELECT * FROM regressions WHERE (sqlc.narg(order_name)::text IS NULL OR "order" = sqlc.narg(order_name)) ORDER BY id;

Expand Down
2 changes: 1 addition & 1 deletion examples/authors/sqlite/query.sql
Original file line number Diff line number Diff line change
Expand Up @@ -87,7 +87,7 @@ SELECT * FROM authors WHERE id = ?;
SELECT * FROM authors WHERE name = ? ORDER BY id;

-- name: InsertDeclaredTypes :exec
INSERT INTO declared_types (c_varchar, c_decimal, c_unsigned_big_int, c_native, c_double) VALUES (?, ?, ?, ?, ?);
INSERT INTO declared_types (c_varchar, c_decimal, c_unsigned_big_int, c_native, c_double, c_json, c_uuid) VALUES (?, ?, ?, ?, ?, ?, ?);

-- name: GetDeclaredTypes :one
SELECT * FROM declared_types LIMIT 1;
Expand Down
4 changes: 3 additions & 1 deletion examples/authors/sqlite/schema.sql
Original file line number Diff line number Diff line change
Expand Up @@ -18,7 +18,9 @@ CREATE TABLE declared_types (
c_decimal DECIMAL(10, 5),
c_unsigned_big_int UNSIGNED BIG INT,
c_native NATIVE CHARACTER(70),
c_double DOUBLE PRECISION
c_double DOUBLE PRECISION,
c_json JSON,
c_uuid UUID
);

CREATE TABLE regressions (
Expand Down
2 changes: 1 addition & 1 deletion examples/mysql2/models.rb
Original file line number Diff line number Diff line change
Expand Up @@ -67,6 +67,6 @@ class Book < Data.define(:id, :name, :author_id, :description)
class ExtendedBio < Data.define(:author_name, :name, :bio_type, :author_type)
end

class Regression < Data.define(:id, :order, :note)
class Regression < Data.define(:id, :order, :note, :payload)
end
end
3 changes: 2 additions & 1 deletion examples/mysql2/models.rbs
Original file line number Diff line number Diff line change
Expand Up @@ -120,6 +120,7 @@ module Mysql2Codegen
attr_reader id: Integer
attr_reader order: String
attr_reader note: String?
def initialize: ( Integer id, String order, String? note) -> void
attr_reader payload: String?
def initialize: ( Integer id, String order, String? note, String? payload) -> void
end
end
65 changes: 63 additions & 2 deletions examples/mysql2/query_sql.rb
Original file line number Diff line number Diff line change
Expand Up @@ -235,6 +235,16 @@ class ListRegressionsByIdsRow < Data.define(:id, :order, :note)
class CopyRegressionsArgs < Data.define(:order, :note)
end

class CopyRegressionRowsArgs < Data.define(:id, :order, :note, :payload)
end

LIST_REGRESSION_PAYLOADS_SQL = <<~'SQL'
SELECT id, payload FROM regressions ORDER BY id
SQL

class ListRegressionPayloadsRow < Data.define(:id, :payload)
end

TRUNCATE_REGRESSIONS_SQL = <<~'SQL'
TRUNCATE TABLE regressions
SQL
Expand Down Expand Up @@ -523,7 +533,7 @@ def copy_authors(rows)
file.write(csv_line([row.name, row.bio]))
end
file.flush
client.query("LOAD DATA LOCAL INFILE '#{client.escape(file.path)}' INTO TABLE `authors` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n' (`name`, `bio`)")
load_data(client, "LOAD DATA LOCAL INFILE '#{client.escape(file.path)}' INTO TABLE `authors` CHARACTER SET binary FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n' (`name`, `bio`)")
end
nil
end
Expand Down Expand Up @@ -748,12 +758,41 @@ def copy_regressions(rows)
file.write(csv_line([row.order, row.note]))
end
file.flush
client.query("LOAD DATA LOCAL INFILE '#{client.escape(file.path)}' INTO TABLE `regressions` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n' (`order`, `note`)")
load_data(client, "LOAD DATA LOCAL INFILE '#{client.escape(file.path)}' INTO TABLE `regressions` CHARACTER SET binary FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n' (`order`, `note`)")
end
nil
end
end

def copy_regression_rows(rows)
with_client('copy_regression_rows') do |client|
return if rows.empty?

Tempfile.create(['copy_regression_rows', '.csv']) do |file|
rows.each do |row|
file.write(csv_line([row.id, row.order, row.note, row.payload]))
end
file.flush
load_data(client, "LOAD DATA LOCAL INFILE '#{client.escape(file.path)}' INTO TABLE `regressions` CHARACTER SET binary FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\\' LINES TERMINATED BY '\\n' (`id`, `order`, `note`, `payload`)")
end
nil
end
end

def list_regression_payloads
with_client('list_regression_payloads') do |client|
stmt = client.prepare(LIST_REGRESSION_PAYLOADS_SQL)
begin
result = stmt.execute(as: :array)
result.map do |row|
ListRegressionPayloadsRow.new(row[0], row[1])
end
ensure
stmt.close
end
end
end

def truncate_regressions
with_client('truncate_regressions') do |client|
stmt = client.prepare(TRUNCATE_REGRESSIONS_SQL)
Expand Down Expand Up @@ -803,6 +842,28 @@ def csv_line(values)
"#{fields.join(',')}\n"
end

# Runs LOAD DATA in a transaction (a savepoint inside #transaction): all rows or none, and an error instead
# of the warnings LOAD DATA LOCAL gives for duplicate keys and invalid values.
def load_data(client, sql)
outermost = !client.in_transaction
client.query(outermost ? 'BEGIN' : 'SAVEPOINT sqlc_copy')
done = false
begin
client.query(sql)
unless client.warning_count.zero?
warnings = client.query('SHOW WARNINGS', as: :array).reject { |level, *| level == 'Note' }
raise Mysql2::Error, "LOAD DATA: #{warnings.map { |w| w[2] }.join('; ')}" unless warnings.empty?
end
done = true
ensure
if outermost
client.query(done ? 'COMMIT' : 'ROLLBACK')
else
client.query(done ? 'RELEASE SAVEPOINT sqlc_copy' : 'ROLLBACK TO SAVEPOINT sqlc_copy')
end
end
end

def slice_placeholders(values)
values.empty? ? 'NULL' : Array.new(values.size, '?').join(', ')
end
Expand Down
22 changes: 22 additions & 0 deletions examples/mysql2/query_sql.rbs
Original file line number Diff line number Diff line change
Expand Up @@ -255,6 +255,22 @@ module Mysql2Codegen
def initialize: ( String order, String? note) -> void
end

class CopyRegressionRowsArgs
attr_reader id: Integer
attr_reader order: String
attr_reader note: String?
attr_reader payload: String?
def initialize: ( Integer id, String order, String? note, String? payload) -> void
end

LIST_REGRESSION_PAYLOADS_SQL: String

class ListRegressionPayloadsRow
attr_reader id: Integer
attr_reader payload: String?
def initialize: ( Integer id, String? payload) -> void
end

TRUNCATE_REGRESSIONS_SQL: String

class QuerySql
Expand Down Expand Up @@ -337,6 +353,10 @@ module Mysql2Codegen

def copy_regressions: (Array[CopyRegressionsArgs] rows) -> void

def copy_regression_rows: (Array[CopyRegressionRowsArgs] rows) -> void

def list_regression_payloads: -> Array[ListRegressionPayloadsRow]

def truncate_regressions: -> void

private
Expand All @@ -347,6 +367,8 @@ module Mysql2Codegen

def csv_line: (Array[untyped] values) -> String

def load_data: (untyped client, String sql) -> void

def slice_placeholders: (Array[untyped] values) -> String
end
end
Loading
Loading