Pietto

Readable, typed SQL authoring with deterministic compilation.

Pietto is a gradual semantic SQL authoring DSL. It parses and checks readable, indentation-based source, builds immutable compiler facts, and emits explicitly selected PostgreSQL or MySQL SQL. It does not connect to a database or execute SQL. PostgreSQL is the public Python SQL emitter; MySQL is available through explicit CLI lowering with a private emitter surface.

Status: 0.1.1 public preview. Pietto 0.1.1 can be installed from PyPI with pip install pietto and is also attached to a GitHub pre-release. It has the same compiler and CLI as 0.1.0 and adds PyPI packaging information. The compiler and CLI described below are the public surface; database execution and result APIs are not public yet.

What Pietto provides

• Typed shapes, sources, tables, queries, computed fields, aggregates, grouping, and bounded window expressions.
• Deterministic parser, semantic, and backend diagnostics with source locations where the relevant stage has them.
• Single-file check, explain, and emit-sql commands, with applicable JSON output.
• Explicit-project emit-sql for one selected table or query, one selected dialect and an explicit emission contract, producing the versioned pietto.sql-emission.v1 artifact.
• Deterministic project checking from an explicit pietto.toml root, including legacy-flat projects and the current explicit-module foundation.
• Compile-time validation and fail-closed lowering for unsupported syntax, semantics, and backend capability.

The compiler has no runtime evaluation, database connection, transaction management, scheduler, or arbitrary I/O language features.

Install

Install Pietto from PyPI into a virtual environment with CPython 3.12 or later (replace python3.12 with your interpreter):

python3.12 -m venv pietto-env
pietto-env/bin/python -m pip install pietto
pietto-env/bin/pietto --version

The optional extras described below install the same way, for example pietto-env/bin/python -m pip install "pietto[arrow]".

The wheel and the sdist are also attached to the v0.1.1 release. To install the wheel from there instead:

pietto-env/bin/python -m pip install "https://github.com/MianliWang/pietto/releases/download/v0.1.1/pietto-0.1.1-py3-none-any.whl"

The release lists the SHA-256 of the wheel and the sdist in SHA256SUMS. To check manually downloaded files, keep their names and run sha256sum --ignore-missing -c SHA256SUMS in the download directory. GitHub also offers automatic "Source code" archives; the tar.gz one downloads under the same name, pietto-0.1.1.tar.gz, but it is a snapshot of the repository, not the sdist, and it is not listed in SHA256SUMS. This preview was verified with CPython 3.12 and 3.13 on Linux x86-64; other platforms and Python versions are not covered by this evidence. With Pietto installed, run the commands below with pietto-env/bin/pietto (or activate the environment) instead of uv run pietto.

Install from a checkout

Pietto requires Python 3.12 or later and uses a locked uv environment:

uv sync --locked
uv run pietto --version
uv run pietto --help

Optional Arrow interoperability

The core compiler and CLI need no PyArrow. The pietto[arrow] selector selects exactly pyarrow==25.0.1; the execution extras below add one database route's pinned drivers. From this checkout, install it into a separate environment:

uv venv /tmp/pietto-arrow
uv pip install --python /tmp/pietto-arrow/bin/python ".[arrow]"

Or build and select the extra on that exact local wheel:

uv build --wheel
uv pip install --python /tmp/pietto-arrow/bin/python "./dist/pietto-0.1.1-py3-none-any.whl[arrow]"

For a core-only wheel install, omit [arrow] and use a fresh environment. These are checkout/local-wheel instructions; from PyPI, install "pietto[arrow]" as shown above. Package and CLI version are 0.1.1. The verified optional-extra matrix is CPython 3.12 and 3.13, Linux x86-64, PyArrow 25.0.1; other platforms, interpreters and Arrow versions are not covered by this evidence. Phase67 result APIs remain private under pietto._project; installing the extra only makes the dependency available. Public result APIs belong to Phase69, and the extra does not enable database execution. The Phase67 audit and handoff records the evidence layers, supported boundaries and remaining execution obligations.

Execution extras

Each private execution route has its own selector; each includes the Arrow pin because captured results are Arrow IPC:

Saved-result use without any database driver needs only pietto[arrow]. Selectors combine, for example "pietto[execute-postgres,execute-mysql]". Installing an extra never selects a route, grants execution, opens a source or qualifies storage. A selected route whose pinned driver is absent refuses with EXECUTION_DEPENDENCY_MISSING before any connection, another version refuses with that route's version error, and another route's installed driver is never used instead. These extras were verified on CPython 3.13.13, Linux x86-64, with the lock's exact artifacts; other platforms, interpreters and driver versions are not covered. Execution APIs remain private under pietto._project.

Quick start

Create active_users.pietto:

shape User:
    id: UUID not null
    email: Text nullable
    email_norm: Text nullable
    deleted_at: Timestamp nullable

source users: User is postgres.table("users")

table active_users:
    from users
    where deleted_at is null
    select:
        id
        email
        email_norm = lower(trim(email))

Check it:

uv run pietto check active_users.pietto
uv run pietto check active_users.pietto --format json

Explain its semantic metadata:

uv run pietto explain active_users.pietto
uv run pietto explain active_users.pietto --format json

Generate PostgreSQL SQL:

uv run pietto emit-sql active_users.pietto --dialect postgres

check does not generate or execute SQL. emit-sql writes SQL to stdout by default and can atomically replace an explicitly selected regular output file.

Projects

Project commands use an explicit root containing pietto.toml:

demo-project/
├── pietto.toml
└── models/
    └── active_users.pietto

For the available legacy-flat project mode:

schema_version = 1

[sources]
include = ["models/*.pietto"]

Then run:

uv run pietto check --project demo-project

Project input selection and diagnostics are deterministic. Project checking is not SQL generation; project SQL emission is a separate explicit command.

Project SQL emission

emit-sql --project compiles one explicitly selected table or query of an explicit-module project into the versioned pietto.sql-emission.v1 artifact for one explicitly selected dialect. The physical relation and column names, storage types and value premises come from an emission contract you write for your database; the compiler never connects to it.

demo-emit/
├── pietto.toml
├── main.pietto
└── contract.json

pietto.toml selects the explicit-module project mode:

schema_version = 2

[sources]
include = ["*.pietto"]

main.pietto:

shape Item:
    id: Int not null
    label: Text not null

source items: Item is postgres.table("fixture.items")

table active_items:
    from items
    select:
        id
        label

contract.json maps the logical source items to the physical relation fixture.items and each field to its column and representation:

{
  "format": "pietto.emission-contract.v1",
  "target": {"family": "postgres", "release": "18.6"},
  "sources": [{
    "selector": {"module": "main.pietto", "kind": "source", "name": "items"},
    "relation": {"namespace": "fixture", "name": "items"},
    "scan": "relation_rows",
    "fields": [
      {"ordinal": 0, "name": "id", "column": "item_id",
       "representation": {"storage": {"kind": "pg_int8"}, "nullable": false,
                          "domain": {"kind": "int_range", "min": "0", "max": "100"}}},
      {"ordinal": 1, "name": "label", "column": "item_label",
       "representation": {"storage": {"kind": "pg_text"}, "nullable": false,
                          "domain": {"kind": "text", "max_characters": 64,
                                     "encoding": "UTF8", "collation": "C",
                                     "padding": "NO PAD"}}}
    ],
    "premises": [
      {"key": "row_domain_matches", "scope": "source", "value": true},
      {"key": "read_only_object", "scope": "source", "value": true}
    ]
  }],
  "environment": [
    {"key": "client_encoding", "scope": "statement", "value": "UTF8"},
    {"key": "operator_environment", "scope": "statement", "value": "builtin_only"}
  ]
}

Emit the artifact as JSON on stdout (--format json and --literal-policy preserve are the defaults; the contract path is relative to the project root):

uv run pietto emit-sql --project demo-emit --module main.pietto --kind table \
    --name active_items --dialect postgres --emission-contract contract.json

Show the readable presentation on stdout and atomically write the same JSON artifact to a file:

uv run pietto emit-sql --project demo-emit --module main.pietto --kind table \
    --name active_items --dialect postgres --emission-contract contract.json \
    --format text --output active_items.sql-emission.json

Exit codes are 0 for a VERIFIED artifact, 1 for BLOCKED (typed blockers or project diagnostics) and 2 for INPUT_REJECTED (usage, project, contract or output errors); failures carry no candidate SQL. A --literal-policy bind-safe artifact must be consumed together with its fixed_values and parameter_uses; its SQL alone is not a complete statement. The complete contract is the Slice13 specification.

Documentation

• Language
• Project and package
• Development
• Roadmap
• Status

Public contracts:

• CLI JSON v1
• Diagnostics
• Project JSON v2
• Semantic metadata artifact v1
• Configuration
• Golden fixture policy

Development

Run the authoritative local validation suite with:

uv run python scripts/validate.py

The normal implementation loop is focused tests, Ruff, and targeted type checking. Natural CI runs the final Python 3.12 and 3.13 validation coverage.

Direction

Pietto is an early preview. These are the goals it is working toward; they are not promises or dates, and the detailed plan lives in the roadmap.

• Everyday SQL: CASE, CAST, COALESCE/NULLIF, IN lists, NOT, LIKE, and common string, number and date functions.
• Real-world column types end to end: timestamps, UUIDs, decimals, dates and times, then arrays and nested records.
• Richer queries: subqueries and WITH, EXISTS/IN, VALUES and table functions.
• Richer aggregation: SUM and AVG in project SQL emission, FILTER, statistical aggregates, grouping extensions and aggregates as window functions.
• Run queries safely from Python: compile, execute through an explicit database driver, and get typed Arrow results.
• Data tools: pandas, Polars and NumPy interoperability.
• More databases: deeper PostgreSQL and MySQL support, then SQLite and DuckDB.
• Tooling: a formatter, language-server and editor support, clearer diagnostics.
• Stability: a 1.0 release with frozen syntax, CLI and APIs.

License

Pietto is released under the MIT License. Third-party material in the wheel, the sdist and the source archive is listed in THIRD_PARTY_NOTICES.md, with the original license texts in LICENSES/.