Documentation:SQLite

SQLite

This page was created by Hans.karlsen on 2019-10-06. Last edited by Wikiadmin on 2026-07-29.

SQLite lets you use MDriven persistence with a file-based SQLite database, including schema evolution when a model change removes an attribute.

Use SQLite with MDriven persistence

Persistence mapping maps classes and attributes in your model to database tables and columns. SQLite is one of MDriven's built-in persistence mappers.

SQLite is a small, file-based database engine that is suitable when your application needs a local data store. For example, you can create a database file for a prototype, create the schema from the model, and persist objects to that file.

In the Prototyper, provide an SQLite connection string in this form:

Data Source=C:\Path\MySQLite.db

The value after Data Source= is the path to the SQLite database file. After the connection string has been validated, create the database schema. In the migration walkthrough, this creates the tables defined by the model; the example model creates 19 tables.

Evolve a SQLite schema when an attribute is removed

When you remove an attribute from a persistent class, the corresponding database change is to remove a column from its table. SQLite does not support removing a column with a column-by-column drop operation.

MDriven handles this SQLite limitation by recreating the affected table. The SQLite persistence mapper sets DropColumnsByRecreateTableTemplate by default. Working with Code and Persistence Mapping describes the configuration area in which persistence-mapping behavior can be tuned.

How table recreation works

For a table named Customer, assume that a model change removes the LegacyCode attribute. MDriven retains the columns that still belong to the model and performs the equivalent of these steps:

Create a backup table containing the columns to retain.
Copy the retained column values from the original table to the backup table.
Drop the original table.
Create the original table again with the retained columns.
Copy the retained values back into the recreated table.
Drop the backup table.

The default template is:

sb.AppendLine("CREATE TABLE <TableName>_backup(<KeepColumnsForCreate>);");
sb.AppendLine("INSERT INTO <TableName>_backup SELECT <KeepColumns> FROM <TableName>;");
sb.AppendLine("DROP TABLE <TableName>;");
sb.AppendLine("CREATE TABLE <TableName>(<KeepColumnsForCreate>);");
sb.AppendLine("INSERT INTO <TableName> SELECT <KeepColumns> FROM <TableName>_backup;");
sb.AppendLine("DROP TABLE <TableName>_backup;");
config.DropColumnTemplate = ""; // SQLite does not support removing column by column
config.DropIndexTemplate = "";
config.DropColumnsByRecreateTableTemplate = sb.ToString();

<TableName>, <KeepColumns>, and <KeepColumnsForCreate> are placeholders that MDriven replaces for the table being evolved. The backup table contains only columns that remain in the model. Therefore, data in the removed column is not copied back.

What DBEvolution does

DBEvolution compares the persistence mapping with the existing database schema and applies the database changes needed to bring the schema in line with the model. MDriven includes an additional DBEvolution pass for SQLite that detects when the configured recreate-table template must be used.

For example, if Customer.LegacyCode is removed from the model, DBEvolution uses the recreate-table path rather than attempting an unsupported SQLite column drop.

Recommended workflow for a column removal

Make the model change: remove the persistent attribute that is no longer required.
Work against a copy of the SQLite database when the database contains data you need to retain.
Run DBEvolution so that MDriven updates the SQLite schema.
Verify that the recreated table has the expected remaining columns and that existing values in those columns are present.
Verify the application behavior through the relevant views that show the data.

The removed column and its values are not retained by the recreate-table operation. Keep a database backup if you may need to inspect or recover those values.

Move data to an SQLite database

You can use the data migration tool to load data into an SQLite database after its schema has been created. A practical sequence is:

Configure and validate the SQLite Data Source connection string.
Create the schema from the model.
Use the migration tool to read the source data, such as a data dump, and save it to the SQLite database.
Start persistence against the SQLite database and use the debugger to verify the migrated data.

For a walkthrough of creating an SQLite schema and loading data, watch the data migration walkthrough.

Contents