sqlite3

https://pub.dev/packages/sqlite3
Admin
sqlite3 provides lightweight and convenient Dart bindings to SQLite for both native platforms and...
Tokens:4,901
Snippets:64
Trust Score:9.9
Update:1 month ago
Show doc for...
Tokens:
Raw
### Install sqlite3 package

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

Use the Dart pub tool to add the sqlite3 dependency to your project.

```bash
dart pub add sqlite3
```

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

### Define custom CodeAsset

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

Example of adding a derived CodeAsset to outputs when skipping the default build.

```dart
CodeAsset(
  package: 'sqlite3_native_assets',
  name: 'sqlite3_native_assets.dart',
  linkMode: linkMode,
)
```

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

### Initialize and use sqlite3_native_assets

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

Demonstrates opening in-memory databases, creating tables, managing sessions, applying changesets, and executing SQL queries.

```dart
import 'dart:io';

import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite3_native_assets/sqlite3_native_assets.dart';

void main() {
  final sqlite3 = sqlite3Native;
  print('Using sqlite3 ${sqlite3.version}');

  // Create a new in-memory database. To use a database backed by a file, you
  // can replace this with sqlite3.open(yourFilePath).
  final db = sqlite3.openInMemory();
  final db2 = sqlite3.openInMemory();

  final session = Session(db);

  print('session: ${session.runtimeType}');

  // Create a table and insert some data
  db.execute('''
    CREATE TABLE artists (
      id INTEGER NOT NULL PRIMARY KEY,
      name TEXT NOT NULL
    );
  ''');
  db2.execute('''
    CREATE TABLE artists (
      id INTEGER NOT NULL PRIMARY KEY,
      name TEXT NOT NULL
    );
  ''');

  session.attach('artists');
  print('attached to artists');

  // Prepare a statement to run it multiple times:
  final stmt = db.prepare('INSERT INTO artists (name) VALUES (?)');
  stmt
    ..execute(['The Beatles'])
    ..execute(['Led Zeppelin'])
    ..execute(['The Who'])
    ..execute(['Nirvana']);

  // Dispose a statement when you don't need it anymore to clean up resources.
  stmt.dispose();

  // final changeset = session.changeset();
  // print('changeset: ${changeset.lengthInBytes} bytes');

  final changeset = session.patchset();
  print('patchset: ${changeset.bytes.length} bytes');

  // apply changes
  changeset.applyTo(
    db2,
    // conflict: (ctx, eConflict, iter) {
    //   print('conflict: $eConflict');
    //   return ApplyChangesetRule.omit;
    // },
    // filter: (ctx, table) {
    //   print('filter: $table');
    //   return 1;
    // },
  );

  // save to ./changeset.bin
  File('./changeset.bin').writeAsBytes(changeset.bytes);

  // query the database using a simple select statement
  final result = db2.select('SELECT * FROM artists');
  for (final row in result) {
    print('cs: Artist[id: ${row['id']}, name: ${row['name']}]');
  }

  session.delete();
  print('deleted session');

  // You can run select statements with PreparedStatement.select, or directly
  // on the database:
  final ResultSet resultSet = db.select(
    'SELECT * FROM artists WHERE name LIKE ?',
    ['The %'],
  );

  // You can iterate on the result set in multiple ways to retrieve Row objects
  // one by one.
  for (final Row row in resultSet) {
    print('Artist[id: ${row['id']}, name: ${row['name']}]');
  }

  // Register a custom function we can invoke from sql:
  db.createFunction(
    functionName: 'dart_version',
    argumentCount: const AllowedArgumentCount(0),
    function: (args) => Platform.version,
  );
  print(db.select('SELECT dart_version()'));

  // Don't forget to dispose the database to avoid memory leaks
  db.dispose();
}
```

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

### Open a database on the web

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

Load the WebAssembly module and register a virtual file system before opening a database in a web browser.

```dart
import 'package:sqlite3/wasm.dart';

Future<WasmSqlite3> loadSqlite() async {
  final sqlite = await WasmSqlite3.loadFromUrlString('sqlite3.wasm');
  final fileSystem = await IndexedDbFileSystem.open(dbName: 'my_app');
  sqlite.registerVirtualFileSystem(fileSystem, makeDefault: true);
  return sqlite;
}

void main() async {
  final sqlite3 = await loadSqlite();
  final db = sqlite3.open('test.db');
  db
    ..execute(
      'CREATE TABLE users (id INTEGER NOT NULL PRIMARY KEY, name TEXT) STRICT',
    )
    ..execute('INSERT INTO users (name) VALUES (?)', ['SQLite user']);

  print(db.select('SELECT * FROM users'));
  db.close();
}
```

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

### Open a database on native platforms

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

Use the sqlite3 constant to open a database file directly on native platforms.

```dart
import 'package:sqlite3/sqlite3.dart';

void main() {
  final db = sqlite3.open('test.db');
  db
    ..execute(
      'CREATE TABLE users (id INTEGER NOT NULL PRIMARY KEY, name TEXT) STRICT',
    )
    ..execute('INSERT INTO users (name) VALUES (?)', ['SQLite user']);

  print(db.select('SELECT * FROM users'));
}
```

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

### Add sqlite3 dependency via command line

Source: https://pub.dev/packages/sqlite3/install

Use these commands in your terminal to add the package to your project.

```bash
$ dart pub add sqlite3
```

```bash
$ flutter pub add sqlite3
```

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

### Initialize and expose sqlite3_web

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

Initializes the WebSqlite instance with worker and WASM configurations, then exposes database connection and execution functions to the global JavaScript context.

```dart
import 'dart:js_interop';
import 'dart:js_interop_unsafe';

import 'package:sqlite3_web/sqlite3_web.dart';

void main() async {
  final sqlite = WebSqlite.open(
    workers: WorkerConnector.defaultWorkers('worker.dart.js'),
    wasmModule: 'sqlite3.wasm',
  );

  final features = await sqlite.runFeatureDetection();
  print('got features: $features');

  globalContext['open'] = (JSString name, JSString implementation) {
    return Future(() async {
      final database = await sqlite.connect(
        name.toDart,
        DatabaseImplementation.values.byName(implementation.toDart),
      );

      database.updates.listen((update) {
        print('Update on $name: $update');
      });

      return database.toJSBox;
    }).toJS;
  }.toJS;

  globalContext['execute'] = (JSBoxedDartObject database, JSString sql) {
    return Future(() async {
      await (database.toDart as Database).execute(sql.toDart);
    }).toJS;
  }.toJS;
}
```

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

### Initialize sqlite3 with native assets

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

Replace the standard sqlite3 getter with sqlite3Native to utilize the native asset implementation.

```dart
import 'dart:io';

import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite3_native_assets/sqlite3_native_assets.dart';

void main() {
  final Sqlite3 sqlite3 = sqlite3Native;
  print('Using sqlite3 ${sqlite3.version}');
}
```

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

### Connect to a Database in the Main Thread

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

Opens a connection to the database using the worker script and the required WASM module.

```dart
Future<void> connectToDatabase() async {
  final sqlite = await WebSqlite.open(
    workers: WorkerConnector.defaultWorkers('worker.dart.js'),
    wasmModule: 'sqlite3.wasm',
  );

  final features = await sqlite.runFeatureDetection();
  print('got features: $features');

  final connection = await sqlite.connectToRecommended('my_database');
  print(await connection.database.select('select sqlite_version()'));
}
```

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

### Add dependency via command line

Source: https://pub.dev/packages/sqlite3_native_assets/install

Use these commands to add the package to your project's dependencies automatically.

```bash
$ dart pub add sqlite3_native_assets
```

```bash
$ flutter pub add sqlite3_native_assets
```

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

### Configure SQLite source options

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

Various methods to define the SQLite source, including remote URLs, local files, system loading, or disabling the build.

```yaml
hooks:
  user_defines:
    sqlite3_native_assets:
      source:
        amalgamation: https://sqlite.org/2025/sqlite-amalgamation-3490100.zip
```

```yaml
hooks:
  user_defines:
    sqlite3_native_assets:
      source:
        amalgamation:
          uri: https://github.com/utelle/SQLite3MultipleCiphers/releases/download/v2.2.1/sqlite3mc-2.2.1-sqlite-3.50.2-amalgamation.zip
          filename: sqlite3mc_amalgamation.c
```

```yaml
hooks:
  user_defines:
    sqlite3_native_assets:
      source:
        local: third_party/sqlite3/sqlite3.c
```

```yaml
hooks:
  user_defines:
    sqlite3_native_assets:
      source:
        system: # can also use process: or executable: here
```

```yaml
hooks:
  user_defines:
    sqlite3_native_assets:
      source: false
```

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

### Configure user defines in pubspec

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

Basic structure for adding sqlite3_native_assets configuration to your pubspec file.

```yaml
hooks:
  user_defines:
    sqlite3_native_assets:
      # Your options here
```

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

### Initialize Encrypted Database with WasmSqlite3

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

Loads the sqlite3mc.wasm asset to enable encrypted database support on the web. Ensure the asset is available at the specified URL before calling this method.

```dart
final sqlite3 = await WasmSqlite3.loadFromUrlString('sqlite3mc.wasm');
sqlite3.registerVirtualFileSystem(InMemoryFileSystem(), makeDefault: true);

final database = sqlite3.open('/database')
  ..execute("pragma key = 'test';"); // TODO: Replace key
```

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

### Perform SQLite database operations in Dart

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

Demonstrates opening an in-memory database, executing DDL, using prepared statements, querying data, and registering custom SQL functions.

```dart
import 'dart:io';

import 'package:sqlite3/sqlite3.dart';

void main() {
  print('Using sqlite3 ${sqlite3.version}');

  // Create a new in-memory database. To use a database backed by a file, you
  // can replace this with sqlite3.open(yourFilePath).
  final db = sqlite3.openInMemory();

  // Create a table and insert some data
  db.execute('''
    CREATE TABLE artists (
      id INTEGER NOT NULL PRIMARY KEY,
      name TEXT NOT NULL
    );
  ''');

  // Prepare a statement to run it multiple times:
  final stmt = db.prepare('INSERT INTO artists (name) VALUES (?)');
  stmt
    ..execute(['The Beatles'])
    ..execute(['Led Zeppelin'])
    ..execute(['The Who'])
    ..execute(['Nirvana']);

  // Dispose a statement when you don't need it anymore to clean up resources.
  stmt.close();

  // You can run select statements with PreparedStatement.select, or directly
  // on the database:
  final ResultSet resultSet = db.select(
    'SELECT * FROM artists WHERE name LIKE ?',
    ['The %'],
  );

  // You can iterate on the result set in multiple ways to retrieve Row objects
  // one by one.
  for (final Row row in resultSet) {
    print('Artist[id: ${row['id']}, name: ${row['name']}]');
  }

  // Register a custom function we can invoke from sql:
  db.createFunction(
    functionName: 'dart_version',
    argumentCount: const AllowedArgumentCount(0),
    function: (args) => Platform.version,
  );
  print(db.select('SELECT dart_version()'));

  // Don't forget to dispose the database to avoid memory leaks (optional on
  // native platforms).
  db.close();
}
```

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

### Define a Web Worker Entrypoint

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

Initializes the web worker using the WebSqlite.workerEntrypoint method with a custom controller.

```dart
import 'package:sqlite3_web/sqlite3_web.dart';

import 'controller.dart';

void main() {
  WebSqlite.workerEntrypoint(controller: ExampleController());
}
```

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

### Import the package

Source: https://pub.dev/packages/sqlite3_native_assets/install

Include this import statement in your Dart files to access the package functionality.

```dart
import 'package:sqlite3_native_assets/sqlite3_native_assets.dart';
```

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

### Configure compile-time options

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

Methods for overriding or adding compile-time options using maps, lists, or disabling default options.

```yaml
hooks:
  user_defines:
    sqlite3_native_assets:
      defines:
        defines:
          SQLITE_LIKE_DOESNT_MATCH_BLOBS:
          SQLITE_MAX_SCHEMA_RETRY: 2
```

```yaml
hooks:
  user_defines:
    sqlite3_native_assets:
      defines:
        defines:
          - SQLITE_LIKE_DOESNT_MATCH_BLOBS
          - SQLITE_MAX_SCHEMA_RETRY=2
```

```yaml
hooks:
  user_defines:
    sqlite3_native_assets:
      defines:
        default_options: false
        defines:
          # Your preferred options here
```

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

### Add sqlite3 to pubspec.yaml

Source: https://pub.dev/packages/sqlite3/install

Manually add this entry to your project's pubspec.yaml file.

```yaml
dependencies:
  sqlite3: ^3.5.1
```

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

### Import sqlite3 in Dart

Source: https://pub.dev/packages/sqlite3/install

Include this statement at the top of your Dart files to access the library.

```dart
import 'package:sqlite3/sqlite3.dart';
```

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

### Add dependency to pubspec.yaml

Source: https://pub.dev/packages/sqlite3_native_assets/install

Manually add the package version to the dependencies section of your pubspec.yaml file.

```yaml
dependencies:
  sqlite3_native_assets: ^0.0.11
```

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

### Execute transactions with requestLock

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

Use requestLock to ensure exclusive access to the database during transaction execution. The LockToken must be passed to all database operations within the callback.

```dart
await db.requestLock((lock) async {
  await db.execute('BEGIN', token: lock);
  await db.execute(
    '... other statements',
    token: lock,
    checkInTransaction: true,
  );
  await db.execute('COMMIT', token: lock, checkInTransaction: true);
});
```
Tokens:
Raw