Skip to content

Databases

Server bundle code can talk to a database through re.sql, an ADO.NET provider. It supports both a server database (Postgres) and a per bundle SQLite file behind the same API:

await using var db = new re.sql.SqlConnection(re.sql.SqlConnection.For("players"));
await db.OpenAsync();

await using var cmd = new re.sql.SqlCommand(db);
cmd.CommandText = "select id, name from players where score > $1 order by score desc";
cmd.Parameters.AddWithValue(100);

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"{reader.GetInt64(0)} {reader.GetString(1)}");
}

Aliases are declared in the server-side [Database] group of config.toml (see Bundle Config). Bundle code never supplies a host, port, credentials or file path, so none of them can leak into the bundle or to a client.

Everything is asynchronous. The host runs statements on its own thread and answers on a later script tick, so the synchronous DbConnection and DbDataReader members throw and name the async variant instead of deadlocking.

Type Purpose
SqlConnection OpenAsync, CloseAsync, BeginTransactionAsync
SqlCommand CommandText, Parameters, CommandTimeout, ExecuteReaderAsync, ExecuteNonQueryAsync, ExecuteScalarAsync
SqlDataReader ReadAsync, NextResultAsync, typed getters, IsDBNull
SqlTransaction CommitAsync, RollbackAsync

Parameters use $1, $2, ... positional placeholders. AddWithValue takes null, DBNull.Value, bool, any integer or floating point type, decimal, string, char, Enum, byte[], DateOnly, TimeOnly, DateTime and DateTimeOffset.

CommandTimeout is in seconds and defaults to 5. The script host limits it to 30 second at most, otherwise a misbehaving bundle could block all others. 0 means "use the default of 5". If a query exceeds its deadline, it is cancelled.

The API is identical on both backends, so the same bundle code should runs against either one. Batches, multiple result sets, transactions, CommandTimeout, IsDBNull, affected row counts and cursors behave the same, but values might be returned differently depending on the backend. See the table below for notable quirks of each backend.

Differences from Postgres

A few things cannot be made identical, because they are quirks / properties of the database itself:

Area Postgres SQLite
Column types From the columns type OID, so CommandText alone decides From the declared type where SQLite records one, otherwise from the value
Integer columns Int32 for int2/int4, Int64 for int8 Always Int64. Use GetInt64 or Convert
Decimal columns Decimal for numeric, parsed from text so no digits are lost Int64 or Double as SQLite has no exact decimal type
Dates, times and Guid DateOnly, TimeOnly, DateTime, Guid from their OIDs Stored as string or Int64. Needs to be parsed explicitly
Isolation levels Accepted in the form of Serializable, RepeatableRead, etc. A basic BEGIN, level is ignored
Error text Postgres error messages SQLite error messages

On Postgres the CLR type is decided by the column type OID, so GetValue and GetFieldType always match. On SQLite a column that declares no type falls back to the value in its first row, and that type then applies to the whole result set.

Read numeric columns with GetInt64/GetDouble rather than GetInt32 when the same code may run against either backend. Convert.ToInt32(...) on a GetValue result is safe on both. GetGuid, GetDateTime and GetDecimal parse from the stored text, so they work on SQLite even though GetValue returns a string there.

Dapper

re.sql.SqlConnection is a DbConnection, so Dapper works with it directly. Add both packages to your bundle:

<PackageReference Include="Dapper" Version="2.1.66" />
<PackageReference Include="Dapper.AOT" Version="1.0.48" />
<PropertyGroup>
    <InterceptorsNamespaces>$(InterceptorsNamespaces);Dapper.AOT</InterceptorsNamespaces>
    <NoWarn>$(NoWarn);DAP005</NoWarn>
</PropertyGroup>

Declare a row type and annotate the methods containing queries with [DapperAot]. The methods have to contain the direct Dapper call and a constant SQL string.

sv/Players.cs
using Dapper;

public sealed class Player
{
    public long Id { get; set; }
    public string Name { get; set; } = "";
    public int Score { get; set; }
}

public static class Players
{
    public static async Future<List<Player>> Top(re.sql.SqlConnection db, int minScore)
    {
        var rows = await Query(db, minScore);
        return rows.ToList();
    }

    [DapperAot]
    private static Task<IEnumerable<Player>> Query(re.sql.SqlConnection db, int minScore) =>
        db.QueryAsync<Player>(
            "select id, name, score from players where score >= @minScore order by score desc",
            new { minScore }
        );

    [DapperAot]
    private static Task<int> Add(re.sql.SqlConnection db, string name, int score) =>
        db.ExecuteAsync(
            "insert into players (name, score) values (@name, @score)",
            new { name, score }
        );

    [DapperAot]
    private static Task<long> Count(re.sql.SqlConnection db) =>
        db.ExecuteScalarAsync<long>("select count(*) from players");
}
sv/App.cs
await using var db = new re.sql.SqlConnection(re.sql.SqlConnection.For("players"));
await db.OpenAsync();

await Players.Add(db, "alice", 42);

foreach (var player in await Players.Top(db, 10))
{
    Console.WriteLine($"{player.Id} {player.Name} {player.Score}");
}

Parameter names are matched to the properties of the object you pass, so @minScore binds to the minScore property. Dapper sends null as DBNull.Value, which the provider maps to SQL NULL.