Development documentation · Some APIs may be unreleased Published release →

Guides

SQL databases

Write SQL, pass plain values, and map rows to ordinary Raven objects. System.Data.Sql provides common connections, statements and the SqlMapper; System.Data.PostgreSql supplies the optional PostgreSQL provider.

The provider is currently for local development: IPv4 loopback and trust authentication. Password authentication, TLS, remote servers, pooling and streaming are not implemented. Use a matching development bundle containing the provider.

Query objects with PostgreSQL

This model matches the returned column names:

public class Person {
    init() { }
    var id: int { get; set; } = 0
    var name: string { get; set; } = ""
    var active: bool { get; set; } = false
}

Pass positional values alongside the SQL. Query<Person> returns a task containing a Result; ? propagates a SqlError to the enclosing async function.

let positional = await SqlMapper.Query<Person>(connection,
    "SELECT $1::int AS id, $2::text AS name, $3::bool AS active",
    42, "Café team", true)?
for person in positional {
    Console.WriteLine(person.id.ToString() + ": " + person.name)
}

Values are bound separately from SQL text, including quotes and Unicode. The mapper copies columns to scalar properties; it does not generate SQL or track changes.

Use named parameters

A plain object can supply the values for @name placeholders:

let parameters = Person()
parameters.id = 43
parameters.name = "O'Brien"
parameters.active = true
let named = await SqlMapper.Query<Person>(connection,
    "SELECT @id::int AS id, @name::text AS name, @active::bool AS active",
    parameters)?
for person in named {
    Console.WriteLine(person.id.ToString() + ": " + person.name)
}

PostgreSQL owns the placeholder translation. The common mapper passes SQL to the selected provider without imposing PostgreSQL syntax on other database engines.

Run the PostgreSQL people example → The walkthrough includes connection setup, a disposable Docker database and the complete downloadable source.

Connections, errors and transactions

Open a connection with await database.Open() and own it with use. Query results are buffered snapshots and remain readable after the connection closes. Failures are SqlError values; server errors retain SQLSTATE.

Transactions support awaited commit and rollback. Explicit rollback permits connection reuse. Disposing an active transaction closes the connection, allowing PostgreSQL to roll it back on disconnect.

SQL API and mapping rules · PostgresDatabase · SqlMapper