webf_sqflite 1.0.1

WebF SQFlite module for SQLite database operations. Provides persistent local storage with SQL queries, transactions, and batch operations.

webf_sqflite

WebF native plugin for SQLite database operations. This plugin wraps the sqflite Flutter package to provide persistent local storage with SQL queries, transactions, and batch operations for WebF applications.

Features

• Open, close, and delete databases
• Execute raw SQL queries (SELECT, INSERT, UPDATE, DELETE)
• Helper methods for common operations (query, insert, update, delete)
• Batch operations for improved performance
• Transaction support for atomicity
• In-memory database support
• Database version management and migrations

Installation

Flutter Side

Add the dependency to your Flutter app's pubspec.yaml:

dependencies:
  webf: ^0.24.0
  webf_sqflite: ^1.0.0

Register the module in your main.dart:

import 'package:webf/webf.dart';
import 'package:webf_sqflite/webf_sqflite.dart';

void main() {
  WebFControllerManager.instance.initialize(WebFControllerManagerConfig(
    maxAliveInstances: 2,
    maxAttachedInstances: 1,
  ));

  // Register SQFlite module
  WebF.defineModule((context) => SQFliteModule(context));

  runApp(MyApp());
}

JavaScript Side

Install the npm package:

npm install @openwebf/webf-sqflite

Usage

Basic Example

import { WebFSQFlite } from '@openwebf/webf-sqflite';

async function example() {
  // Open a database with initial schema
  const openResult = await WebFSQFlite.openDatabase({
    path: 'my_app.db',
    version: 1,
    onCreate: [
      `CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
      )`,
      `CREATE TABLE posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        title TEXT,
        content TEXT,
        FOREIGN KEY (user_id) REFERENCES users(id)
      )`
    ]
  });

  if (openResult.success !== 'true') {
    console.error('Failed to open database:', openResult.error);
    return;
  }

  const dbId = openResult.databaseId!;

  // Insert a user
  const insertResult = await WebFSQFlite.insert({
    databaseId: dbId,
    table: 'users',
    values: JSON.stringify({
      name: 'John Doe',
      email: '[e-mail]'
    })
  });

  console.log('Inserted user with ID:', insertResult.lastInsertRowId);

  // Query all users
  const queryResult = await WebFSQFlite.query({
    databaseId: dbId,
    table: 'users',
    orderBy: 'name ASC'
  });

  if (queryResult.success === 'true') {
    const users = JSON.parse(queryResult.rows!);
    console.log('Users:', users);
  }

  // Close database when done
  await WebFSQFlite.closeDatabase(dbId);
}

Raw SQL Queries

// Raw SELECT query
const result = await WebFSQFlite.rawQuery({
  databaseId: dbId,
  sql: 'SELECT * FROM users WHERE email LIKE ?',
  arguments: ['[e-mail]']
});

// Raw INSERT
const insertResult = await WebFSQFlite.rawInsert({
  databaseId: dbId,
  sql: 'INSERT INTO users (name, email) VALUES (?, ?)',
  arguments: ['Jane Doe', '[e-mail]']
});

// Raw UPDATE
const updateResult = await WebFSQFlite.rawUpdate({
  databaseId: dbId,
  sql: 'UPDATE users SET name = ? WHERE id = ?',
  arguments: ['Jane Smith', 1]
});

// Raw DELETE
const deleteResult = await WebFSQFlite.rawDelete({
  databaseId: dbId,
  sql: 'DELETE FROM users WHERE id = ?',
  arguments: [1]
});

// Execute DDL statements
await WebFSQFlite.execute({
  databaseId: dbId,
  sql: 'CREATE INDEX idx_users_email ON users(email)'
});

Batch Operations

Batch operations reduce communication overhead and improve performance:

const batchResult = await WebFSQFlite.batch({
  databaseId: dbId,
  operations: JSON.stringify([
    {
      type: 'insert',
      table: 'users',
      values: { name: 'User 1', email: '[e-mail]' }
    },
    {
      type: 'insert',
      table: 'users',
      values: { name: 'User 2', email: '[e-mail]' }
    },
    {
      type: 'insert',
      table: 'users',
      values: { name: 'User 3', email: '[e-mail]' }
    }
  ]),
  noResult: false
});

if (batchResult.success === 'true') {
  const results = JSON.parse(batchResult.results!);
  console.log('Batch results:', results);
}

Transactions

Transactions ensure all operations succeed or all are rolled back:

const txResult = await WebFSQFlite.transaction({
  databaseId: dbId,
  operations: JSON.stringify([
    {
      type: 'insert',
      table: 'users',
      values: { name: 'New User', email: '[e-mail]' }
    },
    {
      type: 'insert',
      table: 'posts',
      values: { user_id: 1, title: 'First Post', content: 'Hello World!' }
    },
    {
      type: 'query',
      table: 'users',
      where: 'email = ?',
      whereArgs: ['[e-mail]']
    }
  ])
});

if (txResult.success === 'true') {
  const results = JSON.parse(txResult.results!);
  console.log('Transaction results:', results);
} else {
  console.error('Transaction failed (rolled back):', txResult.error);
}

Database Management

// Get default databases path
const pathResult = await WebFSQFlite.getDatabasesPath();
console.log('Databases path:', pathResult.path);

// Check if database exists
const existsResult = await WebFSQFlite.databaseExists('my_app.db');
console.log('Database exists:', existsResult.exists === 'true');

// Delete a database
const deleteResult = await WebFSQFlite.deleteDatabase('old_database.db');

// Open in-memory database
const memoryDb = await WebFSQFlite.openDatabase({
  path: 'memory_db',
  inMemory: true,
  onCreate: ['CREATE TABLE cache (key TEXT PRIMARY KEY, value TEXT)']
});

// Open read-only database
const readOnlyDb = await WebFSQFlite.openDatabase({
  path: 'existing.db',
  readOnly: true
});

Query with Filters

const result = await WebFSQFlite.query({
  databaseId: dbId,
  table: 'users',
  columns: ['id', 'name', 'email'],
  where: 'name LIKE ? AND created_at > ?',
  whereArgs: ['%John%', '2024-01-01'],
  orderBy: 'name ASC',
  limit: 10,
  offset: 0,
  distinct: true
});

Update with Conflict Resolution

const result = await WebFSQFlite.insert({
  databaseId: dbId,
  table: 'users',
  values: JSON.stringify({
    id: 1,
    name: 'Updated Name',
    email: '[e-mail]'
  }),
  conflictAlgorithm: 'replace' // 'rollback' | 'abort' | 'fail' | 'ignore' | 'replace'
});

API Reference

Database Management

Method	Description
getDatabasesPath()	Get the default databases directory path
openDatabase(options)	Open or create a database
closeDatabase(databaseId)	Close a database connection
deleteDatabase(path)	Delete a database file
databaseExists(path)	Check if a database file exists

CRUD Operations

Method	Description
query(options)	Query rows from a table
insert(options)	Insert a row into a table
update(options)	Update rows in a table
delete(options)	Delete rows from a table

Raw SQL Operations

Method	Description
rawQuery(options)	Execute a raw SELECT query
rawInsert(options)	Execute a raw INSERT statement
rawUpdate(options)	Execute a raw UPDATE statement
rawDelete(options)	Execute a raw DELETE statement
execute(options)	Execute any SQL statement (DDL, etc.)

Batch & Transaction

Method	Description
batch(options)	Execute multiple operations in a batch
transaction(options)	Execute operations in a transaction

Platform Support

Platform	Support
Android	Yes
iOS	Yes
macOS	Yes
Linux	Via sqflite_common_ffi
Windows	Via sqflite_common_ffi

License

MIT License - see the LICENSE file for details.

Related

• sqflite - The underlying SQLite plugin
• WebF Documentation
• WebF Native Plugins