Database queries

SemanticDatabase turns a question in plain words into one SQL statement with parameters, checks the statement, and runs it only if the check passes. It works over any ADO.NET DbConnection, in the IntentSharp.Data namespace. By default it only reads.

Ask a question, get rows

var db = new SemanticDatabase(client, connection, new DatabaseOptions
{
    AllowedTables = ["Customers", "Orders"],
    MaxRows = 200,
    CommandTimeout = TimeSpan.FromSeconds(10),
});

QueryResult result = await db.QueryAsync("Customers in California with a total above 1000, largest first");
foreach (var row in result.Rows)
    Console.WriteLine(string.Join(", ", row.Select(column => $"{column.Key}: {column.Value}")));
if (result.Truncated)
    Console.WriteLine($"Only the first {db.Options.MaxRows} rows were read.");

List<CustomerRow> customers = result.As<CustomerRow>();

QueryAsync(question) plans the statement, checks it and runs it. Each row is a dictionary from column name to value, with names compared ignoring case. As<T>() maps the rows to objects, matching property names ignoring case.
At most MaxRows rows are returned. Truncated is true when there were more.
A closed connection is opened for each operation and closed again; an open one is left open. The connection is never disposed.

The database use case shows the same query planned, reviewed and run, next to the code you would write by hand.

What the model sees, and what it does not

To plan a statement, the model receives your question, the SQL dialect, and the names and types of the columns of the tables it may use, with their primary keys and whether they accept null. It never receives your rows: the statement runs on your connection, and its results go only to your code. Tables outside AllowedTables are not described to the model at all.

UpsertAsync is the exception: the model receives the source object you pass, because it maps that object's fields to the table's columns.

The options start at the safest value

DatabaseOptions:

Option	Default	What it does
`AllowWrites`	`false`	Only reading statements (`SELECT`, or `WITH` that reads). `true` also allows `INSERT`, `UPDATE`, `DELETE` and `MERGE`.
`AllowedTables`	`null`	The tables a statement may use. Null means every table the connection can see; an empty list means none.
`DryRun`	`false`	`QueryAsync` and `UpsertAsync` return the checked plan without running it.
`MaxRows`	1000	The rows returned per query.
`CommandTimeout`	30 seconds	The ADO.NET command timeout, applied in whole seconds. A value under one second means no time limit.
`RequireParameters`	`true`	Refuse a statement that writes values into a `WHERE` and passes no parameters.
`SchemaCacheDuration`	10 minutes	How long the discovered tables and columns are reused. `GetSchemaAsync(refresh: true)` reads them again now.
`UseTransactionsForWrites`	`true`	Writes run in a transaction that rolls back on a database error.

How parameters are bound

The model is asked to pass each value as a parameter, @name, and to supply its value separately in the plan. Every parameter the statement uses is bound by name through ADO.NET. A statement that uses a parameter with no value fails before the connection is used. The model can still write a value into the SQL text, and the limits of the check say which of those statements pass.

Review a statement before it runs

With DryRun, QueryAsync returns the checked plan and runs nothing. Show it to a person, then run the same plan with ExecutePlanAsync, which checks it again first:

var db = new SemanticDatabase(client, connection, new DatabaseOptions { AllowedTables = ["Customers"], DryRun = true });

// Planned and checked, not run: Executed is false and there are no rows.
QueryResult planned = await db.QueryAsync("Customers in California with a total above 1000");
Console.WriteLine(planned.Plan.Sql);
foreach (var (name, value) in planned.Plan.Parameters)
    Console.WriteLine($"@{name} = {value}");

// After a person approves it, run the same plan. The check runs again first.
QueryResult executed = approve(planned.Plan)
    ? await db.ExecutePlanAsync(planned.Plan)
    : planned;

PlanAsync(question) does the planning alone. It throws nothing for a refused statement: it returns the planning result and the check's report (SqlSafetyReport: IsAllowed, Violations, StatementKind, ReferencedTables and ParameterNames) for you to inspect. Check the planning result's Success first. The report describes the final statement only when planning succeeded with a statement. When planning failed, for example because every attempt was refused, or when the model declined, the report is an empty one: IsAllowed is true and StatementKind is UNKNOWN. ExecutePlanAsync also accepts a plan you build or change yourself; the check still applies.

A refused statement throws, and nothing runs

The check runs on every attempt. A statement that fails it goes back to the model with the reasons, up to three attempts. When no attempt passes, when the model declines because the schema cannot answer the question, or when a plan you pass to ExecutePlanAsync fails the check, the method throws SemanticDatabaseException and runs nothing. Its Reasons list why:

try
{
    return await db.QueryAsync(question);
}
catch (SemanticDatabaseException ex)
{
    // Refused, declined, or rejected by the database: Reasons says why.
    Console.WriteLine(ex.Message);
    foreach (var reason in ex.Reasons)
        Console.WriteLine($"- {reason}");
    return null;
}

A database error while the statement runs also throws SemanticDatabaseException, with the database's message and the SQL in Reasons. A write in a transaction is rolled back. A write without one (UseTransactionsForWrites false) may have been partly applied.

Writes: one statement, one table

UpsertAsync(table, source) inserts or updates from a .NET object. It needs AllowWrites, a table the connection can see and, when AllowedTables is set, a table in it. The model maps the source's fields to the table's columns by meaning:

var db = new SemanticDatabase(client, connection, new DatabaseOptions
{
    AllowWrites = true,
    AllowedTables = ["Customers"],
});

// The model maps CustName to FullName and Region to State by meaning; the values travel as parameters.
var signup = new { CustName = "Ada Moreno", Region = "NY", Total = 0 };
QueryResult written = await db.UpsertAsync("Customers", signup);
Console.WriteLine($"{written.Plan.Sql} ({written.RowsAffected} row)");

The statement must be one INSERT, UPDATE or MERGE on that table alone, whatever else AllowedTables allows, and a DELETE anywhere in it is refused. When the source holds several rows, the model is asked for a statement that handles the first one; the check does not count rows. RowsAffected is the count the database reports.

Which databases

The dialect, and how the tables are found, come from the connection's type:

Connection	Dialect
Microsoft.Data.Sqlite	SQLite
MySQL	MySQL
SQL Server (`SqlClient`)	SQL Server
Npgsql	PostgreSQL
Oracle	Oracle, with the tables from `DbConnection.GetSchema`
any other	general SQL, with the tables from `DbConnection.GetSchema`

The connection string is never copied into the discovered schema. To read the tables another way, pass your own ISchemaDiscovery to the constructor.

The check is a guard, not proof

The check asks for one statement only, a statement kind the options allow, no commands that change the database's structure or permissions, only allowed tables, and parameters for values. Read it as a guard against the common mistakes of model-written SQL, not as proof that a statement is harmless.

It refuses some valid SQL, for example a column whose name is also a refused command word, or quoting that one database reads differently from another.
It cannot prove a statement harmless, and it can miss a table that is not allowed.
SQL Server runs several statements written one after another without a ;. A second statement written that way is still refused when it changes data or structure, but not for being a second statement.
The parameter rule refuses only a statement that has a WHERE, values written into its text and no parameters. A statement with one parameter and other values written into its text passes, and so does a statement with no WHERE, such as an INSERT with its values written in.
Discovery is tested against SQLite only. The other databases above are not tested against live servers. Where primary keys cannot be read, they are left out and discovery still succeeds.

So give the connection a database account with only the permissions the application needs: read-only for QueryAsync, and write access only to the tables UpsertAsync should change.

Table of Contents