SQLCipher Database for Flutter

This is a Flutter plugin that bundles and wraps SQLCipher for Android, an open-source extension to SQLite that provides transparent 256-bit AES encryption of database files.

Features

• Implements fully-encrypted SQLite databases stored on disk or in memory.

• Supports booleans, doubles, integers, strings, blobs, and timestamps.

• Provides a high-fidelity subset of the android.database.sqlite API to aid Android developers migrating to Flutter.

• Facilitates porting existing Android database code to Flutter.

Compatibility

Android only, at present. (iOS support is planned.)

Examples

Executing an arbitrary SQL statement

await db.execSQL("DROP TABLE IF EXISTS links");

// Create a bookmark links table:
await db.execSQL("""
CREATE TABLE links (
  id INTEGER PRIMARY KEY NOT NULL,
  url TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
)
""");

Executing an INSERT statement

// Insert a new link into the table:
var linkID = db.insert(
  table: "links",
  values: <String, dynamic>{
    "id": null, // auto-incremented ID assigned automatically
    "url": "http://example.org/",
    "created_at": DateTime.now().toUtc().millisecondsSinceEpoch ~/ 1000,
    "updated_at": null,
  },
);

Executing an UPDATE statement

// Change the previously-inserted link from HTTP to HTTPS:
db.update(
  table: "links",
  values: <String, dynamic>{
    "url": "https://example.org/",
    "updated_at": DateTime.now().toUtc().millisecondsSinceEpoch ~/ 1000,
  },
  where: "id = ?",
  whereArgs: <String>[linkID.toString()],
);

Executing a DELETE statement

// Delete the previously-inserted link:
db.delete(
  table: "links",
  where: "id = ?",
  whereArgs: <String>[linkID.toString()],
);

Executing an arbitrary SQL query

for (var row in await db.rawQuery("SELECT 1 AS a, 2 as b, 3 AS c")) {
  print(row); // prints: {a: 1, b: 2, c: 3}
}

Creating an in-memory database

import 'package:flutter_sqlcipher/sqlite.dart';

var db = await SQLiteDatabase.createInMemory();

Creating an on-disk database

This example also uses Context from the flutter_android package to obtain the app's cache directory path.

import 'package:flutter_sqlcipher/sqlite.dart';
import 'package:flutter_android/android_content.dart' show Context;

var cacheDir = await Context.cacheDir;
await cacheDir.create(recursive: true);

var cacheFile = File("${cacheDir.path}/cache.db");

var db = await SQLiteDatabase.openOrCreateDatabase(cacheFile.path);

Using a bundled database from the app's assets

(To be added.)

Frequently Asked Questions

Which releases of SQLite and SQLCipher does this plugin bundle?

SQLCipher for Android 4.0.0, SQLCipher 4.0.0, and SQLite 3.25.2.

Why this plugin instead of wrapping Android's native SQLite support?

Two good reasons are:

1. Encryption. Android's native SQLite support does not feature database encryption. By using this plugin, you can trivially enable encryption for your app database, something likely appreciated by both you as well as your users.

2. Compatibility. Android's native SQLite version varies greatly depending on the specific Android release, from SQLite 3.4 (released in 2007) to SQLite 3.19 (released in 2017, bundled in Android 8.1). Further, some device manufacturers include different versions of SQLite on their devices. By using this plugin, you gain a consistent, predictable, and up-to-date version of SQLite for your app regardless of the Android release your app runs on.

How much does using this plugin increase my final app size?

Due to the bundled SQLCipher native libraries, your final APK size currently increases by about 6.7 MiB. We are actively investigating ways to reduce that footprint. (e.g., pruning .so files and using ProGuard).

Why are some of the android.database.sqlite API methods missing?

We don't generally implement methods deprecated in the current Android API level. For example, the SQLiteDatabase#isDbLockedByOtherThreads() method was deprecated long ago (in Android 4.1), so we have omitted it from the Dart interface when implementing this plugin.

Caveats

• At present, iOS is not supported. This may eventually be addressed going forward by bundling and wrapping FMDB which includes SQLCipher support.

• At present, cursors are fully materialized. This means that queries which return very large result sets will incur nontrivial overhead in the IPC transfer of the cursor data from Java to Dart. We are planning on adding windowed cursor and streaming support in a future major release. In the meanwhile, OFFSET and LIMIT are your friends.

Reference

sqlcipher

import 'package:flutter_sqlcipher/sqlcipher.dart';

• SQLCipher.version

sqlite

import 'package:flutter_sqlcipher/sqlite.dart';

• SQLite.version
• SQLiteCursor
• SQLiteDatabase
• SQLiteDatabase.create()
• SQLiteDatabase.createInMemory()
• SQLiteDatabase.deleteDatabase()
• SQLiteDatabase.openDatabase()
• SQLiteDatabase.openOrCreateDatabase()
• SQLiteDatabase.releaseMemory()
• SQLiteDatabase#inTransaction
• SQLiteDatabase#maximumSize
• SQLiteDatabase#pageSize
• SQLiteDatabase#path
• SQLiteDatabase#version
• SQLiteDatabase#beginTransaction()
• SQLiteDatabase#beginTransactionNonExclusive()
• SQLiteDatabase#close()
• SQLiteDatabase#delete()
• SQLiteDatabase#disableWriteAheadLogging()
• SQLiteDatabase#enableWriteAheadLogging()
• SQLiteDatabase#endTransaction()
• SQLiteDatabase#execSQL()
• SQLiteDatabase#getAttachedDbs()
• SQLiteDatabase#getMaximumSize()
• SQLiteDatabase#getPageSize()
• SQLiteDatabase#getPath()
• SQLiteDatabase#getVersion()
• SQLiteDatabase#insert()
• SQLiteDatabase#insertOrThrow()
• SQLiteDatabase#insertWithOnConflict()
• SQLiteDatabase#isDatabaseIntegrityOk
• SQLiteDatabase#isDbLockedByCurrentThread
• SQLiteDatabase#isOpen
• SQLiteDatabase#isReadOnly
• SQLiteDatabase#isWriteAheadLoggingEnabled
• SQLiteDatabase#needUpgrade()
• SQLiteDatabase#query()
• SQLiteDatabase#rawQuery()
• SQLiteDatabase#replace()
• SQLiteDatabase#replaceOrThrow()
• SQLiteDatabase#setForeignKeyConstraintsEnabled()
• SQLiteDatabase#setLocale()
• SQLiteDatabase#setMaxSqlCacheSize()
• SQLiteDatabase#setMaximumSize()
• SQLiteDatabase#setPageSize()
• SQLiteDatabase#setTransactionSuccessful()
• SQLiteDatabase#setVersion()
• SQLiteDatabase#update()
• SQLiteDatabase#updateWithOnConflict()
• SQLiteDatabase#validateSql()
• SQLiteDatabase#yieldIfContendedSafely()

Cross-Reference

Datatype Mappings

Dart Class	Dart API	SQLite Storage Class	Notes
null	SQLiteCursor#isNull()	NULL	-
bool	SQLiteCursor#getBool()	INTEGER	0, 1
int	SQLiteCursor#getInt()	INTEGER	-
double	SQLiteCursor#getDouble()	REAL	-
String	SQLiteCursor#getString()	TEXT	-
Uint8List	SQLiteCursor#getBlob()	BLOB	-
DateTime	SQLiteCursor#getDateTime()	TEXT	ISO-8601 "YYYY-MM-DD HH:MM:SS.SSS"
DateTime	SQLiteCursor#getDateTime()	INTEGER	Seconds since 1970-01-01T00:00:00Z

See Also

• The sql_builder package implements a fluent DSL interface for constructing SQL queries.