SQFlite Plugin
SQLite database for persistent local storage in your WebF applications
Version 1.0.1
Features
SQL Queries
Execute raw SQL queries and prepared statements with parameterized queries for safe data operations.
CRUD Operations
Insert, query, update, and delete data with type-safe APIs. Supports WHERE clauses, column selection, ordering, and limits.
Transactions
Execute multiple operations atomically with transaction support. All or nothing - ensures data integrity.
Batch Operations
Execute multiple statements efficiently in a single batch. Perfect for bulk inserts, updates, or migrations.
Installation
1. Add to pubspec.yaml
1dependencies:
2 webf_sqflite: ^1.0.12. Register the module
1import 'package:webf/webf.dart';
2import 'package:webf_sqflite/webf_sqflite.dart';
3
4void main() {
5 // Register module globally
6 WebF.defineModule((context) => SQFliteModule(context));
7 runApp(MyApp());
8}3. Install npm package (JavaScript/TypeScript)
1npm install @openwebf/webf-sqfliteUsage Examples
Open Database
1import { WebFSQFlite } from '@openwebf/webf-sqflite';
2
3// Get database path
4const dbPath = await WebFSQFlite.getDatabasesPath();
5
6// Open database with version and migration
7const dbId = await WebFSQFlite.openDatabase({
8 path: `${dbPath}/my_app.db`,
9 version: 1,
10 onCreate: `
11 CREATE TABLE users (
12 id INTEGER PRIMARY KEY AUTOINCREMENT,
13 name TEXT NOT NULL,
14 email TEXT UNIQUE
15 );
16 CREATE TABLE tasks (
17 id INTEGER PRIMARY KEY AUTOINCREMENT,
18 title TEXT NOT NULL,
19 completed INTEGER DEFAULT 0
20 );
21 `
22});
23
24console.log('Database opened with ID:', dbId);CRUD Operations
1import { WebFSQFlite } from '@openwebf/webf-sqflite';
2
3// Insert a record
4const userId = await WebFSQFlite.insert({
5 databaseId: dbId,
6 table: 'users',
7 values: {
8 name: 'John Doe',
9 email: 'john@example.com'
10 }
11});
12console.log('Inserted user with ID:', userId);
13
14// Query records
15const users = await WebFSQFlite.query({
16 databaseId: dbId,
17 table: 'users',
18 where: 'email LIKE ?',
19 whereArgs: ['%@example.com'],
20 orderBy: 'name ASC',
21 limit: 10
22});
23console.log('Found users:', users);
24
25// Update a record
26const updatedCount = await WebFSQFlite.update({
27 databaseId: dbId,
28 table: 'users',
29 values: { name: 'Jane Doe' },
30 where: 'id = ?',
31 whereArgs: [userId]
32});
33
34// Delete a record
35const deletedCount = await WebFSQFlite.delete({
36 databaseId: dbId,
37 table: 'users',
38 where: 'id = ?',
39 whereArgs: [userId]
40});Raw SQL Queries
1import { WebFSQFlite } from '@openwebf/webf-sqflite';
2
3// Raw SELECT query
4const results = await WebFSQFlite.rawQuery({
5 databaseId: dbId,
6 sql: `
7 SELECT u.name, COUNT(t.id) as task_count
8 FROM users u
9 LEFT JOIN tasks t ON t.user_id = u.id
10 GROUP BY u.id
11 `
12});
13
14// Raw INSERT
15const lastId = await WebFSQFlite.rawInsert({
16 databaseId: dbId,
17 sql: 'INSERT INTO users (name, email) VALUES (?, ?)',
18 arguments: ['Alice', 'alice@example.com']
19});
20
21// Raw UPDATE
22const rowsAffected = await WebFSQFlite.rawUpdate({
23 databaseId: dbId,
24 sql: 'UPDATE tasks SET completed = 1 WHERE id IN (?, ?, ?)',
25 arguments: [1, 2, 3]
26});
27
28// Execute DDL statements
29await WebFSQFlite.execute({
30 databaseId: dbId,
31 sql: 'CREATE INDEX idx_users_email ON users(email)'
32});Transactions
1import { WebFSQFlite } from '@openwebf/webf-sqflite';
2
3// Execute multiple operations atomically
4const result = await WebFSQFlite.transaction({
5 databaseId: dbId,
6 operations: [
7 {
8 sql: 'INSERT INTO users (name, email) VALUES (?, ?)',
9 arguments: ['Bob', 'bob@example.com']
10 },
11 {
12 sql: 'INSERT INTO tasks (title, user_id) VALUES (?, last_insert_rowid())',
13 arguments: ['Welcome task']
14 },
15 {
16 sql: 'UPDATE users SET last_login = datetime("now") WHERE email = ?',
17 arguments: ['bob@example.com']
18 }
19 ]
20});
21
22if (result.success) {
23 console.log('Transaction completed successfully');
24} else {
25 console.error('Transaction failed:', result.error);
26}Batch Operations
1import { WebFSQFlite } from '@openwebf/webf-sqflite';
2
3// Execute multiple statements in a batch
4const batchResult = await WebFSQFlite.batch({
5 databaseId: dbId,
6 operations: [
7 {
8 sql: 'INSERT INTO tasks (title) VALUES (?)',
9 arguments: ['Task 1']
10 },
11 {
12 sql: 'INSERT INTO tasks (title) VALUES (?)',
13 arguments: ['Task 2']
14 },
15 {
16 sql: 'INSERT INTO tasks (title) VALUES (?)',
17 arguments: ['Task 3']
18 }
19 ]
20});
21
22console.log('Batch results:', batchResult.results);
23// Each result contains the lastInsertId for INSERT operationsAPI Reference
getDatabasesPath()
Returns the default database directory path for the platform.
openDatabase(options)
Opens or creates a database. Returns a database ID for subsequent operations.
Options: path, version, onCreate, onUpgrade, onDowngrade, readOnly, singleInstance
query(options)
Queries the database using a table name and optional filters.
Options: table, columns, where, whereArgs, groupBy, having, orderBy, limit, offset
insert(options) / update(options) / delete(options)
Standard CRUD operations on a specific table.
rawQuery(options) / rawInsert(options) / rawUpdate(options) / rawDelete(options)
Execute raw SQL statements with parameterized arguments.
transaction(options) / batch(options)
Execute multiple operations atomically (transaction) or efficiently (batch).
closeDatabase(databaseId)
Closes an open database connection.
Platform Notes
Android
Databases are stored in the app's private directory. No additional permissions required.
iOS
Databases are stored in the app's Documents directory and are included in iCloud backups by default.
macOS
Databases are stored in the application's support directory.
SQLite Version
Uses the native SQLite library provided by each platform. Android and iOS typically include SQLite 3.x with support for common extensions like FTS (Full-Text Search).
Based On
sqflite
This WebF plugin is built on top of the popular sqflite Flutter package, which provides SQLite database support for Flutter applications. The sqflite package is one of the most widely used database solutions in the Flutter ecosystem with millions of downloads.