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.
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");
}
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.