SQLite

Finch uses the sqlite3 package for SQLite. SQLite is a file-based database — there is no separate database server to manage. All data is stored in a single file on disk.

SQLite is a good choice when:

• You are building a small or medium application that doesn't need high concurrency
• You want zero-dependency deployment (no external database server required)
• You are developing locally and want a fast setup

The SQLite integration in Finch uses the same MTable, MField*, and Sqler API as MySQL (see MySQL for the full schema/query-builder reference) — the same Sqler call produces valid SQL for whichever database DatabaseDriver is actually holding, since DatabaseDriver detects the underlying connection type at runtime. The differences are: the configuration, the driver access property, and — importantly — how you read values back out of a result set (see Result Handling below).

Configuration

Add sqliteConfig to FinchConfigs. The only required parameter is the path to the .sqlite file:

FinchConfigs configs = FinchConfigs(
  sqliteConfig: FinchSqliteConfig(
    enable: true,
    filePath: env.get('SQLITE_PATH', './app.sqlite'),
  ),
);

The file is created automatically if it doesn't exist. The path can be absolute or relative to the working directory.

Accessing the Driver

// DatabaseDriver<Database> — use this for Sqler queries
var driver = app.sqliteDriver;

// Direct sqlite3.Database — use only when you need low-level access
var db = app.sqliteDb;

Use app.sqliteDriver in your data layer classes. Pass it as a constructor argument to keep controllers thin:

class BooksData {
  final DatabaseDriver db;
  BooksData(this.db);

  // your query methods...
}

// In a controller:
var books = BooksData(app.sqliteDriver);

Defining a Table

Table schemas are defined identically to MySQL using MTable and MField*. Finch imports both from finch_mysql.dart — they are shared between MySQL and SQLite:

import 'package:finch/finch_mysql.dart'; // MTable, MField* are shared by MySQL and SQLite
import 'package:finch/finch_ui.dart';    // FieldValidator

final table = MTable(
  name: 'books',
  fields: [
    MFieldInt(name: 'id', isPrimaryKey: true, isAutoIncrement: true, isNullable: false),
    MFieldVarchar(
      name: 'title',
      isNullable: false,
      validators: [
        FieldValidator.requiredField().toSimple(),
        FieldValidator.fieldLength(min: 3, max: 255).toSimple(),
      ],
    ),
    MFieldVarchar(name: 'author', isNullable: false),
    MFieldDate(name: 'published_date', isNullable: false),
    MFieldInt(name: 'category_id', isNullable: true),
  ],
);

MFieldInt maps to SQLite's INTEGER automatically when the SQL is generated for a SQLite connection (toSQL<Sqlite>() vs toSQL<Mysql>()) — you don't need a SQLite-specific field type. See MySQL — Available Field Types for the full list, including MFieldBoolean (not MFieldBool) and MFieldDecimal/MFieldFloat (there is no MFieldDouble).

Querying with Sqler

Queries are built with the same Sqler fluent API as MySQL. Finch handles the SQL dialect differences internally:

import 'package:finch/finch_mysql.dart';

Future<SqlDatabaseResult> getAllBooks(DatabaseDriver db) async {
  var query = Sqler()
    ..from(QField(table.name, as: 'b'))
    ..selects([
      QSelect('b.id'),
      QSelect('b.title'),
      QSelect('b.author'),
    ])
    ..orderBy(QOrder('b.id', desc: true))
    ..limit(20);

  return db.execute(query);
}

Insert

Sqler.insert() takes the target table and a list of row maps:

await db.execute(
  Sqler().insert(QField(table.name), [
    {'title': QVar('Dart in Action'), 'author': QVar('Alice')},
  ]),
);

Update

Use .update(table), then .updateSet(field, value) per field, then .where():

await db.execute(
  Sqler()
    ..update(QField(table.name))
    ..updateSet('title', QVar('Updated Title'))
    ..where(WhereOne(QField('id'), QO.EQ, QVar(1))),
);

Delete

Combine .delete() with .from():

await db.execute(
  Sqler()
    ..delete()
    ..from(QField(table.name))
    ..where(WhereOne(QField('id'), QO.EQ, QVar(1))),
);

The same MTable convenience methods and the SqliteTable repository base class (mirroring MysqlTable, with deleteBy/deleteById/findById/countBy) are available too — see MySQL — Table Convenience Methods for the full method list; everything there works unchanged against app.sqliteDriver.

Result Handling

This is the one place SQLite genuinely differs from MySQL: a SQLite SqlDatabaseResult.rows is a plain List<List<Object?>> (positional values per row) — not objects with a .colByName() method like the MySQL result rows have. Always use .assoc/.assocFirst to read columns by name instead of indexing into .rows by position:

var result = await getAllBooks(app.sqliteDriver);

for (var row in result.assoc) {
  var title = row['title']; // Map<String, String?> — every value comes back as a String
}

var firstRow = result.assocFirst; // Map<String, String?>? — first row or null
var numRows  = result.numRows;
var newId    = result.insertId;     // sqlite3's last insert row id
var affected = result.affectedRows; // rows changed by the last statement

Note: unlike MySQL's .assoc (which preserves native Dart types), SQLite's .assoc/.assocFirst stringify every value (row[i]?.toString()). Parse numbers/booleans back out explicitly if you need them typed (e.g. int.parse(row['id']!)).

For a total-row-count pagination query, alias COUNT(*) as count_records and read .countRecords, exactly as with MySQL:

var countQuery = Sqler()..from(table.qName)..addSelect(SQL.count(QField('id', as: 'count_records')));
var total = (await table.execute(driver, countQuery)).countRecords;

Migrations

SQLite migrations are tracked separately from MySQL migrations, with their own dedicated app-runtime command, migrate_sqlite:

# Apply all pending SQLite migrations
finch run --args="migrate_sqlite --init"

# Create a new SQLite migration file
finch migrate --create --name add_books_table --sqlite

finch migrate --init --sqlite (the combined MySQL command with a --sqlite flag) also applies pending SQLite migrations, in the same call as MySQL's — but only for --init. migrate --rollback --sqlite and migrate --list --sqlite silently ignore the --sqlite flag and act on MySQL only; use migrate_sqlite --rollback / migrate_sqlite --list for those. See Database Migration for the full command reference and migration file format.

SQLite is ideal for development, testing, and small production deployments that don't require a separate database server.