SwiftSQL 0.10.0

SwiftSQL

SwiftSQL is a lightweight, async-first, ORM-like data access library built on top of Dapper.

It is designed specifically for SwiftlyS2 / CS2 plugins, where:

• IDbConnection is provided by SwiftlyS2
• Connection pooling is handled externally
• Explicit reads and writes are preferred
• Parallel async execution is common
• Predictable behavior matters more than abstraction magic

Supported databases:

• SQLite
• MySQL / MariaDB
• PostgreSQL

Core philosophy

SwiftSQL intentionally does NOT:

• Manage database connections
• Track entity changes
• Auto-evolve or repair schemas
• Modify existing tables
• Hide SQL behavior

Instead:

• Tables are defined explicitly
• Tables are created explicitly
• Reads and writes are explicit
• SQL dialect is chosen once at startup

This mirrors how SwiftlyS2 plugins are structured and loaded.

1. Define a table model

A table model represents a database table. All mappings are explicit using attributes.

    using SwiftSQL.Attributes;

    [Table("avip_players")]
    public sealed class PlayerModel
    {
        [Key]
        [Column("steamid")]
        public ulong SteamId { get; set; }

        [Column("name", Length = 128)]
        public string? Name { get; set; }

        [Column("vip_group")]
        public int Group { get; set; }

        [Column("is_active")]
        public bool IsActive { get; set; }

        [Column("date_expires")]
        public DateTime? Expires { get; set; }
    }

Supported attributes:

• Table
• Column
• Key
• Ignore
• JsonColumn

2. Define a table model with JSON storage (optional)

SwiftSQL supports JSON-backed columns for flexible data such as player preferences.

    [Table("avip_preferences")]
    public sealed class PreferenceModel
    {
        [Key]
        [Column("steamid")]
        public ulong SteamId { get; set; }

        [Column("preferences")]
        [JsonColumn]
        public Dictionary<string, object> Preferences { get; set; } = new();

        [Column("date_updated")]
        public DateTime Updated { get; set; }
    }

JSON column mapping:

• SQLite → TEXT
• MySQL / MariaDB → JSON
• PostgreSQL → JSONB

3. Create a database service (SwiftlyS2 style)

SwiftSQL does not provide a DbContext. Instead, you create a small database service responsible for:

• Dialect creation
• Table creation at startup
• Providing connections

    using SwiftSQL;
    using SwiftSQL.Dialects;
    using SwiftlyS2.Shared;
    using SwiftlyS2.Shared.Database;
    using System.Data;

    public sealed class PluginDatabase
    {
        private readonly IDatabaseService _dbService;
        private readonly string _connectionName;

        public ISqlDialect Dialect { get; }
        public bool IsEnabled { get; private set; }

        public PluginDatabase(
            ISwiftlyCore core,
            IDatabaseService dbService,
            string connectionName)
        {
            _dbService = dbService;
            _connectionName = connectionName;

            var driver =
                dbService.GetConnectionInfo(connectionName).Driver;

            Dialect = CreateDialect(driver);
        }

        public async Task InitializeAsync()
        {
            using var connection = Open();

            await Orm.CreateTableIfNotExistsAsync<PlayerModel>(
                connection,
                Dialect
            );

            await Orm.CreateTableIfNotExistsAsync<PreferenceModel>(
                connection,
                Dialect
            );

            IsEnabled = true;
        }

        public IDbConnection Open()
            => _dbService.GetConnection(_connectionName);

        private static ISqlDialect CreateDialect(string driver)
            => driver switch
            {
                "sqlite" => new SqliteDialect(),
                "mysql" => new MySqlDialect(),
                "postgresql" => new PostgresDialect(),
                _ => throw new NotSupportedException(
                    $"Unsupported database provider: {driver}")
            };
    }

Notes:

• Tables are created only if they do not exist
• Existing tables are never altered
• Safe to call on every plugin startup

4. SwiftlyS2 plugin integration

Using the official SwiftlyS2 plugin template:

    using Microsoft.Extensions.DependencyInjection;
    using SwiftlyS2.Shared.Plugins;
    using SwiftlyS2.Shared;

    namespace PluginId;

    [PluginMetadata(
        Id = "PluginId",
        Version = "1.0.0",
        Name = "PluginName",
        Author = "PluginAuthor",
        Description = "PluginDescription")]
    public partial class PluginId : BasePlugin
    {
        private PluginDatabase _database = null!;

        public PluginId(ISwiftlyCore core) : base(core) { }

        public override void Load(bool hotReload)
        {
            var dbService =
                Core.Services.GetRequiredService<IDatabaseService>();

            _database =
                new PluginDatabase(
                    Core,
                    dbService,
                    connectionName: "host");

            _database.InitializeAsync();
        }

        public override void Unload() { }
    }

5. Writing data

SwiftSQL does not track changes. Writes are always explicit.

    using var connection = _database.Open();

    var player = new PlayerModel
    {
        SteamId = steamId,
        Name = "Player",
        Group = 1,
        IsActive = true
    };

    await Orm.InsertOrUpdateAsync(
        connection,
        player,
        _database.Dialect
    );

InsertOrUpdate:

• Inserts if the primary key does not exist
• Updates if the primary key already exists

6. Reading data

Primary-key lookup:

    using var connection = _database.Open();

    var player =
        await Orm.GetAsync<PlayerModel>(
            connection,
            steamId,
            _database.Dialect
        );

Returns null if the row does not exist.

GetOrDefaultAsync (read-only, does not write to the database):

    using var connection = _database.Open();

    var prefs =
        await Orm.GetOrDefaultAsync<PreferenceModel>(
            connection,
            steamId,
            defaultFactory: () => new PreferenceModel
            {
                SteamId = steamId,
                Preferences = new Dictionary<string, object>
                {
                    ["showHints"] = true,
                    ["uiScale"] = 100
                },
                Updated = DateTime.UtcNow
            },
            _database.Dialect
        );

defaultFactory is only invoked when no row exists, and the default value is not inserted.

QueryAsync and Select.Where predicates support simple comparisons (==, !=, >, >=, <, ⇐) combined with && and || over mapped properties.

7. Updating data

Modify the object, then save it explicitly.

    player.IsActive = false;
    player.Expires = DateTime.UtcNow;

    await Orm.InsertOrUpdateAsync(
        connection,
        player,
        _database.Dialect
    );

There is no implicit update or change tracking.

8. Parallel reads (recommended pattern)

SwiftSQL is fully async and safe for parallel reads when the connection provider supports it.

    using var connection = _database.Open();

    var playerTask =
        Orm.GetAsync<PlayerModel>(
            connection,
            steamId,
            _database.Dialect);

    var prefsTask =
        Orm.GetAsync<PreferenceModel>(
            connection,
            steamId,
            _database.Dialect);

    await Task.WhenAll(playerTask, prefsTask);

Aggregates are created by your application, not the ORM.

What SwiftSQL intentionally does NOT do

• No DbContext
• No lazy loading
• No automatic migrations
• No schema repair
• No connection management

These are deliberate choices for SwiftlyS2 plugins.

Contributing

See CONTRIBUTING.md for the full guidelines.

Local build and test:

dotnet build
dotnet run -c Release --project SwiftSQL.Tests/SwiftSQL.Tests.csproj

License

MIT