SQL scripts

SQL scripts

Use language sql. Declare exactly one postgresql, mysql, sqlserver or sqlite resource. The resource chooses the dialect; there is no separate mysql script language. The platform executes SQL directly with its database client; no Python/Bun interpreter or ctx exists here. The account must be allowed to author/run in the project, and the resource’s database account must have the SQL privileges required. Parameter binding is not authorization or read-only enforcement.

Complete minimal script

With an existing isolated PostgreSQL resource named db, save scripts/sql_echo.sql:

-- @resource db
-- @input value:int
SELECT :value AS value;

viat script create --name sql_echo --language sql --file scripts/sql_echo.sql
viat script run sql_echo --args '{"value":7}'
viat script update sql_echo --file scripts/sql_echo.sql

Expected result body: {"rows":[{"value":7}],"affected":1}. This read-only example creates no table. Resource creation takes viat resource create --file resources/db.yaml, not --config; the resource reference owns that file’s credential/config shape. Do not put real credentials in the script or its logs. Public binding/publishing is shown in Testing.

Parameters and result types

Write :name in every dialect. The binder rewrites to PostgreSQL $N, MySQL ?, SQL Server @PN, SQLite ?N and sends values separately; never concatenate user values. String literals, dollar-quoted bodies and ::casts are not runtime parameters. Static schema extraction is simpler and can misread unusual SQL; inspect warnings. -- @input name:type[=default] declares types, otherwise placeholders become required string inputs with warning. Annotation defaults remain strings, not a promise to supply missing parameters. See all type names.

All dialects return {rows,affected}. Numeric/decimal columns generally become strings to preserve exactness. PostgreSQL UUID/time become strings, bytea base64, interval {months,days,microseconds}, arrays element-wise; unsupported column types can become null with a warning (cast to text to choose representation). MySQL TINYINT(1) is boolean. SQL Server bit is boolean, uniqueidentifier string and binary base64; unsupported spatial/sql_variant may fail rather than becoming null. SQLite’s flexible storage classes make explicit casts useful. SQL Server uses SQL authentication, not Windows/AD.

Statements, transactions and failures

Parameterized PostgreSQL scripts are one prepared statement. A parameterless PostgreSQL script can fall back to simple-query mode for multiple statements and executes them in one transaction; failure rolls them back. Returned text-protocol values do not decode every type (e.g. interval), so prefer one statement for result-bearing queries. Other engines and broker query helpers are one statement per call; do not extrapolate this PostgreSQL exception. Persistent schema migrations should use migration tooling rather than repeatedly executing DDL as an endpoint.

-- timeout: N chooses an execution budget subject to deployment clamps; database/network errors and timeout fail the run. There is no ViatError, no task API, and no per-row streaming. Cancellation is not a guarantee that an already-sent write is undone. Do not retry writes blindly; workflow retries classify runtime errors, not arbitrary SQL error text. Use explicit transactions through a general-language broker helper when several application operations must be atomic.

Check results on the isolated resource, inspect logs/warnings, then remove any disposable endpoint before deleting sql_echo. There is no database cleanup for this SELECT-only example. A successful save does not prove the database is reachable.