Using dapper with sqlite

Connection strings

# basic
Data Source=Application.db;Cache=Shared

# encrypted
Data Source=Encrypted.db;Password=MyEncryptionKey

# read only
Data Source=Reference.db;Mode=ReadOnly

# in-memory
Data Source=:memory:

# shareable in-memory
Data Source=Sharable;Mode=Memory;Cache=Shared

By setting Mode to memory, you can create an in-memory sqlite database. Mode=Memory;Cache=Shared will make the database shared across connections.

var masterSqliteConnection = new SqliteConnection(
  "Data Source=Scriveners;Mode=Memory;Cache=Shared"
  );
 masterSqliteConnection.Open();

var createCommand = connection.CreateCommand();
createCommand.CommandText =
    @"
    CREATE TABLE ServiceDefinitions (
        Name Text NOT NULL PRIMARY KEY,
        Release Text,
        Build Text,
        Team Text,
        Repository Text,
        Tier Text,
        Domain Text,
        SubDomain Text
    )";

createCommand.ExecuteNonQuery();

Btw, Sqlite query is case-sensitive by default. To do case-insensitive comparison, use COLLATE NOCASE

public async Task<HiveTeam> GetTeam(string name)
{
    await using var connection = new SqliteConnection(ScrivenerDatabase.ConnectionString);
    return await connection.QueryFirstOrDefaultAsync<HiveTeam>(
        $"SELECT * FROM {Tables.Teams} WHERE Name = @name COLLATE NOCASE",
        new {name});
}