Getting Started with Fast SQL

1. Install the package

   npm

   npm i @capgo/capacitor-fast-sql

   pnpm

   pnpm add @capgo/capacitor-fast-sql

   yarn

   yarn add @capgo/capacitor-fast-sql

   bun

   bun add @capgo/capacitor-fast-sql

2. Sync with native projects

   npm

   npx cap sync

   pnpm

   pnpm cap sync

   yarn

   yarn cap sync

   bun

   bunx cap sync

3. Configure platforms

   iOS

   Allow local network access in Info.plist:

   ios/App/App/Info.plist

   <key>NSAppTransportSecurity</key>
   <dict>
       <key>NSAllowsLocalNetworking</key>
       <true/>
   </dict>

   Android

   Add cleartext exception for localhost traffic:

   android/app/src/main/AndroidManifest.xml

   <application android:networkSecurityConfig="@xml/network_security_config">
       ...
   </application>

   Web

   Install sql.js for web storage fallback:

   npm install sql.js

Why Fast SQL?

Fast SQL avoids heavy bridge serialization by using a local HTTP transport to native SQLite, which is much faster for large result sets and sync-style writes.

Basic Usage

import { FastSQL, IsolationLevel, KeyValueStore } from '@capgo/capacitor-fast-sql';

const db = await FastSQL.connect({
  database: 'myapp',
  encrypted: false,
  readOnly: false,
});

await db.execute(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at INTEGER DEFAULT (strftime('%s', 'now'))
  )
`);

await db.run('INSERT INTO users (name, email) VALUES (?, ?)', ['John', 'john@example.com']);
const users = await db.query('SELECT * FROM users');

Core connection methods

• execute(statement, params?) -> returns full SQL result
• query(statement, params?) -> returns rows
• run(statement, params?) -> returns { rowsAffected, insertId }
• executeBatch(operations) -> returns an array of results

import { type SQLBatchOperation } from '@capgo/capacitor-fast-sql';

const rows = await db.query('SELECT * FROM users WHERE email LIKE ?', ['john%']);

const batch: SQLBatchOperation[] = [
  { statement: 'INSERT INTO users (name, email) VALUES (?, ?)', params: ['Alice', 'alice@acme.dev'] },
  { statement: 'INSERT INTO users (name, email) VALUES (?, ?)', params: ['Bob', 'bob@acme.dev'] },
];
await db.executeBatch(batch);

Transactions

callback helper

await db.transaction(async (tx) => {
  await tx.run('UPDATE accounts SET balance = balance - 100 WHERE id = ?', [1]);
  await tx.run('UPDATE accounts SET balance = balance + 100 WHERE id = ?', [2]);
});

explicit control

import { IsolationLevel } from '@capgo/capacitor-fast-sql';

await db.beginTransaction(IsolationLevel.Serializable);
try {
  await db.run('UPDATE wallets SET points = points - 1 WHERE user_id = ?', [42]);
  await db.commit();
} catch (error) {
  await db.rollback();
  throw error;
}

Supported isolation levels:

• ReadUncommitted
• ReadCommitted
• RepeatableRead
• Serializable

Binary data (BLOB)

const imageData = new Uint8Array([0xFF, 0xD8, 0xFF, 0xE0]);
await db.run('INSERT INTO assets (name, data) VALUES (?, ?)', ['avatar', imageData]);

const rows = await db.query('SELECT data FROM assets WHERE name = ?', ['avatar']);
const retrieved = rows[0].data; // Uint8Array

Encryption and read-only modes

const secureDb = await FastSQL.connect({
  database: 'secure_db',
  encrypted: true,
  encryptionKey: 'replace-with-a-strong-key',
});

const readOnlyDb = await FastSQL.connect({
  database: 'public_db',
  readOnly: true,
});

On Android, encrypted mode uses SQLCipher; include dependency in app build.gradle.

KeyValueStore

KeyValueStore is a convenience wrapper for mobile key/value data.

import { KeyValueStore } from '@capgo/capacitor-fast-sql';

const kv = await KeyValueStore.open({
  database: 'kv',
  store: 'session',
  encrypted: true,
  encryptionKey: 'super-secret-key',
});

await kv.set('session', { token: 'abc', expiresAt: Date.now() + 3600_000 });
const session = await kv.get('session');
await kv.has('session');
await kv.keys();
await kv.remove('session');
await kv.clear();
await kv.close();

Connection lifecycle

await FastSQL.disconnect('myapp');
await FastSQL.disconnectAll();
const openDatabases = FastSQL.getOpenDatabases();
const same = FastSQL.getConnection('myapp');

Error handling

try {
  await FastSQL.connect({ database: 'myapp' });
  await db.query('SELECT * FROM unknown_table');
} catch (error) {
  console.error('Fast SQL error:', error);
}

Common SQL Patterns

Check if Table Exists

const result = await db.query(
  "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
  ['users']
);

const tableExists = result.length > 0;

Get Table Schema

const schema = await db.query('PRAGMA table_info(users)');
console.log('Columns:', schema);

Count Rows

const result = await db.query('SELECT COUNT(*) as count FROM users');
const count = result[0].count;

Pagination

const pageSize = 20;
const page = 1;
const offset = (page - 1) * pageSize;

const users = await db.query(
  'SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?',
  [pageSize, offset]
);

Performance Tips

1. Use Transactions for multiple operations - significantly faster than individual commits
2. Use Batch Operations for bulk inserts - more efficient than loops
3. Create Indexes on frequently queried columns
4. Use Prepared Statements with parameters (?) - prevents SQL injection and improves performance
5. Use HTTP Protocol Directly for very large result sets
6. Close Connections when not in use to free resources

Next Steps

Check out the complete tutorial for advanced patterns including:

• Database service architecture
• Migration systems
• Sync engines
• Complex queries and joins
• Performance optimization

Related Documentation

• Migrate from Ionic Secure Storage
• Ionic enterprise plugins migration solution