Delly.DBunny.Sqlite 2026.5.7

Delly.DBunny.Sqlite

SQLite provider implementation for DBunny.

Installation

dotnet add package Delly.DBunny.Sqlite

Quick Start

using Delly.DBunny;
using Delly.DBunny.Sqlite;
using Delly.DBunny.Sql.Extension;
using System.Data.Common;

// Create provider
var provider = new SqliteProvider();
var connectionString = "Data Source=mydb.db;Pooling=False";

using var connection = provider.GetDbConnection(connectionString);
connection.Open();

// Create a table
var columnDescriptors = new List<DbColumnDesciptor>
{
    new DbColumnDesciptor { ColumnName = "Id", ColumnType = "INTEGER", PrimaryKeyFlag = true, NullableFlag = false },
    new DbColumnDesciptor { ColumnName = "Name", ColumnType = "TEXT(100)", PrimaryKeyFlag = false, NullableFlag = false },
    new DbColumnDesciptor { ColumnName = "Age", ColumnType = "INTEGER", PrimaryKeyFlag = false, NullableFlag = true }
};
var createTableSql = provider.SqlProvider.CreateTable(string.Empty, "Users", columnDescriptors);

using var createCommand = provider.GetDbCommand(connection);
createCommand.CommandText = createTableSql.Sql;
await createCommand.ExecuteNonQueryAsync();

// Insert data
var insertSql = new Sqled("INSERT INTO [Users] (Name, Age) VALUES (@name, @age)")
    .Set("name", "John Doe")
    .Set("age", 30);

using var insertCommand = provider.GetDbCommand(connection);
insertCommand.CommandText = insertSql.Sql;
provider.SetParameters(insertCommand, insertSql.Parameters);
await insertCommand.ExecuteNonQueryAsync();

// Query data
var selectSql = new Sqled("SELECT * FROM [Users] WHERE Age > @minAge")
    .Set("minAge", 18);

await provider.ReadAsync(connection, selectSql, async reader =>
{
    while (await reader.ReadAsync())
    {
        var id = reader["Id"];
        var name = reader["Name"];
        var age = reader["Age"];
        Console.WriteLine($"Id: {id}, Name: {name}, Age: {age}");
    }
});

Connection Builder

Use the fluent builder for connection configuration:

using Delly.DBunny.Sqlite;
using Delly.DBunny.Connecting.Extension;

var connectionDefine = new SqliteConnectionDefine()
    .WithDataSource("mydb.db")
    .WithPooling(false)
    .WithForeignKeys(true)
    .WithCacheSize(2000)
    .WithDefaultTimeout(30)
    .WithJournalMode("WAL");

var descriptor = connectionDefine.GetDbConnectionDescriptor(
    SqliteConnectionDefine.DATABASE_TYPE, "Default");

var connectionString = descriptor.ConnectionString;

Connection Parameters

SQLite Features

• No Database Layer: SQLite uses a file-based storage, no database operations
• No Schema Layer: SQLite doesn't use schemas
• Name Quoting: Uses square brackets [name]
• Parameter Prefix: @
• Type Mapping:
  • Boolean, Byte, SByte, Int16, UInt16, Int32, UInt32 → INTEGER
  • Int64, UInt64 → INTEGER
  • Single, Double, Decimal → REAL
  • String → TEXT
  • DateTime → TEXT

SQLite-Specific Notes

Journal Mode

SQLite supports several journal modes for transaction durability:

• Delete: Default mode, rollback journal is deleted at end of transaction
• Truncate: Journal is truncated instead of deleted
• Persist: Journal is kept but marked as invalid
• Memory: Journal is stored in RAM
• WAL: Write-Ahead Logging mode, allows concurrent readers and writers (recommended)
• Off: Disable rollback journal (dangerous, may corrupt database)

Foreign Keys

Foreign key constraints are disabled by default in SQLite. The connection builder enables them by default.

In-Memory Database

For temporary in-memory databases:

var connectionDefine = new SqliteConnectionDefine()
    .WithDataSource(":memory:");

Dependencies

• System.Data.SQLite.Core 1.0.119

License

MIT License