Sqflite Common FFI

https://pub.dev/packages/sqflite_common_ffi
Admin
A Dart/Flutter package providing FFI-based SQLite database implementation for desktop, web, and unit...
Tokens:10,734
Snippets:109
Trust Score:9.9
Update:4 hours ago
Show doc for...
Tokens:
Raw
### Application example with path_provider

Source: https://pub.dev/packages/sqflite_common_ffi

Example using path_provider to get the application documents directory and create a database path.

```dart
import 'dart:io' as io;
import 'package:path/path.dart' as p;
import 'package:sqflite_common/sqlite_api.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:path_provider/path_provider.dart';

Future main() async {
  // Init ffi loader if needed.
  sqfliteFfiInit();

  var databaseFactory = databaseFactoryFfi;
  final io.Directory appDocumentsDir = await getApplicationDocumentsDirectory();
  
  //Create path for database
  String dbPath = p.join(appDocumentsDir.path, "databases", "myDb.db");
  var db = await databaseFactory.openDatabase(
    dbPath,
  );

  await db.execute('''
  CREATE TABLE Product (
      id INTEGER PRIMARY KEY,
      title TEXT
  )
  ''');
  await db.insert('Product', <String, Object?>{'title': 'Product 1'});
  await db.insert('Product', <String, Object?>{'title': 'Product 1'});

  var result = await db.query('Product');
  print(result);
  // prints [{id: 1, title: Product 1}, {id: 2, title: Product 1}]
  await db.close();
}

```

--------------------------------

### Linux apt install

Source: https://pub.dev/packages/sqflite_common_ffi

Alternative setup for Linux using apt-get.

```bash
sudo apt-get -y install libsqlite3-0 libsqlite3-dev

```

--------------------------------

### Linux setup script

Source: https://pub.dev/packages/sqflite_common_ffi

One-time setup for Ubuntu to run as root.

```bash
dart tool/linux_setup.dart

```

--------------------------------

### Initialize UI with buttons

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Adds buttons to trigger various operations: loading sqlite, incrementing variables, incrementing sqflite values in different contexts, handling exceptions, reading/writing files, and getting web options.

```dart
void initUi() {
  addButton('load sqlite', () async {});
  addButton('increment var in shared worker', () async {
    await incrementVarInSharedWorker();
  });
  addButton('increment var in basic worker', () async {
    await incrementVarInBasicWorker();
  });
  addButton('increment sqflite value in main thread', () async {
    await incrementNoWebWorker();
  });
  addButton('increment sqflite value in web worker', () async {
    await incrementWork();
  });
  addButton('exception in web worker', () async {
    await exceptionWork();
  });
  addButton('increment sqflite value in pre-built web worker', () async {
    await incrementPrebuilt();
  });
  addButton('read write file', () async {
    for (var factory in [
      databaseFactoryWebNoWebWorkerLocal,
      databaseFactoryWebPrebuilt,
      databaseFactoryWebLocal,
    ]) {
      try {
        write('factory: ${_tags[factory]}');
        await readWriteDatabase(factory, 3);
        await readWriteDatabase(factory, 1024 * 1024);
      } catch (e) {
        write('exception $e');
      }
    }
  });
  addButton('get web options', () async {
    for (var factory in [
      databaseFactoryWebNoWebWorkerLocal,
      databaseFactoryWebPrebuilt,
      databaseFactoryWebLocal,
      databaseFactoryWebBasicWorkerLocal,
    ]) {
      try {
        write('factory: ${_tags[factory]}');
        var options = await factory.getWebOptions();
        write(const JsonEncoder.withIndent('  ').convert(options.toMap()));
      } catch (e) {
        write('exception $e');
      }
    }
  });
}
```

--------------------------------

### example/main.dart

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Main example file for sqflite_common_ffi_web demonstrating database operations with web workers and big integer handling.

```dart
import 'dart:async';
import 'dart:convert';
import 'dart:math';
import 'dart:typed_data';

import 'package:sqflite_common/sqlite_api.dart';
import 'package:sqflite_common/utils/utils.dart';
import 'package:sqflite_common_ffi_web/sqflite_ffi_web.dart';
import 'package:sqflite_common_ffi_web/src/database_factory.dart';
import 'package:sqflite_common_ffi_web/src/sw/constants.dart';
import 'package:sqflite_common_ffi_web/src/web/load_sqlite_web.dart'
    show SqfliteFfiWebContextExt;
import 'package:web/web.dart' as web;

import 'ui.dart';

/// quick test open for basic web worker (as we cannot have both shared and simple worker)
var _useBasicWebWorker = false; // devWarning(true);

var _debugVersion = 1;
var _shc = '/_shc$_debugVersion';

var swOptions = SqfliteFfiWebOptions(
  sharedWorkerUri: Uri.parse('sw.dart.js'),
  // ignore: invalid_use_of_visible_for_testing_member
  forceAsBasicWorker: _useBasicWebWorker,
);
var swBasicOptions = SqfliteFfiWebOptions(
  sharedWorkerUri: Uri.parse('sw.dart.js'),
  // ignore: invalid_use_of_visible_for_testing_member
  forceAsBasicWorker: true,
);
Future incrementPrebuilt() async {
  await incrementSqfliteValueInDatabaseFactory(
    databaseFactoryWebPrebuilt,
    tag: 'prebuilt',
  );
}

Future incrementWork() async {
  await incrementSqfliteValueInDatabaseFactory(
    databaseFactoryWebLocal,
    tag: 'work',
  );
}

Future exceptionWork() async {
  try {
    var factory = databaseFactoryWebLocal;
    var db = await factory.openDatabase('test_missing');
    await db.query('Test');
  } catch (e) {
    write('exception $e');
  }
}

class _Data {
  late Database db;
}

/// Out internal data.
// ignore: library_private_types_in_public_api
final _Data data = _Data();

/// Get the value field from a given id
Future<dynamic> getValue(int id) async {
  return ((await data.db.query('Test', where: 'id = $id')).first)['value'];
}

/// insert the value field and return the id
Future<int> insertValue(dynamic value) async {
  return await data.db.insert('Test', {'value': value});
}

/// insert the value field and return the id
Future<int> updateValue(int id, dynamic value) async {
  return await data.db.update('Test', {'value': value}, where: 'id = $id');
}

Future bigInt() async {
  try {
    var factory = databaseFactoryWebLocal;
    var db = data.db = await factory.openDatabase('test_big_int');
    await db.execute(
      'CREATE TABLE IF NOT EXISTS Test (id INTEGER PRIMARY KEY AUTOINCREMENT, value INTEGER)',
    );
    await db.query('Test');
  } catch (e) {
    write('exception $e');
  }
  var id = await insertValue(-1);
  assert(await getValue(id) == -1);

  // less than 32 bits
  id = await insertValue(pow(2, 31));
  assert(await getValue(id) == pow(2, 31));

  // more than 32 bits
  id = await insertValue(pow(2, 33));
  //devPrint('2^33: ${await getValue(id)}');
  assert(await getValue(id) == pow(2, 33));

  id = await insertValue(pow(2, 62));
  //devPrint('2^62: ${pow(2, 62)} ${await getValue(id)}');
  assert(await getValue(id) == pow(2, 62));
  /*
  var value = pow(2, 63).round() - 1;
  id = await insertValue(value);
  //devPrint('${value} ${await getValue(id)}');
  expect(await getValue(id), value, reason: '$value ${await getValue(id)}');

  value = -(pow(2, 63)).round();
  id = await insertValue(value);
  //devPrint('${value} ${await getValue(id)}');
  expect(await getValue(id), value, reason: '$value ${await getValue(id)}');
  */
}

Future incrementNoWebWorker() async {
  await incrementSqfliteValueInDatabaseFactory(
    databaseFactoryWebNoWebWorkerLocal,
    tag: 'ui',
  );
}

Future<void> main() async {
  // sqliteFfiWebDebugWebWorker = true;
  initUi();

  write('$_shc running $_debugVersion');
  // devWarning(incrementVarInSharedWorker());
  // await devWarning(bigInt());
  // await devWarning(exceptionWork());
  // await devWarning(incrementWork());
  // await devWarning(incrementPrebuilt());
  // await incrementVarInServiceWorker();
  // await incrementSqfliteValueInDatabaseFactory(
  // databaseFactoryWebNoWebWorkerLocal);
  // await incrementSqfliteValueInDatabaseFactory(databaseFactoryWebLocal);
  // await devWarning(
  //  incrementSqfliteValueInDatabaseFactory(databaseFactoryWebPrebuilt));
}

var _webContextRegisterAndReady = sqfliteFfiWebStartSharedWorker(swOptions);

var _webBasicContextRegisterAndReady = sqfliteFfiWebStartSharedWorker(
  swBasicOptions,
);

Future<web.SharedWorker> sharedWorkerRegisterAndReady() async =>
    (await _webContextRegisterAndReady).sharedWorker!;

Future<SqfliteFfiWebContext> webContextRegisterAndReady() async =>
    (await _webContextRegisterAndReady);
```

--------------------------------

### Register and get basic web context

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Registers a basic web context and returns it when ready. Used to get a context for the basic worker.

```dart
Future<SqfliteFfiWebContext> webBasicContextRegisterAndReady() async =>
    (await _webBasicContextRegisterAndReady);
```

--------------------------------

### Map database factories to tags

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Maps each database factory to a string tag for identification in logs.

```dart
var _tags = {
  databaseFactoryWebNoWebWorkerLocal: 'no_web_worker',
  databaseFactoryWebPrebuilt: 'prebuilt',
  databaseFactoryWebLocal: 'local',
  databaseFactoryWebBasicWorkerLocal: 'basic_worker',
};
```

--------------------------------

### Install sqlite3.wasm binaries

Source: https://pub.dev/packages/sqflite_common_ffi_web

Run this command to install the required sqlite3.wasm binaries and the sqflite shared worker into your web folder.

```bash
$ dart run sqflite_common_ffi_web:setup
```

--------------------------------

### Get and set test value in context

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Sends raw messages to get and set a test value in the given context. Uses commandVarGet and commandVarSet commands.

```dart
var key = 'testValue';

Future<Object?> getTestValue(SqfliteFfiWebContext context) async {
  var response =
      await context.sendRawMessage([
            commandVarGet,
            {'key': key},
          ])
          as Map;
  return (response['result'] as Map)['value'] as Object?;
}

Future<void> setTestValue(SqfliteFfiWebContext context, Object? value) async {
  await context.sendRawMessage([
    commandVarSet,
    {'key': key, 'value': value},
  ]);
}
```

--------------------------------

### Application example

Source: https://pub.dev/packages/sqflite_common_ffi

Example of using sqflite_common_ffi in a Flutter/Dart application. Creates a table, inserts data, and queries it.

```dart
import 'package:sqflite_common/sqlite_api.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

Future main() async {
  // Init ffi loader if needed.
  sqfliteFfiInit();

  var databaseFactory = databaseFactoryFfi;
  var db = await databaseFactory.openDatabase(inMemoryDatabasePath);
  await db.execute('''
  CREATE TABLE Product (
      id INTEGER PRIMARY KEY,
      title TEXT
  )
  ''');
  await db.insert('Product', <String, Object?>{'title': 'Product 1'});
  await db.insert('Product', <String, Object?>{'title': 'Product 1'});

  var result = await db.query('Product');
  print(result);
  // prints [{id: 1, title: Product 1}, {id: 2, title: Product 1}]
  await db.close();
}

```

--------------------------------

### Define database factories for web

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Creates database factories for different web configurations: prebuilt, no web worker, local, and basic worker.

```dart
var databaseFactoryWebPrebuilt = databaseFactoryFfiWeb;
var databaseFactoryWebNoWebWorkerLocal = databaseFactoryFfiWebNoWebWorker;
var databaseFactoryWebLocal = createDatabaseFactoryFfiWeb(options: swOptions);
var databaseFactoryWebBasicWorkerLocal = createDatabaseFactoryFfiWeb(
  options: swBasicOptions,
);
```

--------------------------------

### main.dart: Open in-memory database, insert rows, and query

Source: https://pub.dev/packages/sqflite_common_ffi/example

Initializes the FFI loader with sqfliteFfiInit(), opens an in-memory database using databaseFactoryFfi, creates a Product table, inserts two rows, queries all rows, prints the result, and closes the database. The printed output is [{id: 1, title: Product 1}, {id: 2, title: Product 1}].

```dart
// ignore_for_file: avoid_print

import 'package:sqflite_common_ffi/sqflite_ffi.dart';

Future main() async {
  // Init ffi loader if needed.
  sqfliteFfiInit();

  var databaseFactory = databaseFactoryFfi;
  var db = await databaseFactory.openDatabase(inMemoryDatabasePath);
  await db.execute('''
  CREATE TABLE Product (
      id INTEGER PRIMARY KEY,
      title TEXT
  )
  ''');
  await db.insert('Product', <String, Object?>{'title': 'Product 1'});
  await db.insert('Product', <String, Object?>{'title': 'Product 1'});

  var result = await db.query('Product');
  print(result);
  // prints [{id: 1, title: Product 1}, {id: 2, title: Product 1}]
  await db.close();
}
```

--------------------------------

### Read and write database bytes

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Writes a byte array to a database file and reads it back, logging the sizes. Catches and logs exceptions.

```dart
Future<void> readWriteDatabase(DatabaseFactory factory, int size) async {
  try {
    var path = 'read_write.db';
    var bytes = Uint8List.fromList(List.generate(size, (index) => index % 256));
    await factory.writeDatabaseBytes(path, bytes);
    var readBytes = await factory.readDatabaseBytes(path);
    write('wrote ${bytes.length}, read ${readBytes.length}');
  } catch (e) {
    write('Exception $e');
  }
}
```

--------------------------------

### Unit test code

Source: https://pub.dev/packages/sqflite_common_ffi

Example unit test using sqflite_common_ffi. Initializes the FFI loader and opens an in-memory database.

```dart
import 'package:test/test.dart';
import 'package:sqflite_common/sqlite_api.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

void main() {
  // Init ffi loader if needed.
  sqfliteFfiInit();
  test('simple sqflite example', () async {
    var db = await databaseFactoryFfi.openDatabase(inMemoryDatabasePath);
    expect(await db.getVersion(), 0);
    await db.close();
  });
}

```

--------------------------------

### Increment sqflite value in database factory

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Opens a database, reads a value, increments it, and writes it back. Logs the before and after values and any errors.

```dart
Future<void> incrementSqfliteValueInDatabaseFactory(
  DatabaseFactory factory, {
  String? tag,
}) async {
  tag ??= 'db';
  try {
    write('/$tag accessing db...');
    // await factory.debugSetLogLevel(sqfliteLogLevelVerbose);
    var db = await factory.openDatabase(
      'test.db',
      options: OpenDatabaseOptions(
        version: 1,
        onCreate: (db, version) async {
          await db.execute(
            'CREATE TABLE Test(id INTEGER PRIMARY KEY, value INTEGER)',
          );
        },
      ),
    );
    Future<int?> readValue() async {
      var value =
          firstIntValue(
            await db.query('Test', columns: ['value'], where: 'id = 1'),
          ) ??
          0;

      return value;
    }

    var value = await readValue();
    write('/$tag read before $value');
    await db.insert('Test', {
      'id': 1,
      'value': (value ?? 0) + 1,
    }, conflictAlgorithm: ConflictAlgorithm.replace);
    value = await readValue() ?? 0;
    write('/$tag read after $value');
  } catch (e) {
    write('/$tag error: $e');
    rethrow;
  }
}
```

--------------------------------

### Increment variable in basic worker

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Increments a variable stored in the basic worker context and logs the before and after values.

```dart
Future<void> incrementVarInBasicWorker() async {
  var context = await webBasicContextRegisterAndReady();
  write('basic worker ready');
  var value = await getTestValue(context);
  write('var before $value');
  if (value is! int) {
    value = 0;
  }

  await setTestValue(context, value + 1);
  value = await getTestValue(context);
  write('var after $value');
}
```

--------------------------------

### Increment variable in shared worker

Source: https://pub.dev/packages/sqflite_common_ffi_web/example

Increments a variable stored in the shared worker context and logs the before and after values.

```dart
Future<void> incrementVarInSharedWorker() async {
  var context = await webContextRegisterAndReady();
  write('shared worker ready');
  var value = await getTestValue(context);
  write('var before $value');
  if (value is! int) {
    value = 0;
  }

  await setTestValue(context, value + 1);
  value = await getTestValue(context);
  write('var after $value');
}
```

--------------------------------

### Force reinstall of binaries

Source: https://pub.dev/packages/sqflite_common_ffi_web

Use the --force option to reinstall binaries when sqlite3 or its wasm binary are updated.

```bash
$ dart run sqflite_common_ffi_web:setup --force
```

--------------------------------

### Add sqflite_common_ffi_web dependency

Source: https://pub.dev/packages/sqflite_common_ffi_web

Add the sqflite_common_ffi_web package to your project's dependencies in pubspec.yaml.

```yaml
  dependencies:
    sqflite_common_ffi_web:
```

--------------------------------

### Add web support to existing sqflite application

Source: https://pub.dev/packages/sqflite_common_ffi_web

Change the default database factory on the web to use databaseFactoryFfiWeb and adjust the database path.

```dart
import 'package:sqflite_common_ffi_web/sqflite_ffi_web.dart';
import 'package:sqflite/sqflite.dart';

var path = '/my/db/path';
if (kIsWeb) {
  // Change default factory on the web
  databaseFactory = databaseFactoryFfiWeb;
  path = 'my_web_web.db';
}

// open the database
var db = openDatabase(path);
```

--------------------------------

### Use the ffi web factory

Source: https://pub.dev/packages/sqflite_common_ffi_web

Use databaseFactoryFfiWeb in web apps to open a database and query the SQLite version.

```dart
// Use the ffi web factory in web apps (flutter or dart)
var factory = databaseFactoryFfiWeb;
var db = await factory.openDatabase('my_db.db');
var sqliteVersion = (await db.rawQuery('select sqlite_version()')).first.values.first;
print(sqliteVersion); // should print 3.39.3
```

--------------------------------

### sqlite3 v3 support constraint

Source: https://pub.dev/packages/sqflite_common_ffi

Use this constraint for sqlite3 v3 support.

```yaml
dependencives:
  sqlite3: ^3.0.0
  sqflite_common_ffi: ^2.4.0

```

--------------------------------

### sqlite3 v2 support constraint

Source: https://pub.dev/packages/sqflite_common_ffi

Use this constraint for sqlite3 v2 support.

```yaml
dependencives:
  sqlite3: ^2.9.4
  sqflite_common_ffi: ^2.3.7

```

--------------------------------

### Add sqflite_common_ffi dev dependency

Source: https://pub.dev/packages/sqflite_common_ffi

Adds sqflite_common_ffi as a dev dependency for Dart projects.

```yaml
dev_dependencies:
  sqflite_common_ffi:

```

--------------------------------

### Dependency constraint with explicit sqlite3 version

Source: https://pub.dev/packages/sqflite_common_ffi/changelog

Use this YAML constraint to pin sqflite_common_ffi to ^2.3.7 and sqlite3 to ^2.9.4, ensuring compatibility with sqlite3 v2.

```yaml
  sqflite_common_ffi: ^2.3.7
  sqlite3: ^2.9.4
```

--------------------------------

### Dependency constraint for sqflite_common_ffi 2.3.7

Source: https://pub.dev/packages/sqflite_common_ffi/changelog

Use this YAML constraint to pin sqflite_common_ffi to version 2.3.7 or later but below 2.4.0, ensuring compatibility with sqlite3 v2.

```yaml
  sqflite_common_ffi: ">=2.3.7 <2.4.0"
```
Tokens:
Raw