DOCSEARCHbyAlgolia
Submit Docs

sqlite3.dart

repository·main·Indexed 43 days ago

https://github.com/simolus3/sqlite3.dart

Dart bindings for SQLite using dart:ffi, supporting standalone Dart apps, Flutter apps, and web environments. The ecosystem includes the core sqlite3 package, sqlite3_connection_pool for asynchronous isolate-aware connection pooling, sqlite3_test for time-faking and VFS utilities in tests, and sqlite3_web for running SQLite in browsers via WebAssembly and Web Workers.

Tokens
18.2K
Snippets
66
Records
87
Agent score
55%

What's inside sqlite3.dart

  1. Overview of sqlite3.dart packages

    main

    The sqlite3.dart repository provides several packages for using SQLite within the Dart ecosystem via dart:ffi:

    • package:sqlite3: The core package containing all Dart APIs and implementations. It is a pure-Dart package that does not depend on Flutter, making it suitable for both standalone Dart applications and Flutter apps.
    • package:sqlite3_test: Provides utilities for integrating SQLite into Dart tests, specifically patching CURRENT_TIMESTAMP and related SQL constructs to work with package:clock for time-faking.
    • package:sqlite3_web: Contains helpers for running SQLite in web environments, including web worker compilation support.
    • package:sqlite3_connection_pool: Provides an asynchronous connection pool for SQLite designed to work effectively across isolates.

  2. How sqlite3_connection_pool manages concurrency and performance

    main

    Unlike traditional Dart-only connection pools that use a single background isolate as a 'server' (which can lead to complex communication and expensive data copying), sqlite3_connection_pool uses a Rust-based management layer.

    Key architectural benefits:

    • Global Pool Map: The Rust library maintains a global map of active pools, allowing pools to be shared across Flutter engines or background tasks.
    • Synchronous API on Asynchronous Pool: While the pool itself is asynchronous, it provides Database instances from the sqlite3 package that can be used synchronously. This is highly efficient for background isolates where synchronous access is acceptable.
    • Efficient Data Transfer: For asynchronous queries, the library spawns a short-lived isolate per call. Because each isolate runs exactly one query, the library can "move" result sets across isolates instead of copying them, significantly reducing overhead for large datasets.

  3. Use custom WASM modules with package:sqlite3

    main

    You can build a custom sqlite3.wasm module to include additional SQLite extensions or use specific compile-time options. This is particularly useful when you need to implement custom sqlite3_os_init or sqlite3_os_end hooks to load extensions or manage the environment.

    In a typical custom build workflow:

    1. SQLite is compiled to WebAssembly object files.
    2. Your custom extensions (e.g., written in Rust) are compiled into a static library.
    3. The components are linked together into a single sqlite3.wasm file that can be loaded by package:sqlite3.

  4. How to synchronize SQLite time with package:clock

    main

    By default, SQLite's CURRENT_TIMESTAMP, CURRENT_TIME, and CURRENT_DATE are unaware of Dart testing utilities like package:clock. To ensure that database queries using these functions return values consistent with your test's controlled time (e.g., using FakeAsync), use TestSqliteFileSystem and register it with the sqlite3 library.

    This allows you to reliably test logic that depends on database-level timestamps by overriding the time in your Dart test environment.

    import 'package:fake_async/fake_async.dart';
import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite3_test/sqlite3_test.dart';
import 'package:file/local.dart';
import 'package:test/test.dart';

void main() {
  late TestSqliteFileSystem vfs;

  setUpAll(() {
    // Initialize the VFS with a FileSystem from package:file
    vfs = TestSqliteFileSystem(fs: const LocalFileSystem());
    // Register the VFS globally for the sqlite3 library
    sqlite3.registerVirtualFileSystem(vfs);
  });

  tearDownAll(() => sqlite3.unregisterVirtualFileSystem(vfs));

  test('my test depending on database time', () {
    // Open the database using the registered VFS name
    final database = sqlite3.openInMemory(vfs: vfs.name);
    addTearDown(database.dispose);

    final moonLanding = DateTime.utc(1969, 7, 20, 20, 18, 04);
    FakeAsync(initialTime: moonLanding).run((_) {
      // current_timestamp will now reflect the FakeAsync time
      final row = database.select('SELECT unixepoch(current_timestamp)').first;
      expect(row.columnAt(0), -14182916);
    });
  });
}

  5. How sqlite3_web works with Web Workers

    main

    Accessing sqlite3 on the web reliably requires using Web Workers to leverage the filesystem access API. Because workers run in a separate context from your main JavaScript application, you cannot use the synchronous package:sqlite3 API.

    package:sqlite3_web solves this by:

    1. Providing an API to write database workers in Dart.
    2. Managing asynchronous communication channels between your app and the worker.
    3. Automatically detecting and selecting the best available browser storage engine (persistence API).

  6. Write cross-platform database code using package:sqlite3/common.dart

    main

    To ensure your code can run on all platforms (native and web), restrict your direct usage of the sqlite3 package to package:sqlite3/common.dart. This package provides the common definitions and interfaces implemented by both native and web bindings.

    While using common.dart makes your code portable, it does not provide the actual database implementation. To bridge this, you should write your database logic against the common interfaces and use conditional imports to provide the platform-specific implementation at runtime.

  7. Write platform-independent database code

    main

    To write code that works on both native and web platforms, do not import package:sqlite3/sqlite3.dart or package:sqlite3/wasm.dart directly in your shared logic. Instead, import package:sqlite3/common.dart.

    This provides shared interfaces:

    • CommonDatabase
    • CommonPreparedStatement
    • CommonSqlite3 (implemented by both WasmSqlite3 and the native sqlite3 constant)
    import 'package:sqlite3/common.dart';

// Use CommonDatabase and CommonPreparedStatement in your shared logic
void performQuery(CommonDatabase db) {
  db.execute('SELECT 1');
}

  8. Choosing between sqlite3.wasm and sqlite3.debug.wasm for web

    main

    When deploying sqlite3.dart to the web, you need to include a .wasm file in your web assets folder:

    • sqlite3.debug.wasm: Recommended for development and examples. It prints more detailed log messages to the console, which is helpful for debugging.
    • sqlite3.wasm: Recommended for production applications to ensure optimal performance and minimal logging overhead.

  9. Customize iOS launch screen assets

    main

    To change the launch screen image for the iOS integration test runner, replace the existing image files within the examples/flutter_integration_tests/ios/Runner/Assets.xcassets/LaunchImage.imageset/ directory with your own assets.

    Alternatively, you can manage these assets through Xcode:

    1. Open the iOS workspace using open ios/Runner.xcworkspace from your terminal.
    2. In the Xcode Project Navigator, navigate to Runner/Assets.xcassets.
    3. Drag and drop your desired images into the asset catalog to replace the existing ones.
    open ios/Runner.xcworkspace

  10. Run the sqlite3 web example

    main

    To run the web demonstration, you must first provide a WebAssembly (WASM) binary for SQLite.

    1. Download the sqlite3.debug.wasm file from the official releases.
    2. Place the downloaded file into the web/ directory of the example folder.
    3. Navigate to the sqlite3/ root directory (../..) and start the local server using build_runner:
    dart run build_runner serve example:8080
    1. Open your browser and visit http://localhost:8080/web/ to view the running example.

  11. Open and query a database with sqlite3-web

    main

    Use connectToRecommended to open a database. The library automatically selects the best available storage implementation (IndexedDB or OPFS) based on browser support. Once connected, you can execute SQL commands using .execute() or retrieve data using .select() with parameter binding.

    const db = await sqlite3.connectToRecommended("my_database");

await db.execute("CREATE TABLE users (name TEXT) STRICT");
const result = await db.select(
  "SELECT * FROM users LIMIT ?",
  { parameters: [10] }
);

  12. Customize the SQLite WASM module

    main

    You can modify the SQLite WASM module to include custom compile-time options or C extensions. The build scripts are designed to mirror the options used by sqlite3_flutter_libs.

    • To change compile-time options: Modify assets/wasm/sqlite_cfg.h and re-run the build process.
    • To include C extensions: Adapt the CMakeLists.txt located in assets/wasm.
    • To include Rust-based extensions: Refer to the example provided in example/custom_wasm_build (requires the WASI/Clang toolchains).