SQLite

SQLite is the default scaffold for new projects. It runs without external services and is great for local development, CLI tools, and fast integration tests.

Prerequisites

• Drivers Overview
• Configuration

What You’ll Learn

• How to run SQLite with generated datasource helpers
• Which SQLite options matter for local dev and tests
• How env-based path configuration (DB_PATH) fits into scaffolded config

Step Outcome

By the end of this page, you should have:

• A bootable SQLite datasource (file or in-memory)
• Clear DB_PATH behavior for local runtime
• A known path for migration + test workflows

Install

Add the driver package:

dependencies:
  ormed: ^0.2.0
  ormed_sqlite: ^0.2.0

Configure

driver:
  type: sqlite
  options:
    database: database.sqlite   # relative to project root
    session:
      foreign_keys: true
      journal_mode: WAL
    init:
      - PRAGMA busy_timeout = 5000

Use this in ormed.yaml when you want explicit config-driven CLI/runtime wiring.
Code-first scaffolds already generate equivalent runtime options in lib/src/database/config.dart.

Use in code (DataSource)

The recommended way is the generated createDataSource() helper:

Generated helper

DataSource createDataSource({DataSourceOptions? options, String? connection}) {
  return DataSource(options ?? buildDataSourceOptions(connection: connection));
}

DataSource createDefaultDataSource({DataSourceOptions? options}) {
  return DataSource(options ?? buildDefaultDataSourceOptions());
}

Manual adapter

Future<DataSource> createSqliteDataSource() async {
  final dataSource = DataSource(
    DataSourceOptions(
      name: 'default',
      driver: SqliteDriverAdapter.file('database.sqlite'),
      entities: generatedOrmModelDefinitions,
    ),
  );
  await dataSource.init();
  return dataSource;
}

Code-first Helper APIs

When you want explicit programmatic setup (without manual adapter wiring), use:

• registry.sqliteFileDataSourceOptions(path: ..., name: ...)
• registry.sqliteFileDataSource(path: ..., name: ...)
• registry.sqliteInMemoryDataSourceOptions(name: ...)
• registry.sqliteInMemoryDataSource(name: ...)

SQLite Migrations Helpers

Import the SQLite migrations entrypoint to use SQLite-specific column aliases:

import 'package:ormed_sqlite/migrations.dart';

Available helpers:

• blob() → BLOB (alias for binary())
• real() → REAL (alias for float())
• numeric() → NUMERIC (alias for decimal())

In-memory (tests)

DataSource createInMemorySqliteDataSource() {
  return DataSource(
    DataSourceOptions(
      name: 'test',
      driver: SqliteDriverAdapter.inMemory(),
      entities: generatedOrmModelDefinitions,
    ),
  );
}

Options

Option	Type	Default	Description
database	String	database.sqlite	Path to the .sqlite file (relative to project root or absolute).
path	String?	—	Alias for database used by some programmatic helpers.
memory	bool	false	When true, connects to an in-memory database (tests / ephemeral usage).
session	Map<String, Object?>	{}	Session pragmas applied via PRAGMA key = value.
init	List<String>	[]	SQL statements executed after connecting.

Environment Variables

SQLite does not require network credentials. For scaffolded projects, generated config.dart reads:

• DB_PATH: SQLite file path (database/<package>.sqlite fallback by default)

If you keep ormed.yaml, interpolation is also supported:

• ${DB_PATH} and ${DB_PATH:-database/app.sqlite}

Quick Verification

1. Boot your app with generated datasource helpers.
2. Run a migration command.
3. Confirm the SQLite file appears at expected path (unless using in-memory mode).

Notes

• The adapter automatically enables RETURNING support and schema inspection.
• Migrations and seeds default to convention registries (lib/src/database/*) and can be overridden via ormed.yaml.
• For plan/previews/schema internals, see Drivers → Internals.

Extensions

Driver extensions let you register custom clause compilers for SQLite queries.

class SqliteCaseInsensitiveExtensions extends DriverExtension {
  const SqliteCaseInsensitiveExtensions();

  @override
  List<DriverExtensionHandler> get handlers => const [
    DriverExtensionHandler(
      kind: DriverExtensionKind.where,
      key: 'ci_equals',
      compile: _compileSqliteCaseInsensitive,
    ),
  ];
}

DriverExtensionFragment _compileSqliteCaseInsensitive(
  DriverExtensionContext context,
  Object? payload,
) {
  final data = payload as Map<String, Object?>;
  final column = context.grammar.wrapIdentifier(data['column'] as String);
  final placeholder = context.grammar.parameterPlaceholder();
  return DriverExtensionFragment(
    sql: 'LOWER(${context.tableIdentifier}.$column) = LOWER($placeholder)',
    bindings: [data['value']],
  );
}

Future<List<Map<String, Object?>>> searchDocumentsSqlite(
  DataSource dataSource,
  String query,
) {
  return dataSource.context.table('documents').whereExtension('ci_equals', {
    'column': 'title',
    'value': query,
  }).rows();
}

Future<DataSource> createSqliteExtensionDataSource() async {
  final dataSource = DataSource(
    DataSourceOptions(
      name: 'docs-sqlite-extensions',
      driver: SqliteDriverAdapter(database: 'database.sqlite'),
      entities: generatedOrmModelDefinitions,
      driverExtensions: const [SqliteCaseInsensitiveExtensions()],
    ),
  );
  await dataSource.init();
  return dataSource;
}

Read This Next

• PostgreSQL, MySQL, or D1 for non-local runtimes
• Guide: Testing