Database Providers

Multi-database support for SQLite, SQL Server, PostgreSQL, and MySQL

Overview

RoomSharp supports multiple database providers through a unified API. Switch providers with a single builder method call.

Supported Providers

Provider	NuGet Package	Status
SQLite	`RoomSharp`	✓ Fully Supported
SQL Server	`RoomSharp.SqlServer`	✓ Fully Supported
PostgreSQL	`RoomSharp.PostgreSql`	✓ Fully Supported
MySQL/MariaDB	`RoomSharp.MySql`	✓ Fully Supported

SQLite

Installation

Bash

dotnet add package RoomSharp

Configuration

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("app.db")  // File path
    .SetJournalMode(JournalMode.WAL)
    .EnableMultiInstanceInvalidation()
    .Build();

SQLite-Specific Features

Journal Modes: DELETE, WAL, MEMORY, OFF
Multi-Instance: Enable for concurrent access
Type Affinity: INTEGER, TEXT, REAL, BLOB, NULL

SQL Server

Installation

Bash

dotnet add package RoomSharp.SqlServer

Configuration

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlServer("Server=localhost;Database=MyApp;Trusted_Connection=True;")
    .Build();

PostgreSQL

Installation

Bash

dotnet add package RoomSharp.PostgreSql

Configuration

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UsePostgreSql("Host=localhost;Database=myapp;Username=postgres;Password=xxx")
    .Build();

MySQL/MariaDB

Installation

Bash

dotnet add package RoomSharp.MySql

Configuration

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseMySql("Server=localhost;Database=myapp;Uid=root;Pwd=xxx;")
    .Build();

Switching Providers

Switch providers by changing a single line:

// Development: SQLite
var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("dev.db")
    .Build();

// Production: PostgreSQL
var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UsePostgreSql(Configuration.GetConnectionString("Production"))
    .Build();

Provider-Specific Considerations

Boolean Handling

SQLite: Stored as INTEGER (0/1)
SQL Server: Native BIT type
PostgreSQL: Native BOOLEAN
MySQL: TINYINT(1)

Auto-Increment

SQLite: AUTOINCREMENT keyword
SQL Server: IDENTITY
PostgreSQL: SERIAL or GENERATED AS IDENTITY
MySQL: AUTO_INCREMENT

Best Practices

Use connection strings from configuration
Test with your target database provider
Be aware of SQL dialect differences
Use standard SQL when possible for portability
Consider using different providers for dev/test/prod

Next Steps

Installation - Install provider packages
Performance Notes - Provider performance tips
Error Handling - Provider-specific errors