<b012_data>
Flutter package — v2.1.0
2.1 New: web, encryption, transactions, migrations

b012_data, Object–Relational Mapping, the Spring Boot way for Dart / Flutter.

b012_data turns a plain Dart class into a persistent entity. CRUD statements, transactions, projections and file I/O all follow six simple conventions. No hand-written SQL. No repository boilerplate. Just Dart.

$ add to pubspec.yaml
b012_data: ^2.1.0
  • 01Zero SQL to maintain
  • 026 platforms, one API
  • 03Optional encryption
§ 00 · Overview

Four ideas. One package.

b012_data keeps the surface area narrow on purpose: a class is the schema, an instance is a row, and two singletons do the rest — DataAccess for the database, DiscData for files.

[01]

Spring-style entities

Declare a primary key, non-null columns, uniques, checks, defaults and foreign keys as plain getters. DataAccess turns them into SQL.

pKeyAuto · notNulls · uniques · fKeys
[02]

Every CRUD, for free

Insert, bulk-insert, read by predicate, project columns, update, delete and count with typed generic APIs.

insertObjet · get · getAll · update · delete
[03]

Runs wherever Flutter runs

The same calls on Android, iOS, macOS, Linux, Windows and in the browser: b012_data picks the SQLite engine for each platform.

Android · iOS · macOS · Linux · Windows · Web
[04]

Bytes on disk, metadata in SQLite

DiscData reads, writes, appends and resolves binary files as text, Base64, Uint8List or Flutter Image.

readFileAs* · saveDataToDisc · getEntityFileOnDisc
§ 2.1 · What’s new

Hardened, without changing how you use it.

Every 2.0.3 method keeps its name, its parameters and its default behaviour — including returning false, null or [] on failure. Everything new is opt-in: each card opens its part of the guide.

[01]

Bound values

? placeholders and whereArgs on every method that filters.

[02]

Strict mode

The real error, thrown or passed to onError, instead of false or [].

[03]

Transactions

Everything or nothing, across several calls.

[04]

Queries

Paging, sorting and raw SQL, with bound values.

[05]

Keys and indexes

Composite primary keys, indexes, INSERT OR REPLACE, enforced foreign keys.

[06]

Schema evolution

Stable table names, new columns added, versioned migrations.

[07]

Data location & files

One data folder per application on Windows and Linux; files written all at once.

[08]

Web

The same API in a browser, on SQLite compiled to WebAssembly.

[09]

Encryption

Encrypted database and files, never opened without the key.

Upgrading? Check the requirements, then the full changelog.

§ 01 · Setup

Installation

Add the dependency, import it, and you are ready: there is nothing to configure.

Add the package to your pubspec.yaml:

dependencies:
  b012_data: ^2.1.0

Then run:

flutter pub get

2.1 requires Dart 3.12 and Flutter 3.44 or later (the current sqflite packages do); an application on an older Flutter keeps resolving 2.0.3.

Import the library you need: b012_sqlflite_easy.dart for the database, b012_disc_data.dart for files.

import 'package:b012_data/b012_sqlflite_easy.dart'; // DataAccess, ColumnType, newKey
import 'package:b012_data/b012_disc_data.dart';     // DiscData, DataType

Before calling any method that needs the documents directory (databasesPath, filesPath, …), make sure the Flutter bindings are initialized:

void main() async {
  WidgetsFlutterBinding.ensureInitialized();
  runApp(const MyApp());
}
§ 02 · Conventions

Declaring an entity

An entity is a plain Dart class that exposes six well-known members. DataAccess reads them once, then generates every statement it needs.

  1. 1

    Primary key required

    A MapEntry whose key is the column name and whose value is true to use AUTOINCREMENT. For a primary key made of several columns, declare pKeys instead.

    MapEntry<String, bool> get pKeyAuto => const MapEntry('idPers', false);
  2. 2

    Constraints optional

    Non-null columns, uniques, checks, defaults and foreign keys are each expressed as a getter.

    List<String> get notNulls  => ['firstName', 'lastName', 'sex', 'dateOfBirth'];
    // List<String> get uniques => ['email'];
    // Map<String, String> get checks   => {'email': 'length(email) > 4'};
    // Map<String, String> get defaults => {'profession': 'NULL'};
    // Map<String, List<String>> get fKeys => {'profession': ['Profession', 'idProf']};
  3. 3

    Unnamed constructor required

    Used by the package to instantiate an entity from the fromMap callback below.

    Person([this.idPers, this.firstName, this.lastName, this.sex, this.dateOfBirth]);
  4. 4

    Serialization toMap required

    When a field is null, its ColumnType still tells the package the type of the column. It can be omitted for the notNulls fields, which are never null at insert time.

    Map<String, dynamic> toMap() => {
      'idPers': idPers ?? ColumnType.String,
      'firstName': firstName ?? ColumnType.String,
      'lastName': lastName ?? ColumnType.String,
      'sex': sex ?? ColumnType.bool,
      'dateOfBirth': dateOfBirth ?? ColumnType.DateTime,
    };
  5. 5

    Named fromMap constructor required

    Deserialization from a single SQLite row. SQLite has no boolean or date type: the boolean and dateTime helpers convert them back.

    Person.fromMap(Map<String, Object?> json, {bool isInt = true}) {
      idPers      = json['idPers']      as String?;
      firstName   = json['firstName']   as String?;
      lastName    = json['lastName']    as String?;
      sex         = boolean(json['sex'], isInt: isInt);
      dateOfBirth = dateTime(json['dateOfBirth'] as String?);
    }
  6. 6

    Instance-side fromMap required

    Wraps the named constructor so generic getters can call it from any instance.

    Person fromMap(Map<String, Object?> json) => Person.fromMap(json);
§ 03 · Runtime

The CRUD walk-through

Every call below is the high-level equivalent of a Spring Data JPA repository method, driven only through DataAccess.instance: at most, you write a WHERE condition.

Start by inspecting the schema generated from your entity, then check whether the table already exists:

// Inspect the CREATE TABLE statement generated for the Person entity.
debugPrint('${DataAccess.instance.showCreateTable(Person())}\n\n');

// Does the Person table already exist in the database?
final bool personTableExists =
    await DataAccess.instance.checkIfEntityTableExists<Person>();
debugPrint('Person table exists? $personTableExists\n');
a. Create

One row, or a whole list.

insertObjet inserts one entity, insertObjetList a list; both return a bool. When a row of the list fails, the rows before it are kept: call it inside a transaction for everything or nothing. newKey returns a random 32-character key.

// CREATE — insert a single Person row.
final bool inserted = await DataAccess.instance.insertObjet(
  Person(newKey, 'Birane', 'KEBE', true, DateTime(2000, 8, 5)),
);

// CREATE (bulk) — insert several Persons in one call.
final bool personsListInserted =
    await DataAccess.instance.insertObjetList(<Person>[
  Person(newKey, 'Aliou', 'Mbaye', true, DateTime(1999, 5, 1)),
  Person(newKey, 'Fatou', 'Cisse', false, DateTime(2000, 7, 9)),
]);
b. Read

One row, many rows, a single column, or several columns.

The condition goes in afterWhere, with a ? for each value, and the values go in whereArgs: a value is never pasted into the SQL. Booleans and dates are converted the way b012_data stores them.

// READ — fetch a single row.
final Person? birane = await DataAccess.instance.get<Person>(
  Person(),
  'firstName = ? AND lastName = ?',
  ['Birane', 'KEBE'],
);

// READ — fetch every row.
final List<Person>? everyone =
    await DataAccess.instance.getAll<Person>(Person());

// READ — fetch the rows matching a condition.
final List<Person>? men = await DataAccess.instance.getAllSorted<Person>(
  Person(),
  'sex = ?',
  [true],
);

Projection — single column or a set of columns:

// READ (projection) — a single column, on the whole table or filtered.
final List<String> firstNames =
    await DataAccess.instance.getAColumnFrom<String, Person>('firstName');

final List<String> womenFirstNames = await DataAccess.instance
    .getAColumnFrom<String, Person>('firstName',
        afterWhere: 'sex = ?', whereArgs: [false]);

// READ (projection) — several columns at once.
final List<Map<String, Object?>> nameRows = await DataAccess.instance
    .getSomeColumnsFrom<Person>('firstName, lastName');
c. Update

Values follow the order columnsToUpdate + whereColumns.

Here: set firstName = 'developer' and lastName = '2022' where firstName = 'Birane' AND lastName = 'KEBE'.

final bool updated = await DataAccess.instance.updateSomeColumnsOf<Person>(
  <String>['firstName', 'lastName'],
  <String>['firstName', 'lastName'],
  <Object>['developer', '2022', 'Birane', 'KEBE'],
);

The whereColumns are joined with AND; pass whereMcop: 'OR' to join them with OR.

d. Delete & count

Remove rows matching a predicate, then count what remains.

// DELETE — remove every row matching the condition.
final bool deletedFatou =
    await DataAccess.instance.deleteObjet<Person>('firstName = ?', ['Fatou']);

// COUNT — total rows and filtered rows.
final int total = await DataAccess.instance.countElementsOf<Person>();
final int males = await DataAccess.instance.countElementsOf<Person>(
  afterWhere: 'sex = ?',
  whereArgs: [true],
);
§ 04 · Disk

Files on disk

The DiscData singleton reads, writes, appends and checks files. It returns text, Base64, Uint8List or a Flutter Image widget.

Keep metadata in SQLite, keep the binary blob on disk, and resolve one from the other with a single call.

The Images entity used below, declared like Person
class Images {
  String? imageID;
  String? imageName;
  DateTime? dateSave;
  DateTime? dateLastUpdate;

  MapEntry<String, bool> get pKeyAuto => const MapEntry('imageID', false);

  List<String> get notNulls =>
      const ['imageName', 'dateSave', 'dateLastUpdate'];

  Images([this.imageID, this.imageName, this.dateSave, this.dateLastUpdate]);

  Map<String, dynamic> toMap() => {
    'imageID':        imageID        ?? ColumnType.String,
    'imageName':      imageName      ?? ColumnType.String,
    'dateSave':       dateSave       ?? ColumnType.DateTime,
    'dateLastUpdate': dateLastUpdate ?? ColumnType.DateTime,
  };

  Images.fromMap(Map<String, Object?> json, {bool isInt = true}) {
    imageID        = json['imageID']   as String?;
    imageName      = json['imageName'] as String?;
    dateSave       = dateTime(json['dateSave']       as String?);
    dateLastUpdate = dateTime(json['dateLastUpdate'] as String?);
  }

  Images fromMap(Map<String, Object?> json) => Images.fromMap(json);
}
a. System paths

Three directories exposed by the package.

final String databasesPath = await DiscData.instance.databasesPath; // SQLite files
final String filesPath     = await DiscData.instance.filesPath;     // user files
final String rootPath      = await DiscData.instance.rootPath;      // documents dir
b. Write

One call writes text, Base64 or raw bytes.

// Save a text file into the default `files` directory.
final String? savedName = await DiscData.instance.saveDataToDisc(
  'contents of test.txt',
  DataType.text,
  takeThisName: 'test.txt',
);

// Save an Image (Base64) to the default `files` directory.
final String? testImage = await DiscData.instance.saveDataToDisc(
  testImageAsBase64,
  DataType.base64,
  takeThisName: 'testImage.png',
);

// Store a matching metadata row for that image.
final bool imageRecordCreated = await DataAccess.instance.insertObjet(
  Images('img0001', 'testImage.png', DateTime.now(), DateTime.now()),
);
c. Read

As a string, Base64, bytes — or a Flutter widget.

// Exists?
final bool exists = await DiscData.instance.checkFileExists(fileName: 'test.txt');

// As a UTF-8 string.
final String? asString = await DiscData.instance.readFileAsString(fileName: 'test.txt');

// As Base64 — handy to ship blobs over JSON.
final String? asBase64 = await DiscData.instance.readFileAsBase64(fileName: 'testImage.png');

// As raw bytes — images, audio, video, PDFs, anything binary.
final Uint8List? asBytes = await DiscData.instance.readFileAsBytes(fileName: 'testImage.png');

// Straight into a Flutter `Image` widget.
final Image? asImage = await DiscData.instance.getImageFromDisc(imageName: 'testImage.png');
d. Database-backed resolution

Metadata in SQLite, blobs on disk, one call to bind them.

Given a table Images with a column imageName that holds a file name on disk, fetch the raw bytes of the row where imageID = 'img0001':

final Uint8List? entityBytes = await DiscData.instance
    .getEntityFileOnDisc<Uint8List, Images>(
        'imageName', 'imageID', 'img0001');
§ 05 · 2.1

Going further

Safe conditions, errors, transactions, keys, schema evolution, web and encryption. Each part stands alone: read only the one you need.

a. Bound values

Never build afterWhere from user input.

afterWhere is inserted as is after WHERE — that is what lets you add ORDER BY or LIMIT to it. Put a ? for each value and pass the values in whereArgs: a value with an apostrophe works, and a crafted value cannot inject SQL.

final List<Person>? recent = await DataAccess.instance.getAllSorted<Person>(
  Person(),
  'dateOfBirth >= ? ORDER BY lastName LIMIT 10',
  [DateTime(2000)],
);

whereArgs is positional after afterWhere in get, getAllSorted and deleteObjet, and named elsewhere. The queries the package builds itself (getLogin, checkIfTableExists, getEntityFileOnDisc, …) always bind their values. Table and column names that are SQL keywords (order, group, …) are quoted for you; in your own afterWhere, quote them yourself ('"group" = ?').

b. Errors

Strict mode, or just be told.

DataAccess.instance.strict = true;              // throw the real error
DataAccess.instance.onError = (e, s) => log(e); // or just be told

By default, a failing call returns false or [] and prints the error with debugPrint. With strict, it throws the real DatabaseException instead; “nothing found” is still null or []. Without strict, onError receives every error turned into false or [].

c. Transactions

Everything or nothing.

await DataAccess.instance.transaction(() async {
  await DataAccess.instance.insertObjet(order);
  await DataAccess.instance.updateSomeColumnsOf<Stock>(['qty'], ['id'], [qty - 1, id]);
});

Every DataAccess method called inside takes part in the transaction, and a transaction started inside another one joins it. Inside a transaction, a failing method throws (even without strict) instead of returning false, so that everything is rolled back.

d. Queries

Paged, sorted, bound — or raw SQL.

final List<Person> page = await DataAccess.instance.query<Person>(
  Person(),
  where: 'sex = ?',
  whereArgs: [true],
  orderBy: 'lastName',
  limit: 20,
  offset: 40,
); // [] when nothing matches
final rows = await DataAccess.instance.rawQuery('SELECT ... WHERE x = ?', [x]);
final people = await DataAccess.instance.rawQueryAs<Person>(Person(), 'SELECT ...');
final changed = await DataAccess.instance.rawExecute('UPDATE ... WHERE id = ?', [id]);

orderBy is inserted as is, like afterWhere: never from user input. rawQuery, rawQueryAs and rawExecute bind their ? placeholders too; rawExecute returns the number of rows the statement changed.

e. Keys and indexes

Replace, composite keys, indexes, foreign keys.

await DataAccess.instance.insertObjet(person, orReplace: true); // INSERT OR REPLACE

With orReplace, a row that has the same key is replaced. Three optional entity getters declare a primary key made of several columns (in place of pKeyAuto), indexes and unique indexes; missing indexes are created, also on an existing table:

List<String> get pKeys => ['maintenanceId', 'technicianId'];   // composite key
Map<String, List<String>> get indexes => {'assignment_trade': ['trade']};
Map<String, List<String>> get uniqueIndexes => {'one_trade': ['maintenanceId', 'trade']};

The foreign keys declared in fKeys are enforced only when enforceForeignKeys is set before the first access (PRAGMA foreign_keys = ON). It is off by default, because enforcing them could break an application that inserts a child before its parent.

DataAccess.instance.enforceForeignKeys = true;
f. Schema evolution

Stable table names, new columns, migrations.

A table is named after its class. Obfuscated builds (and web release builds) rename classes, so register the entities whose table name must not change; every method follows the registration, including those that only receive the type. In a browser, registration is required.

DataAccess.instance.registerEntity<Person>('person');

With autoAddColumns (off by default), a field added to an entity becomes a new column of its table; columns are never dropped or renamed. Each migration runs once, in order, when the database opens, in its own transaction that sets PRAGMA user_version; a failing step is rolled back and makes the opening fail. Set both before the first access.

DataAccess.instance.autoAddColumns = true;
DataAccess.instance.migrations = {
  2: () => DataAccess.instance.rawExecute('UPDATE person SET sex = 0 WHERE sex IS NULL'),
  3: () async { /* ... */ },
};
g. Where your data lives

One folder per application, files written safely.

  • Android, iOS, macOS: the databases folder of the platform, as in 2.0.3.
  • Windows, Linux: databases, dbName and files go to the application support folder. A database created by 2.0.3 under the current directory (.dart_tool/sqflite_common_ffi/databases) is found and kept where it is, so nothing disappears.
  • changeDB creates the folder it needs.
  • A file is written to a temporary file, flushed, then renamed over the target: a reader never sees half of it.
  • A file name cannot point outside filesPath ('../x' throws an ArgumentError); explicit absolute paths work as before.
h. Web

The same API, in a browser.

dart run b012_data:setup_web   # puts sqlite3mc.wasm and sqlite3.wasm in web/
  • SQLite runs as WebAssembly (sqlite3mc.wasm by default; set DataAccess.instance.webSqliteWasmUri to use another module). Databases and DiscData files live in IndexedDB; persistent storage is requested.
  • Every write is stored in IndexedDB before its future completes.
  • One tab at a time: a second tab opening the same database gets a clear StateError instead of a copy that would overwrite the first. This needs the Web Locks API, available on https and localhost.
  • Entities must be registered.
  • DataAccess.db and openDB (sqflite objects) are not available; DiscData.getFile returns null (there is no file system).
  • A browser can still erase site data when the user clears it.
i. Encryption

Against a lost or stolen device.

The database and the files saved by name can be encrypted with SQLite3MultipleCiphers (MIT licence). Choose this engine in your application’s pubspec.yaml (in a browser, setup_web already provides sqlite3mc.wasm):

hooks:
  user_defines:
    sqlite3:
      source: sqlite3mc
await DataAccess.instance.useEncryptionKey(key);      // before the first access
await DataAccess.instance.encryptDatabase(key);       // encrypt an existing plaintext database in place
await DataAccess.instance.changeEncryptionKey(newKey);
  • A new database is created encrypted; a wrong key throws a StateError.
  • The engine is checked first: if it does not encrypt, a StateError says what to add to pubspec.yaml. A database is never created in plaintext by mistake.
  • A key given for an existing plaintext database is refused (use encryptDatabase); an encrypted database is never opened without its key.
  • Files saved by name with DiscData are encrypted (AES-256-GCM) with a random key kept inside the encrypted database, so changing the database key rewrites no file. Files written at an explicit path stay plain unless saveDataToDisc(..., encrypt: true).
  • encryptDatabase also encrypts the files DiscData saved by name and the names you pass in alsoFiles; nothing else in the files folder is touched.
  • On Android and iOS, an encrypted database goes through the engine bundled by package:sqlite3, not the system SQLite.
  • b012_data never stores the key: your application provides it at each start. On a device, keep a random key in the system vault (Keystore, Keychain, Windows data protection); in a browser there is no vault, so the key must come from something the user types.
§ 06 · Edge cases

Things to know

Two subtleties that save debugging time later.

01

Missing-table exceptions

Most entity-oriented methods throw DatabaseException('no such table: ...') when the table does not exist. updateWholeObject, updateSomeColumnsOf, getAColumnFromWithTableName and getAColumnFrom only log the error through debugPrint and onError — unless strict is set.

02

Dart map vs. SQLite-ready map

insertObjet and insertObjetList convert your entity map with mapToUse(entity.toMap()). Call mapToUse(entity.toMap(), forDB: false) when you need a plain Dart map.

§ 07 · Sandbox

Full worked example

The example/ folder holds one self-contained, runnable file per topic.

  • Spring Boot-style walk-through built around the Person entity and DataAccess.instance.

    flutter run -t example/sqlite_example.dart
  • Binary files (text, Base64, raw bytes, Image) built around DiscData.instance and the Images entity.

    flutter run -t example/disc_example.dart
  • The 2.1 additions: registration, bound values, transactions, queries, replace.

    flutter run -t example/whats_new_example.dart
  • example/main.dart Entry point

    Runs the three demos in sequence.

    flutter run -t example/main.dart
§ 08 · Reach

One import. Six targets.

Same code everywhere Flutter runs — native SQLite on mobile, FFI on desktop, WebAssembly in the browser.

Tested for 2.1 on Windows (unit tests, real SQLite3MultipleCiphers), in Chrome (the check application in tool/web_check) and on an Android emulator. iOS, macOS and Linux share the same code paths.

§ 09 · Ship it

Add a line. Delete a thousand.

Stop writing repositories and data-access objects. Declare an entity, import the singletons, and move on to the part of your app that matters.