StringThing.MySql 2.0.2

StringThing.MySql

Injection-safe interpolated SQL for MySQL/MariaDB with type-checked parameterization, built on MySqlConnector. Part of StringThing.

Install

dotnet add package StringThing.MySql

Quick start

var userId = 42;
MySql statement = $"SELECT name FROM users WHERE id = {userId}";
await using var command = statement.ToCommand(connection);

Parameters are named automatically using the variable name: @userId, not @p0.

Result mapping

Mark row types with [StringThingRow] and declare them partial. A source generator emits an AOT-friendly row materializer — no reflection, no IL emit, no third-party mapper.

using StringThing.Aot;

[StringThingRow]
public partial record User(int Id, string Name, string? Email);

var user = await connection.QueryStringSingleAsync<User>(
    $"SELECT id AS Id, name AS Name, email AS Email FROM users WHERE id = {userId}");

var users = await connection.QueryStringAsync<User>(
    $"SELECT id AS Id, name AS Name, email AS Email FROM users ORDER BY id");

await connection.ExecuteStringAsync($"DELETE FROM users WHERE id = {userId}");

The full surface: QueryString<T>, QueryStringFirst<T>, QueryStringFirstOrDefault<T>, QueryStringSingle<T>, QueryStringSingleOrDefault<T>, ExecuteString, ExecuteStringScalar (+ T overload), plus Async variants. Column ordinals are resolved once per query; rows are then read by ordinal — name-based binding without per-row name lookup.

Scalar columns map directly. When T is a supported scalar type rather than a [StringThingRow] type, the query reads its first column into that value — no row type or wrapper needed:

var ids = await connection.QueryStringAsync<int>($"SELECT id FROM users ORDER BY id");
var name = await connection.QueryStringSingleAsync<string>($"SELECT name FROM users WHERE id = {id}");
var email = await connection.QueryStringSingleAsync<string?>($"SELECT email FROM users WHERE id = {id}");

Nullable scalars read NULL as null/default. A T that is neither a supported scalar nor a [StringThingRow] type is a compile error (ST0002).

Override the column name with [Column] from System.ComponentModel.DataAnnotations.Schema:

[StringThingRow]
public partial record User(
    [property: Column("user_id")] int Id,
    [property: Column("full_name")] string Name);

Nullable annotations drive IsDBNull checks — string? becomes a null-checked read; string is a direct read.

If the generator can't handle your shape — say, you want to derive a property from a column rather than read it straight, or read columns into a shape the generator couldn't infer from the type — implement IStringThingRow<T> by hand. Same runtime path:

public sealed class UserSummary : IStringThingRow<UserSummary>
{
    public int Id { get; init; }
    public string Status { get; init; } = "";

    public static ReadOnlySpan<string> ColumnBindingOrder => ["id", "email"];

    public static UserSummary Read(DbDataReader reader, ReadOnlySpan<int> ordinals) => new()
    {
        Id = reader.GetInt32(ordinals[0]),
        Status = reader.IsDBNull(ordinals[1]) ? "no-email" : "has-email",
    };
}

Supported types

bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, decimal, string, char, Guid, DateTime, DateTimeOffset, DateOnly, TimeOnly, TimeSpan, byte[].

Nullable reference types (string?, byte[]?) map to NULL.

Fragments

var minAge = 18;
var status = "active";
MySqlFragment filter = $"age >= {minAge} AND status = {status}";

MySql statement = $"SELECT * FROM users WHERE {filter}";

Multi-row insert

record InsertUser(int Id, string Name, string? Email) : IMySqlRow
{
    public MySqlFragment RowValues => $"({Id}, {Name}, {Email})";
}

var users = new InsertUser[] { new(1, "alice", "alice@example.com"), new(2, "bob", null) };
MySql statement = $"INSERT INTO users (id, name, email) VALUES {MySql.InsertRows(users)}";

IN list

var ids = new List<int> { 1, 2, 3 };
MySql statement = $"SELECT * FROM users WHERE id IN {MySql.InList([.. ids])}";

JSON

Implement IMySqlJson on your types to store them as JSON. Use your choice of serializer:

record UserData(string Name, int Age) : IMySqlJson
{
    public string ToJson() => JsonSerializer.Serialize(this);
}

MySql statement = $"INSERT INTO data (payload) VALUES ({userData})";

Type overrides

// TEXT instead of default VARCHAR
MySql statement = $"WHERE description = {MySql.Text(description)}";

// TIMESTAMP instead of default DATETIME
MySql statement = $"WHERE created < {MySql.Timestamp(date)}";

Unsafe escape hatch

using StringThing.UnsafeSql;

var tableName = Sql.Unsafe("users");
MySql statement = $"SELECT * FROM {tableName} WHERE id = {userId}";

Built by Immersus Machina