Skip to content

Compiled SQL Execution

MetaTables let applications keep SQLAlchemy/Core ergonomics while TS Manager handles authentication, authorization, routing, limits, and execution through a registered data source.

The protocol is compiled-sql.v1.

That version string is a TS Manager wire-contract version. It is not a SQLAlchemy version. The SDK sets it automatically and validates it with Pydantic before sending the operation.

Why Compile On The Client

Your app should build queries with its normal tools:

stmt = (
    select(Asset.uid, Asset.symbol)
    .join(Account, Asset.account_uid == Account.uid)
    .where(Account.uid == account_uid)
    .where(Asset.symbol.ilike("%BTC%"))
)

The SDK compiles the SQLAlchemy/Core statement into plain SQL plus bound parameters:

{
  "operation": "select",
  "version": "compiled-sql.v1",
  "dialect": "postgresql",
  "statement": {
    "sql": "SELECT asset.uid, asset.symbol FROM public.asset AS asset JOIN public.account AS account ON asset.account_uid = account.uid WHERE account.uid = %(account_uid)s",
    "parameters": {
      "account_uid": "cccccccc-cccc-4ccc-8ccc-cccccccccccc"
    },
    "paramstyle": "pyformat"
  },
  "scope": {
    "data_source_uid": "dddddddd-dddd-4ddd-8ddd-dddddddddddd",
    "tables": [
      {
        "meta_table_uid": "aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa",
        "alias": "asset",
        "access": "read"
      },
      {
        "meta_table_uid": "bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb",
        "alias": "account",
        "access": "read"
      }
    ]
  }
}

TS Manager does not import your SQLAlchemy models and does not interpret ORM relationship paths. It receives the compiled artifact and the declared table scope.

Build A Payload Manually

Use this when you already have SQL text and parameters:

from mainsequence.meta_tables.compiled_sql.v1 import build_operation


operation = build_operation(
    operation="select",
    sql="SELECT asset.uid, asset.symbol FROM public.asset AS asset WHERE asset.symbol ILIKE %(symbol_1)s",
    parameters={"symbol_1": "%BTC%"},
    scope={
        "data_source_uid": data_source.uid,
        "tables": [
            {
                "metaTableUid": asset_meta_table.uid,
                "alias": "asset",
                "access": "read",
            }
        ]
    },
    limits={"max_rows": 1000, "statement_timeout_ms": 15000},
)

The return value is a MetaTableCompiledSQLOperation Pydantic object. If scope.data_source_uid is omitted or None, the SDK resolves the configured project/session default data source and serializes that concrete UID into the request payload.

The SDK validates:

  • version == "compiled-sql.v1"
  • dialect == "postgresql"
  • paramstyle == "pyformat"
  • non-empty SQL
  • concrete execution data source after default resolution
  • non-empty declared table scope
  • positive limits when supplied
  • operation kind: select, insert, update, delete, or upsert

The SDK accepts metaTableUid and meta_table_uid when constructing Pydantic objects. When MetaTable.execute_operation(...) sends the payload, it serializes the field as meta_table_uid, which is the TS Manager server contract.

Compile A SQLAlchemy Statement

Use compile_sqlalchemy_statement(...) to turn a SQLAlchemy statement into the backend compiled SQL protocol.

For platform-managed SQLAlchemy models, compile statements after the MetaTable migration workflow has registered and bound the model. Migration-managed registration binds the backend MetaTable.uid and storage metadata while preserving Model.__table__.name, so compiled SQL targets the authored table name that Alembic created.

from sqlalchemy import select

from mainsequence.meta_tables.compiled_sql.v1 import compile_sqlalchemy_statement


stmt = (
    select(Asset.uid, Asset.symbol)
    .join(Account, Asset.account_uid == Account.uid)
    .where(Account.uid == account_uid)
    .where(Asset.symbol.ilike("%BTC%"))
)

operation = compile_sqlalchemy_statement(
    stmt,
    operation="select",
    data_source_uid=data_source.uid,
    scope_tables=[
        {
            "metaTableUid": asset_meta_table.uid,
            "alias": "asset",
            "access": "read",
        },
        {
            "metaTableUid": account_meta_table.uid,
            "alias": "account",
            "access": "read",
        },
    ],
    limits={"max_rows": 1000, "statement_timeout_ms": 15000},
)

Then execute through TS Manager:

from mainsequence.client import MetaTable


result = MetaTable.execute_operation(operation)

The backend endpoint is:

POST /orm/api/ts_manager/meta_table/execute-operation/

Scope Is Required

Compiled SQL execution has two pieces of scope:

  • scope.data_source_uid selects the DynamicTableDataSource execution connection. The SDK resolves the project/session default when callers omit it.
  • scope.tables declares MetaTable permissions for the operation.

Every table referenced by the compiled SQL should appear in scope:

scope_tables=[
    {"metaTableUid": asset_meta_table.uid, "alias": "asset", "access": "read"},
    {"metaTableUid": account_meta_table.uid, "alias": "account", "access": "read"},
]

Access can be:

  • read
  • write

For write operations, scope the mutated table with access="write".

TS Manager does not treat SQL text as the permission model. PostgreSQL owns SQL validity, while the declared MetaTable scope is the platform permission contract.

Backend Execution Rules

TS Manager validates and executes the operation through the selected DynamicTableDataSource.

It checks:

  • every scoped MetaTable exists and is visible to the caller
  • the caller has the requested access
  • the selected data source exists and can execute the requested operation
  • the operation kind matches the SQL statement kind
  • the dialect is supported
  • the statement is single-statement
  • parameters are bound, not string-interpolated
  • row limits and statement timeouts are respected
  • writes are gated by object permission and data-source capabilities

If omitted, the SDK sends max_rows=1000, offset=0, and statement_timeout_ms=15000, matching the backend defaults. For select operations, max_rows is the total number of rows requested by the SDK call. The backend still enforces a per-response page cap, so MetaTable.execute_operation(...) uses the backend pagination.next_offset contract to fetch additional pages until it has returned the requested row count or the backend reports no more rows. Stable pagination requires the SQL to include a deterministic ORDER BY. Write operations execute once; the SDK does not follow pagination for insert, update, upsert, or delete responses because that would rerun the mutation.

Read-only raw SQL remains separate. Writable MetaTable operations should use compiled operation envelopes with declared scope.

Read-Only Raw SQL

Use MetaTable.run_query(...) for direct read-only inspection of one MetaTable. This path is for diagnostics and exploration, not for reusable application operations.

The SDK uses:

  • POST /orm/api/ts_manager/meta_table/{meta_table_uid}/run_query/

Request contract:

  • the request body is a JSON string containing the SQL
  • do not send JSON like { "sql": "SELECT ..." }

Example:

import mainsequence.client as msc

meta_table = msc.MetaTable.get(uid="<META_TABLE_UID>")
result = meta_table.run_query("SELECT * FROM public.some_table LIMIT 100")

CLI:

mainsequence meta-table run_query <META_TABLE_UID> "SELECT * FROM public.some_table LIMIT 100"

Writes

The same protocol family supports insert, update, upsert, and delete when the backend data source allows those operations.

Example shape:

operation = build_operation(
    operation="upsert",
    sql=(
        "INSERT INTO public.asset (uid, symbol) "
        "VALUES (%(uid)s, %(symbol)s) "
        "ON CONFLICT (uid) DO UPDATE SET symbol = EXCLUDED.symbol"
    ),
    parameters={"uid": str(asset_uid), "symbol": "BTC"},
    scope={
        "data_source_uid": data_source.uid,
        "tables": [
            {
                "metaTableUid": asset_meta_table.uid,
                "alias": "asset",
                "access": "write",
            }
        ]
    },
)

Do not send arbitrary writable SQL without scope. TS Manager expects the operation kind, declared MetaTable scope, and backend capability gates.

Common Mistakes

Do not pass SQLAlchemy objects to MetaTable.execute_operation(...):

MetaTable.execute_operation(stmt)  # wrong

Compile first:

operation = compile_sqlalchemy_statement(
    stmt,
    operation="select",
    data_source_uid=data_source.uid,
    scope_tables=[{"metaTableUid": asset_meta_table.uid, "access": "read"}],
)
MetaTable.execute_operation(operation)

Do not omit joined tables from scope. If Asset joins Account, both MetaTables must be declared unless the backend adapter explicitly supports another authorization model.