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. The only differences are the configuration and the driver access property.

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['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),
  ],
);

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', descending: true))
    ..limit(20);

  return db.execute(query);
}

Insert

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

Update

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

Delete

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

Result Handling

db.execute() returns a SqlDatabaseResult. Iterate over .rows and access each column by name:

var result = await getAllBooks(app.sqliteDriver);

for (var row in result.rows) {
  var title = row.colByName('title')?.value ?? '';
}

// Total row count (useful for pagination)
var total = result.count;

Migrations

SQLite migrations are managed separately from MySQL migrations. Use the --sqlite flag:

# Create all tables defined in your migration files
dart run lib/app.dart migrate --init --sqlite

# Create a new SQLite migration file
dart run lib/app.dart migrate --create --name add_books_table --sqlite

See Database Migration for the migration file format and full workflow.

Configuration

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

Accessing the Driver

var driver = app.sqliteDriver; // DatabaseDriver<Database>

// Direct sqlite3.Database access
var db = app.sqliteDb;

Defining a Table

Identical to MySQL — use MTable and MField*:

import 'package:finch/finch_mysql.dart'; // MTable, MField* are shared
import 'package:finch/finch_ui.dart';

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),
  ],
);

Querying with Sqler

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', descending: true))
    ..limit(20);

  return db.execute(query);
}

Insert

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

Update

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

Delete

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

Result Handling

var result = await getAllBooks(app.sqliteDriver);

for (var row in result.rows) {
  var title = row.colByName('title')?.value ?? '';
}

var total = result.count;

Migrations

SQLite migrations are managed separately from MySQL migrations:

# Apply all pending SQLite migrations
dart run lib/app.dart migrate --init --sqlite

# Create a new SQLite migration file
dart run lib/app.dart migrate --create --name add_books_table --sqlite

See Database Migration for the migration file format.

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