CSharpDB FAQ

General

What is CSharpDB?

CSharpDB is an embedded database engine written in C# for .NET. It runs in-process, stores data in a single .db file, and uses a WAL file for durability and crash recovery.

Does CSharpDB require a server process?

No. You open the database file directly from your application using the Engine API or ADO.NET provider.

Which .NET version is supported?

Current projects target .NET 10 (net10.0).

SQL and Schema

Do SQL scripts need GO separators?

No. Use semicolon-terminated SQL statements (;). GO is not required.

Can I query system metadata using SQL?

Yes. Query virtual catalog sources:

  • sys.tables
  • sys.columns
  • sys.indexes
  • sys.foreign_keys
  • sys.views
  • sys.triggers
  • sys.validation_rules
  • sys.temp_tables
  • sys.temp_columns
  • sys.objects

Underscored aliases are also supported (sys_tables, sys_columns, etc.).

Do sys.* objects appear as normal tables in the Admin sidebar?

No. They are virtual system catalog sources, so use the Query tab to query them.

How do I preview duplicates, validation failures, or orphaned rows?

Use the SQL-first data hygiene commands:

FIND DUPLICATES IN Customers ON Email COLLATE NOCASE;
VALIDATE TABLE Customers;
FIND ORPHANS IN Bookings;

FIND and VALIDATE commands return normal query rows and do not change data. Use DEDUP ... KEEP FIRST|LAST or MERGE DUPLICATES ... only when you want to apply transactional cleanup.

Why does index creation fail on some column types?

Current SQL secondary indexes support INTEGER, TEXT, and REAL columns. REAL indexes use hashed equality access only; they do not provide ordered/range access or satisfy ORDER BY. An INTEGER-tag value stored in a REAL-indexed column must be exactly representable within ±253, otherwise index creation or the write is rejected instead of rounded. COLLATE is only valid on TEXT index columns.

Which ALTER COLUMN TYPE conversions are supported?

The bounded rewrite path supports exact INTEGER to REAL and REAL to INTEGER conversions, including atomic rebuilding of affected ready SQL indexes. It also supports dependency-free TEXT to BLOB UTF-8 encoding and strict UTF-8 BLOB to TEXT decoding. TYPE BLOB clears the old TEXT collation, and BLOB-to-TEXT starts at default BINARY unless followed by SET COLLATION. Drop an incompatible old default before changing type and set the target default afterward. Indexed TEXT/BLOB columns and key, foreign-key, full-text, collection, or non-ready dependencies remain blocked.

Are foreign keys enforced?

Yes. CSharpDB supports column-level and table-level scalar/composite foreign keys with ordered primary/unique candidate-key validation, MATCH SIMPLE null behavior, and the full immediate RESTRICT, NO ACTION, CASCADE, SET NULL, and SET DEFAULT action matrix for both deletes and referenced-key updates. SET NULL requires every child-key column to be nullable and outside the primary key.

Current v1 boundary:

  • Constraints are enforced immediately.
  • MATCH SIMPLE allows a scalar NULL or any composite child tuple containing at least one NULL. A fully non-NULL tuple must match a parent candidate key.
  • NO ACTION remains metadata-distinct but has the same immediate restrictive behavior as RESTRICT.
  • SET DEFAULT uses each child column's persisted literal default, or NULL when no explicit default exists. The resulting tuple must satisfy nullability, checks, and uniqueness; a fully non-NULL tuple must also match a parent, while any NULL component satisfies MATCH SIMPLE.
  • Deferred constraints and MATCH FULL/PARTIAL are not implemented yet.

Will older databases automatically show foreign keys after I upgrade?

No. Opening an older database on a newer engine does not add FK metadata by itself.

Use the foreign-key retrofit migration workflow when you want to persist FK metadata onto existing tables:

  • ICSharpDbClient.MigrateForeignKeysAsync(...)
  • POST /api/maintenance/migrate-foreign-keys
  • csharpdb migrate-foreign-keys ...
  • .migrate-fks ... in the CLI REPL
  • the Admin Storage tab

Admin, CLI, and API

How do I run a sample SQL file quickly?

Use the CLI:

dotnet run --project src/CSharpDB.Cli -- mydata.db

Then:

csdb> .read samples/ecommerce-store/schema.sql

Can the Admin Query tab run multi-statement SQL?

Yes. Multiple semicolon-delimited statements are supported.

How do I report bugs or request features?

Use GitHub issue templates:

  • Bug report
  • Feature request
  • Documentation
  • Question

Security issues should be reported privately through GitHub Security Advisories.

Development

How do I build the repo?

dotnet build CSharpDB.slnx

How do I run tests?

This repository uses executable xUnit projects. Run tests with:

dotnet run --project tests/CSharpDB.Tests/CSharpDB.Tests.csproj --
dotnet run --project tests/CSharpDB.Data.Tests/CSharpDB.Data.Tests.csproj --
dotnet run --project tests/CSharpDB.Cli.Tests/CSharpDB.Cli.Tests.csproj --

Why do I see a .wal file next to my database?

That is expected. The WAL file stores recent committed changes before checkpointing into the main .db file.

Lifecycle details:

  • While the database is open, the .wal file exists.
  • A checkpoint copies committed pages back into the .db file and resets/truncates the WAL, but does not delete it while the DB is still open.
  • On a clean close/dispose, CSharpDB performs a final checkpoint (when needed) and deletes the .wal file.
  • If the process crashes or is terminated, the .wal file can remain on disk. On next open, CSharpDB recovers from it and continues normally.