moor ffi (Desktop support)

Supported platforms

The moor/ffi.dart library uses the sqlite3 package to send queries. At the moment, that package supports iOS, macOS and Android out of the box. Most Linux Distros have sqlite available as a shared library, those are supported as well.

If you’re shipping apps for Windows and Linux, it is recommended that you bundle a sqlite3.so and sqlite3.dll file with your app. You can then make moor_ffi support your setup by running this code before opening the database:

import 'dart:ffi';
import 'dart:io';
import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite3/open.dart';

void main() {
  open.overrideFor(OperatingSystem.linux, _openOnLinux);

  final db = sqlite3.openInMemory();
  db.dispose();
}

DynamicLibrary _openOnLinux() {
  final script = File(Platform.script.toFilePath());
  final libraryNextToScript = File('${script.path}/sqlite3.so');
  return DynamicLibrary.open(libraryNextToScript.path);
}
// _openOnWindows could be implemented similarly by opening `sqlite3.dll`

Migrating from moor_flutter to moor ffi

First, adapt your pubspec.yaml: You can remove the moor_flutter dependency and instead add both the moor and sqlite3_flutter_libs dependencies:

dependencies:
 moor: ^3.0.0
 sqlite3_flutter_libs:
 sqflite: ^1.1.7 # Still used to obtain the database location
dev_dependencies:
 moor_generator: ^3.0.0

Adapt your imports:

• In the file where you created a FlutterQueryExecutor, replace the moor_flutter import with package:moor/ffi.dart.
• In all other files where you might have imported moor_flutter, just import package:moor/moor.dart.
  

Replace the executor. This code:

FlutterQueryExecutor.inDatabaseFolder(path: 'db.sqlite')

can now be written as

import 'package:sqflite/sqflite.dart' show getDatabasesPath;
import 'package:path/path.dart' as p;

LazyDatabase(() async {
  final dbFolder = await getDatabasesPath();
  final file = File(j.join(dbFolder, 'db.sqlite'));
  return VmDatabase(file);
})

Note: If you haven’t shipped a version with moor_flutter to your users yet, you can drop the dependency on sqflite. Instead, you can use path_provider which works on Desktop. Please be aware that FlutterQueryExecutor.inDatabaseFolder might yield a different folder than path_provider on Android. This can cause data loss if you’ve already shipped a version using moor_flutter. In that case, using getDatabasePath from sqflite is the suggested solution.

Using moor ffi with an existing database

If your existing sqlite database is stored as a file, you can just use VmDatabase(thatFile) - no further changes are required.

If you want to load databases from assets or any other source, you can use a LazyDatabase. It allows you to perform some async work before opening the database:

// before
VmDatabase(File('...'));

// after
LazyDatabase(() async {
  final file = File('...');
  if (!await file.exists()) {
    // copy the file from an asset, or network, or any other source
  }
  return VmDatabase(file);
});

Used compile options on Android

Note: Android is the only platform where moor_ffi will compile sqlite. The sqlite3 library from the system is used on all other platforms. The chosen options help reduce binary size by removing features not used by moor. Important options are marked in bold.

• We use the -O3 performance option
• SQLITE_DQS=0: This will make sqlite not accept double-quoted strings (and instead parse them as identifiers). This matches the behavior of moor and compiled queries
• SQLITE_THREADSAFE=0: Since the majority of Flutter apps only use one isolate, thread safety is turned off. Note that you can still use the isolate api for background operations. As long as all database accesses happen from the same thread, there’s no problem.
• SQLITE_DEFAULT_MEMSTATUS=0: The sqlite3_status() interfaces are not exposed by moor_ffi, so there’s no point of having them.
• SQLITE_MAX_EXPR_DEPTH=0: Disables maximum depth when sqlite parses expressions, which can make the parser faster.
• SQLITE_OMIT_AUTHORIZATION, SQLITE_OMIT_DECLTYPE, SQLITE_OMIT_DEPRECATED, SQLITE_OMIT_GET_TABLE, SQLITE_OMIT_LOAD_EXTENSION, SQLITE_OMIT_PROGRESS_CALLBACK, SQLITE_OMIT_SHARED_CACHE, SQLITE_OMIT_TCL_VARIABLE, SQLITE_OMIT_TRACE: Disables features not supported by moor.
• SQLITE_USE_ALLOCA: Allocate temporary memory on the stack
• SQLITE_UNTESTABLE: Remove util functions that are only required to test sqlite3
• SQLITE_HAVE_ISNAN: Use the isnan function from the system instead of the one shipped with sqlite3.
• SQLITE_ENABLE_FTS5: Enable the fts5 engine for full-text search.
• SQLITE_ENABLE_JSON1: Enable the json1 extension for json support in sql query.

For more details on sqlite compile options, see their documentation.

Moor-only functions

moor_ffi includes additional sql functions not available in standard sqlite:

• pow(base, exponent) and power(base, exponent): This function takes two numerical arguments and returns base raised to the power of exponent. If base or exponent aren’t numerical values or null, this function will return null. This function behaves exactly like pow in dart:math.
• sqrt, sin, cos, tan, asin, acos, atan: These functions take a single argument. If that argument is null or not a numerical value, returns null. Otherwise, returns the result of applying the matching function in dart:math.
• regexp: Wraps the Dart RegExp apis, so that foo REGEXP bar is equivalent to RegExp(bar).hasMatch(foo). Note that we have to create a new RegExp instance for each regexp sql call, which can impact performance on large queries.
• current_time_millis: Returns the current unix timestamp as milliseconds. Equivalent to DateTime.now().millisecondsSinceEpoch in Dart.

Note that NaN, -infinity or +infinity are represented as NULL in sql.

When enabling the moor_ffi module in your build options, the generator will allow you to use those functions in moor files or compiled queries.

To use those methods from Dart, you need to import package:moor/extensions/moor_ffi.dart. You can then use the additional functions like this:

import 'package:moor/moor.dart';
// those methods are hidden behind another import because they're only available on moor_ffi
import 'package:moor/extensions/moor_ffi.dart';

class Coordinates extends Table {
  RealColumn get x => real()();
  RealColumn get y => real()();
}

// Can now be used like this:
Future<List<Coordinate>> findNearby(Coordinate center, int radius) {
  return (select(coordinates)..where((other) {
    // find coordinates where sqrt((center - x)² + (center.y - y)²) < radius
    final distanceSquared = sqlPow(center.x - row.x, 2) + sqlPow(center.y - row.y, 2);
    return sqlSqrt(distanceSquared).isLessThanValue(radius);
  })).get();
}

Aller the other functions are available under a similar name (sqlSin, sqlCos, sqlAtan and so on). They have that sql prefix to avoid clashes with dart:math.