SQLite in FunkyORM

The same FunkyORM package talks to SQLite. No second NuGet. One restore is enough because FunkyORM, a .NET micro-ORM, maps those same plain C# classes to SQL Server, PostgreSQL, MySQL, and SQLite. Version 3.5.0 is the release that put the file database on that package, so the classes you map for SQL Server are the classes you map for a file on disk. You restore Funcular.Data.Orm once, then point at a path when you want a file instead of a server.

The GitHub repo is where the SQLite provider lives in the same Funcular.Data.Orm tree; Introducing Funcular ORM is the query API from the top.

A file next to the exe

You point the provider at a file path or at :memory: for a database that lives as long as the process, and there is no server to provision — the connection string is the whole configuration, with the same provider and the same mapping. A desktop app can keep its library next to the exe, and Aperture Image Browser, a Windows image browser, does exactly that: the SQLite file sits beside the executable, and FunkyORM talks to it through the same provider you would point at SQL Server.

What this engine will not run

SQLite has no stored procedures, so ExecProcedure, ExecScalar, and ExecNonQuery throw an exception that names the engine instead of compiling a fake CALL. If you call them on this engine you will get that exception, not a result set you could map to a class.

Types, JSON, and one writer

JSON functions need SQLite 3.38 or later, and Microsoft.Data.Sqlite bundles that, so you are not hunting a system sqlite3; on an older engine those functions are simply missing and the statement fails. Type affinity is SQLite’s, not SQL Server’s (SQLite stores a value by what it looks like more than by the column’s declared type), so dates are TEXT and booleans are 0 and 1. If you assume datetime columns and bit flags, you will be wrong in a way that only shows up at the boundary, when a round-trip comes back as a string or an int instead of a DateTime or a bool.

WAL lets readers continue during a write, but you still have one writer, so a second write waits while a read during a write can still return rows — that is the engine, not a FunkyORM quirk.

The SQLite provider ships in Funcular.Data.Orm 3.5.0; if an Exec* call is what you wanted on a file database, the 3.5.0 release is the page that records that limit.