CloudTheWolf.DSharpPlus.Scaffolding.Data 3.4.0.1

CloudTheWolf.DSharpPlus.Scaffolding.Data

Database providers for CloudTheWolf DSharpPlus workers and plugins.

Supported SQL providers are MySQL, SQL Server, PostgreSQL, SQLite, SurrealDB, and Supabase PostgreSQL. Firebase Cloud Firestore is exposed separately through the document-oriented IDocumentDatabase API.

SQL databases

Existing synchronous calls remain supported. New code can use typed, cancellation-aware asynchronous methods:

IDatabase database = DatabaseFactory.CreateDatabase(configuration);

IEnumerable<User> users = await database.QueryAsync<User>(
    "select id, name from users where active = @active",
    new { active = true },
    cancellationToken);

Set Database:type to mysql, sqlsrv, pgsql, sqlite, surreal, or supabase.

MySQL

{
  "Database": {
    "type": "mysql",
    "mysql": {
      "server": "localhost",
      "port": 3306,
      "userid": "bot_user",
      "password": "use-a-secret-provider",
      "database": "discord_bot"
    }
  }
}

IDatabase database = DatabaseFactory.CreateDatabase(configuration);

IEnumerable<GuildSettings> settings = await database.QueryAsync<GuildSettings>(
    "SELECT guild_id AS GuildId, prefix AS Prefix FROM guild_settings WHERE guild_id = @guildId",
    new { guildId },
    cancellationToken);

Microsoft SQL Server

{
  "Database": {
    "type": "sqlsrv",
    "sqlsrv": {
      "server": "localhost",
      "port": 1433,
      "userid": "bot_user",
      "password": "use-a-secret-provider",
      "database": "DiscordBot"
    }
  }
}

IDatabase database = DatabaseFactory.CreateDatabase(configuration);

int affected = await database.ExecuteAsync(
    "UPDATE guild_settings SET prefix = @prefix WHERE guild_id = @guildId",
    new { prefix = "!", guildId },
    cancellationToken);

PostgreSQL

{
  "Database": {
    "type": "pgsql",
    "pgsql": {
      "server": "localhost",
      "port": 5432,
      "userid": "bot_user",
      "password": "use-a-secret-provider",
      "database": "discord_bot"
    }
  }
}

IDatabase database = DatabaseFactory.CreateDatabase(configuration);

IEnumerable<GuildSettings> settings = await database.QueryAsync<GuildSettings>(
    "SELECT guild_id AS GuildId, prefix AS Prefix FROM guild_settings WHERE guild_id = @guildId",
    new { guildId },
    cancellationToken);

SQLite

{
  "Database": {
    "type": "sqlite",
    "sqlite": {
      "datasource": "data/discord-bot.db",
      "mode": "ReadWriteCreate",
      "password": ""
    }
  }
}

IDatabase database = DatabaseFactory.CreateDatabase(configuration);

await database.ExecuteAsync(
    "CREATE TABLE IF NOT EXISTS guild_settings (guild_id TEXT PRIMARY KEY, prefix TEXT NOT NULL)",
    cancellationToken: cancellationToken);

SurrealDB

{
  "Database": {
    "type": "surreal",
    "surreal": {
      "server": "http://127.0.0.1:8000",
      "userid": "root",
      "password": "use-a-secret-provider",
      "namespace": "discord",
      "database": "bot"
    }
  }
}

SurrealDB accepts SurrealQL and named parameters:

IDatabase database = DatabaseFactory.CreateDatabase(configuration);

IEnumerable<GuildSettings> settings = await database.QueryAsync<GuildSettings>(
    "SELECT * FROM guild_settings WHERE guild_id = $guildId",
    new { guildId },
    cancellationToken);

await database.ExecuteAsync(
    "UPSERT guild_settings SET guild_id = $guildId, prefix = $prefix WHERE guild_id = $guildId",
    new { guildId, prefix = "!" },
    cancellationToken);

Supabase

Copy a direct or session-pooler PostgreSQL connection string from the Supabase dashboard:

{
  "Database": {
    "type": "supabase",
    "supabase": {
      "connectionString": "Host=db.PROJECT.supabase.co;Port=5432;Username=postgres;Password=...;Database=postgres;SSL Mode=Require"
    }
  }
}

ConnectionStrings:Supabase is also supported. Keep connection strings in user secrets, environment-specific configuration, or another secret store rather than source control.

Supabase uses the same typed SQL methods as PostgreSQL:

IDatabase database = DatabaseFactory.CreateDatabase(configuration);

IEnumerable<GuildSettings> settings = await database.QueryAsync<GuildSettings>(
    "SELECT guild_id AS GuildId, prefix AS Prefix FROM guild_settings WHERE guild_id = @guildId",
    new { guildId },
    cancellationToken);

Firebase Cloud Firestore

Firestore models use the attributes provided by Google.Cloud.Firestore:

[FirestoreData]
public sealed class GuildSettings
{
    [FirestoreProperty]
    public string Prefix { get; set; }
}

Create and use the document provider as follows:

IDocumentDatabase database = DatabaseFactory.CreateDocumentDatabase(configuration);

await database.SetAsync("guild-settings", guildId, settings, merge: true,
    cancellationToken);

DatabaseDocument<GuildSettings> stored = await database.GetAsync<GuildSettings>(
    "guild-settings", guildId, cancellationToken);

IReadOnlyList<DatabaseDocument<GuildSettings>> allSettings =
    await database.GetCollectionAsync<GuildSettings>("guild-settings", cancellationToken);

string newDocumentId = await database.AddAsync(
    "guild-settings", new GuildSettings { Prefix = "?" }, cancellationToken);

await database.DeleteAsync("guild-settings", newDocumentId, cancellationToken);

Configuration:

{
  "Database": {
    "type": "firestore",
    "firebase": {
      "projectId": "my-firebase-project",
      "databaseId": "",
      "detectEmulator": false
    }
  }
}

Firestore uses Google Application Default Credentials. In local or self-hosted environments, set GOOGLE_APPLICATION_CREDENTIALS to a trusted service-account credential file. On Google Cloud, use the workload's attached service account. Set detectEmulator to true when the same build should honor FIRESTORE_EMULATOR_HOST.