sql-plugin.md

SQL plugin

Python SQL @sqlJson columns support lists and string-keyed maps, including structure elements and nullable collections. SQLite stores JSON text; PostgreSQL binds collections through Psycopg's native Jsonb adapter. An absent nullable collection remains SQL NULL rather than a JSON null document. Generated integration tests exercise collection insert/read/update round trips.

PostgreSQL date-time Timestamp members are stored as TIMESTAMPTZ: Smithy timestamps represent instants, not local wall-clock times. PostgreSQL returns timezone-aware datetimes and changes only their displayed offset when a session time zone changes. Epoch-seconds storage remains numeric. When upgrading a legacy TIMESTAMP column, use an explicit conversion with the actual historical storage zone (for example USING occurred_at AT TIME ZONE 'UTC' only when old values were stored in UTC); do not rely on the migration session's default zone.

Generated Python PostgreSQL integration tests can use an existing test server through SMITHPLATES_TEST_POSTGRES_DSN. The role must be allowed to create and drop databases: each test module owns a uniquely named disposable database and never migrates the database in the supplied DSN. When the variable is omitted, the fixture starts a disposable PostgreSQL Testcontainers server instead. This lets CI service containers run tests without requiring access to Docker's published host ports. Use a dedicated test server, not a production DSN.

Maven coordinate: com.jacoby6000:smithplates-plugin:<version> (from sbtn print smithplatesPlugin/version after publishM2, or a published release/snapshot coordinate)

Smithy build plugin (smithplates) and trait namespace for relational schema and repository codegen from Smithy models.

Codegen pipeline

The plugin extracts SQL IR (tables, relationships, derived DML) from the Smithy model. SQL IR feeds schema and migrations directly. SQL database service codegen lowers shared shapes into the language-neutral model set, expands the language's outputs.json deck with CodegenPlanner, and renders Scalate SSP templates through SqlNeutralServiceTemplateAttributes.

Path	smithy-build.json config	Generated artifacts
Schema and migrations	smithplates.<language>.sql.<dialect> with enable: true	Versioned migration .sql files under migrationLocation (initial v1_initial_schema.sql is full schema DDL) and generated per-dialect migration services (Python today)
SQL database service codegen	smithplates.<language>.sql	Target-language query models, repository interfaces, dialect-specific implementations, and derived-query integration tests

See Architecture for the full pipeline diagram and implementation mapping. Settings live in Configuration; this page focuses on modeling and generated SQL behavior.

Trait cheat sheet

Trait / shape	Apply to	Purpose
@sqlTable	structure	Marks a persistence table
@sqlPrimaryKey	member	Primary key column
@sqlAutoUuid	string member	DB-generated UUID PK (omit from inserts)
@sqlAutoIncrement	Integer member	Serial PK (SQLite AUTOINCREMENT / Postgres IDENTITY; omit from inserts)
@sqlForeignKey	member	FK to another @sqlTable
@sqlIndex / @sqlUniqueIndex	member	Secondary / unique indexes (unique FK → one-to-one)
@sqlColumn / @sqlVarchar / @sqlUuid / @sqlJson	member (or type)	Column naming and SQL types
@sqlCreatedTimestamp / @sqlUpdatedTimestamp	member	Managed timestamps (omit from insert/update inputs)
DerivedStruct	operation I/O	Sentinel expanded by derive traits
@sqlDeriveInsert / Update / Delete / SelectOne / Select	operation	Generate DML from the target table
@sqlService	service	Repository service with a flat operations list

Full trait tables and SPI details: modules/smithplates-plugin/README.md.

smithplates SQL outputs

Config	Output
Enabled dialects (sqlite, postgres)	Versioned migration .sql files under migrationLocation (SQL IR → dialect DDL); initial v1_initial_schema.sql contains full schema DDL (CREATE TABLE, indexes, enums, foreign-key constraints)
Language sql targets	Scalate SSP-rendered query models, Protocol interfaces, dialect-specific implementations, and derived-query test suites per @sqlService (service IR + SQL IR + planner decks + templates)

Trait definitions ship inside the plugin JAR: schema traits at META-INF/smithy/smithplates.codegen.sql.smithy (smithplates-sql-ir) and query/service traits at META-INF/smithy/smithplates.codegen.sql.service.smithy (smithplates-sql-service-ir). Typed Java trait classes register via TraitService SPI: schema traits under com.jacoby6000.smithplates.sql.traits (smithplates-sql-ir) and query/service traits under com.jacoby6000.smithplates.sql.service.traits (smithplates-sql-service-ir).

Modeling conventions

• Annotate structures with @sqlTable; put @sqlPrimaryKey, @sqlForeignKey, @sqlIndex, and column traits on members.
• Use @sqlAutoIncrement on an Integer primary-key member for database-generated serial columns (INTEGER PRIMARY KEY AUTOINCREMENT on SQLite, GENERATED ALWAYS AS IDENTITY on Postgres). Auto-increment columns are omitted from derived insert inputs; they must still appear on @sqlUpdate structures when they identify the row.
• Use @sqlAutoUuid for database-generated UUID primary keys (implies @sqlUuid; same insert/update rules as auto-increment).
• Use a dedicated SQL namespace (for example example.db) separate from HTTP API namespaces. Do not share shapes with @httpService models; see Integration — HTTP and SQL model separation.
• Use flat operations lists on @sqlService services, not Smithy resources (resource properties cannot carry SQL member traits).
• Derive DML with @sqlDeriveInsert, @sqlDeriveUpdate, @sqlDeriveDelete, @sqlDeriveSelectOne, or @sqlDeriveSelect on operations; use DerivedStruct as derive input (and derive-select output). @sqlDeriveInsert rejects target tables that participate in a cycle made entirely of @required foreign-key members, because safely inserting those rows requires deferred constraint evaluation. Cycles with at least one optional FK remain derivable.
• @sqlDeriveSelectOne accepts optional joins; when present, output must be DerivedStruct and codegen expands nested joined table structures from @sqlForeignKey cardinality (singular member for many-to-one/one-to-one, list member for one-to-many). Singular nested members are optional when the joining FK member is optional in Smithy, required when the FK member is @required. Each join after the first resolves its ON clause from the nearest prior joined table when no direct FK exists on the target table.
• Bind repository SQL to service methods by matching operation shape ids on derive traits.

Quick example

use smithplates.codegen.sql#DerivedStruct
use smithplates.codegen.sql#sqlForeignKey
use smithplates.codegen.sql#sqlPrimaryKey
use smithplates.codegen.sql#sqlTable

@sqlTable(name: "foos")
structure Foo {
    @sqlPrimaryKey
    id: String
    @sqlForeignKey(references: "example#Bar")
    bar_id: String
    name: String
}

@sqlDeriveInsert(targetTable: "example#Foo")
operation CreateFoo {
    input: DerivedStruct
    output: CreateFooOutput
}

structure CreateFooOutput {
    @required
    id: String
}

@sqlService
service FooRepository {
    version: "1"
    operations: [CreateFoo]
}

SQL database service codegen

SQL database service codegen combines service IR, SQL IR-derived queries, and Scalate SSP templates into target-language artifacts. Bundled Python templates live under templates/python/src/db/ with artifact selection from templates/python/src/db/outputs.json. Generated SQL models use dataclasses with explicit JSON mapping helpers (HTTP models use Pydantic — see HTTP plugin). Configure smithplates.<language>.sql in smithy-build.json (see Configuration); bundled artifacts are selected from enabled dialects via the deck's variants keys (sqlite, postgres).

Paths are namespace-aware: artifacts land under <sourceOutputDir>/<smithy namespace path>/… (and the matching test root). Placeholders such as {{smithyNamespaceDir}} and {{serviceModuleName}} come from the language naming strategy. See Configuration — Namespace-aware layout.

Artifact (relative to sourceOutputDir / testOutputDir)	Pipeline stage	Deck id / notes
{{smithyNamespaceDir}}/models/{{serviceModuleName}}_models.py	Target language query models	python.sql.db.models
{{smithyNamespaceDir}}/{{enumFileName}}.py	Python StrEnum / IntEnum for referenced Smithy enum / intEnum shapes	Scala side path (string_enum / int_enum templates — not listed in outputs.json)
{{smithyNamespaceDir}}/{{serviceModuleName}}_protocol.py	Target language interfaces	python.sql.db.service_protocol
{{smithyNamespaceDir}}/<dialect>/{{serviceModuleName}}_<driver>.py	Dialect-specific implementations	python.sql.db.sqlite.service / python.sql.db.postgres.service
{{smithyNamespaceDir}}/<dialect>/<dialect or driver>_migrations.py	Dialect migration service: reads ordered v<number>*.sql files, creates _smithplates_migrations, validates schema hash, applies pending migrations	python.sql.db.sqlite.migrations_service / python.sql.db.postgres.migrations_service
{{smithyNamespaceDir}}/<dialect>/*_transaction_run.py	Shared transaction helpers (copied verbatim, non-.ssp)	python.sql.db.sqlite.transaction_run / python.sql.db.postgres.transaction_run
{{smithyNamespaceDir}}/<dialect>/test_{{serviceModuleName}}_derived_sql.py	Derived-query integration tests	python.sql.db.*.integration_tests under testOutputDir
conftest.py	Shared pytest bootstrap under testOutputDir	python.sql.db.tests.conftest
{{smithyNamespaceDir}}/postgres/stubs/testcontainers/postgres.pyi	Bundled mypy stubs for testcontainers.postgres.PostgresContainer	python.sql.db.postgres.testcontainers_stub (add that stubs dir to mypy_path)

Layout for the bundled db template root:

db/
  outputs.json                    → deck: shared + sqlite/postgres variants
  models/models.ssp               → {{smithyNamespaceDir}}/models/{{serviceModuleName}}_models.py
  service_protocol.ssp            → {{smithyNamespaceDir}}/{{serviceModuleName}}_protocol.py
  string_enum.ssp / int_enum.ssp  → {{smithyNamespaceDir}}/{{enumFileName}}.py  (Scala side path)
  tests/conftest.py               → <testOutputDir>/conftest.py
  sqlite/service_aiosqlite.ssp
  sqlite/migrations_service.ssp
  sqlite/sqlite_transaction_run.py
  sqlite/tests/…
  postgres/service_psycopg.ssp
  postgres/migrations_service.ssp
  postgres/psycopg_transaction_run.py
  postgres/tests/…
  postgres/stubs/testcontainers/postgres.pyi

dialect selects SQLite (? placeholders in generated Python) or Postgres (%s placeholders in generated Python service implementations). Build-time migration files contain dialect DDL. Generated column definitions include an FK -> table (column) comment for foreign-key columns. Postgres migrations emit foreign-key constraints as trailing ALTER TABLE ... ADD CONSTRAINT statements after table and index creation; SQLite migrations keep foreign keys inline in CREATE TABLE because SQLite cannot add table constraints after creation.

Python row mapping (Postgres vs SQLite)

Generated Postgres (*_psycopg.py) implementations set per-cursor row factories (class_row, dict_row) on psycopg.AsyncConnection.cursor(...). Each fetch is isolated to that cursor, so concurrent coroutines can share a connection safely.

Generated SQLite (*_aiosqlite.py) implementations use mapper-style row factories instead of assigning connection.row_factory. After fetchone() returns the default tuple row, codegen maps rows in-process—for example _Widget_row_factory(cursor, row) for scalar selectOne, or _as_sqlite_named_row(cursor, row) (a column-name dict) before column-keyed readers for JSON structures. The dict mapper avoids sqlite3.Row(cursor, row), which requires a stdlib sqlite3.Cursor and does not accept aiosqlite cursors.

SQLite’s stdlib API only supports row_factory on the connection. Temporarily swapping it in async code is unsafe when multiple coroutines share one aiosqlite.Connection: a task can await between execute and fetchone while another task changes or restores row_factory, producing mis-typed rows or clobbering another task’s setting. Mapper-style factories keep the ergonomics of row factories without mutating shared connection state.

Optional transaction parameter

Every generated service method ends with a keyword-only transaction parameter (default None). The shared {{serviceModuleName}}_protocol.py is generic over a type parameter T (class {{serviceClassName}}ServiceProtocol(Protocol[T])) and declares transaction: T | None. Each dialect implementation binds T to that backend’s transaction handle type and implements the protocol as {{serviceClassName}}ServiceProtocol[{{transactionTypeName}}]:

Dialect	T (transaction handle)	Pass when joining an outer transaction	Auto transaction when None
Postgres (*_psycopg.py)	psycopg.AsyncTransaction	psycopg.AsyncTransaction from async with connection.transaction() as tx: (SQL still runs on self._connection; the handle signals an open transaction on that connection)	psycopg_transaction_run.run wraps the method execute lambda in self._connection.transaction() when transaction is None
SQLite (*_aiosqlite.py)	aiosqlite.Connection	aiosqlite.Connection after the caller has issued BEGIN (or async with conn.transaction():); SQL runs on that connection	sqlite_transaction_run.run issues BEGIN → execute(conn) → commit (or rollback on error) when transaction is None

Pass the same transaction handle into multiple service calls to run them atomically. When transaction is omitted, each method still wraps its SQL in a single-operation transaction.

Generated test_*_derived_sql.py integration tests exercise this: test_derived_sql_methods_transaction_commit runs insert + select-one inside a caller-managed transaction and asserts rows persist after commit; test_derived_sql_methods_transaction_rollback asserts a rolled-back insert is not visible.

Full reference

Trait tables, Smithy examples, template context fields, SPI entries, and Python validation test setup:

→ modules/smithplates-plugin/README.md

Configuration

SQL settings live under smithplates.<language>.sql. Enable dialects and declare outputs entries:

{
  "plugins": {
    "smithplates": {
      "python": {
        "sql": {
          "sqlite": {
            "enable": true,
            "migrationLocation": "db/migrations/sqlite"
          },
          "outputs": [
            { "sourceOutputDir": "src/generated", "testOutputDir": "tests" }
          ]
        }
      }
    }
  }
}

Each outputs entry supports the same fields as HTTP: sourceOutputDir (required), testOutputDir (required), services (optional — accepts full shape IDs com.example#ServerDb or just shape names ServerDb; omit or set an empty list to generate all @sqlService shapes), and packageName (optional). When the model has multiple @sqlService shapes for different deployables, use multiple entries:

"outputs": [
  {
    "services": ["ServerDb"],
    "sourceOutputDir": "src/generated/server",
    "testOutputDir": "server/tests"
  },
  {
    "services": ["RunnerDb"],
    "sourceOutputDir": "src/generated/runner",
    "testOutputDir": "runner/tests"
  }
]

migrationLocation stays per-dialect and is separate from codegen output directories — it controls where the runtime migration service writes versioned .sql files, not where generated code lands.

See Configuration for the settings matrix and Integration for a combined SQL + HTTP walkthrough. Trait and template details: modules/smithplates-plugin/README.md.

Python JSON value mapping

Python SQL @sqlJson lists and maps use native Psycopg JSONB values on PostgreSQL and JSON text on SQLite. Nested structures, unions, lists, and maps receive transitive dump/read helpers; nullable members and nullable columns retain None. Timestamps use ISO-8601 strings, decimals use exact decimal strings, and blobs use hexadecimal strings inside JSON, reconstructed as datetime, Decimal, and bytes on reads. Empty collections remain distinct from SQL NULL.