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