@capawesome-team/capacitor-sqlite

Capacitor plugin to access SQLite databases with support for encryption, transactions, and schema migrations.

Features

We are proud to offer one of the most complete and feature-rich Capacitor plugins to access SQLite databases. Here are some of the key features:

• 🖥️ Cross-platform: Supports Android, iOS and Web.
• 🔒 Encryption: Supports 256 bit AES encryption with custom keys.
• 📖 Read-only mode: Open databases in read-only mode to prevent modifications.
• 📂 File-based: Open existing databases or create new ones with a file path.
• 📦 In-memory databases: Create temporary in-memory databases for quick operations or testing.
• 📈 Schema migrations: Automatically apply schema migrations when opening a database.
• 🔄 Transactions: Supports transactions with beginTransaction(...), commitTransaction(...), and rollbackTransaction(...).
• 🔍 Querying: Execute SQL queries with query(...) and execute(...).
• 🔢 Data Types: Supports all SQLite data types: NULL, INTEGER, REAL, TEXT, and BLOB.
• 🛡️ Prepared Statements: Uses prepared statements to prevent SQL injection attacks.
• 🕸️ SQLite WASM: Uses SQLite WebAssembly for web platform support.
• 📝 Full Text Search: Supports full text search with FTS5.
• 🤝 Compatibility: Compatible with the Secure Preferences plugin.
• 📦 SPM: Supports Swift Package Manager for iOS.
• 🔁 Up-to-date: Always supports the latest Capacitor version.
• ⭐️ Support: Priority support from the Capawesome Team.

Missing a feature? Just open an issue and we'll add it for you!

Compatibility

Plugin Version	Capacitor Version	Status
7.x.x	>=7.x.x	Active support

Installation

This plugin is only available to Capawesome Insiders. First, make sure you have the Capawesome npm registry set up. You can do this by running the following commands:

npm config set @capawesome-team:registry https://npm.registry.capawesome.io
npm config set //npm.registry.capawesome.io/:_authToken <YOUR_LICENSE_KEY>

Attention: Replace <YOUR_LICENSE_KEY> with the license key you received from Polar. If you don't have a license key yet, you can get one by becoming a Capawesome Insider.

Next, install the package:

npm install @capawesome-team/capacitor-sqlite @sqlite.org/sqlite-wasm
npx cap sync

Android

Encryption

If you want to use encryption, you must include the SQLCipher dependency in your app's variables.gradle file by setting the capawesomeCapacitorSqliteIncludeSqlcipher variable to true:

ext {
+  capawesomeCapacitorSqliteIncludeSqlcipher = true // Default: false
}

Attention: When using SQLCipher you are responsible for compliance with all export, re-export and import restrictions and regulations in all applicable countries. You can find more information about this in this blog post.

Variables

If needed, you can define the following project variable in your app’s variables.gradle file to change the default version of the dependency:

• $androidxSqliteVersion version of androidx.sqlite:sqlite (default: 2.4.0)
• $androidxSqliteFrameworkAndroidVersion version of androidx.sqlite:sqlite-framework-android (default: 2.5.2)
• $netZeteticSqlcipherVersion version of net.zetetic:sqlcipher-android (default: 4.9.0)

This can be useful if you encounter dependency conflicts with other plugins in your project.

iOS

Encryption

If you want to use encryption, you must include the SQLCipher dependency in your app's Podfile by adding the following line:

target 'App' do
capacitor_pods
# Add your Pods here
+  pod 'CapawesomeTeamCapacitorSqlite/SQLCipher', :path => '../../node_modules/@capawesome-team/capacitor-sqlite'
end

Attention: When using SQLCipher you are responsible for compliance with all export, re-export and import restrictions and regulations in all applicable countries. You can find more information about this in this blog post.

If you do NOT want to use encryption, you need to add the Plain pod to your app's Podfile by adding the following line:

target 'App' do
capacitor_pods
# Add your Pods here
+  pod 'CapawesomeTeamCapacitorSqlite/Plain', :path => '../../node_modules/@capawesome-team/capacitor-sqlite'
end

Attention: In both cases, do not add the pod in the section def capacitor_pods, but under the comment # Add your Pods here.

Web

This plugin uses the @sqlite.org/sqlite-wasm package to provide SQLite support on the web platform. It will automatically load the SQLite WASM module when needed.

Usage with Angular

If you are using Angular, you need to add the following configuration to your angular.json file to ensure the SQLite WASM module is copied to the assets folder during the build process and to set the necessary headers for the web worker:

{
  "projects": {
    "your-app-name": {
      "architect": {
        "build": {
          "options": {
            "assets": [
+              {
+                "glob": "**/*",
+                "input": "node_modules/@sqlite.org/sqlite-wasm/sqlite-wasm/jswasm/",
+                "output": "/assets/sqlite-wasm/"
+              }
            ]
          }
        },
        "serve": {
+          "options": {
+            "headers": {
+              "Cross-Origin-Embedder-Policy": "require-corp",
+              "Cross-Origin-Opener-Policy": "same-origin"
+            }
+          }
        }
      }
    }
  }
}

Finally, you need to initialize the SQLite WASM module before using the plugin. You can do this in your main.ts file or in a service that is loaded at the start of your application:

import { Capacitor } from '@capacitor/core';
import { Sqlite } from '@capawesome-team/capacitor-sqlite';

const initialize = async () => {
  const isWeb = Capacitor.getPlatform() === 'web';
  if (isWeb) {
    // Initialize the SQLite WASM module
    await Sqlite.initialize({
      worker: new Worker('/assets/sqlite-wasm/sqlite3-worker1-bundler-friendly.mjs', { type: 'module' })
    });
  }
};

Usage with Vite

If you are using Vite, you need to add the following configuration to your vite.config.ts file to ensure the SQLite WASM module is loaded correctly:

import { defineConfig } from 'vite';

export default defineConfig({
  optimizeDeps: {
    include: ['@sqlite.org/sqlite-wasm'],
  },
  server: {
    headers: {
      'Cross-Origin-Embedder-Policy': 'require-corp',
      'Cross-Origin-Opener-Policy': 'same-origin',
    },
  },
});

Configuration

No configuration required for this plugin.

Usage

import { Capacitor } from '@capacitor/core';
import { Directory, Filesystem } from '@capacitor/filesystem';
import { Sqlite } from '@capawesome-team/capacitor-sqlite';

const open = async () => {
  const { databaseId } = await Sqlite.open({
    encryptionKey: 'secret', // Tip: Use the Secure Preferences plugin to store the key securely
    readOnly: false,
    path: 'mydb.sqlite3',
    upgradeStatements: [
      {
        version: 1,
        statements: [
          'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)',
        ],
      },
      {
        version: 2,
        statements: ['ALTER TABLE users ADD COLUMN email TEXT'],
      },
    ],
    version: 2,
  });
};

const execute = async () => {
  const { databaseId } = await Sqlite.open();
  await Sqlite.execute({
    databaseId,
    statement: 'INSERT INTO users (name, age) VALUES (?, ?)',
    values: ['Alice', 30],
  });
};

const query = async () => {
  const { databaseId } = await Sqlite.open();
  const result = await Sqlite.query({
    databaseId,
    statement: 'SELECT * FROM users WHERE age > ?',
    values: [25],
  });
  console.log(result.columns); // The column names in the result set
  console.log(result.rows); // The rows returned by the query
};

const performTransaction = async () => {
  const { databaseId } = await Sqlite.open();
  await Sqlite.beginTransaction({ databaseId });
  await Sqlite.execute({
    databaseId,
    statement: 'INSERT INTO users (name, age) VALUES (?, ?)',
    values: ['Alice', 30],
  });
  await Sqlite.execute({
    databaseId,
    statement: 'INSERT INTO users (name, age) VALUES (?, ?)',
    values: ['Bob', 25],
  });
  await Sqlite.commitTransaction({ databaseId });
};

const close = async () => {
  const { databaseId } = await Sqlite.open();
  await Sqlite.close({ databaseId });
};

const changeEncryptionKey = async () => {
  // Open the database with the old encryption key
  const { databaseId } = await Sqlite.open({
    encryptionKey: 'old-secret',
  });
  // Change the encryption key to a new one
  await Sqlite.changeEncryptionKey({
    databaseId,
    encryptionKey: 'new-secret',
  });
};

const getVersion = async () => {
  const result = await Sqlite.getVersion();
  console.log(result.version); // The version of the SQLite library used by the plugin
};

const vacuum = async () => {
  const { databaseId } = await Sqlite.open();
  await Sqlite.vacuum({ databaseId });
};

API

• beginTransaction(...)
• changeEncryptionKey(...)
• close(...)
• commitTransaction(...)
• execute(...)
• getVersion()
• initialize(...)
• open(...)
• query(...)
• rollbackTransaction(...)
• vacuum(...)
• Interfaces
• Type Aliases

beginTransaction(...)

beginTransaction(options: BeginTransactionOptions) => Promise<void>

Begin a transaction on the specified database.

Param	Type
options	BeginTransactionOptions

Since: 0.1.0

---

changeEncryptionKey(...)

changeEncryptionKey(options: ChangeEncryptionKeyOptions) => Promise<void>

Change the encryption key of the database.

Attention: This must be called after opening the database with the current encryption key.

Only available on Android and iOS.

Param	Type
options	ChangeEncryptionKeyOptions

Since: 0.1.0

---

close(...)

close(options: CloseOptions) => Promise<void>

Close the specified database.

Param	Type
options	CloseOptions

Since: 0.1.0

---

commitTransaction(...)

commitTransaction(options: CommitTransactionOptions) => Promise<void>

Commit the current transaction on the specified database.

Param	Type
options	CommitTransactionOptions

Since: 0.1.0

---

execute(...)

execute(options: ExecuteOptions) => Promise<ExecuteResult>

Execute a single SQL statement on the specified database.

This method can be used to execute any SQL statement, including INSERT, UPDATE, DELETE, and CREATE TABLE.

Param	Type
options	ExecuteOptions

Returns: Promise<ExecuteResult>

Since: 0.1.0

---

getVersion()

getVersion() => Promise<GetVersionResult>

Get the version of the SQLite library used by the plugin.

To get the version of the database schema, simply run the PRAGMA user_version; command.

Returns: Promise<GetVersionResult>

Since: 0.1.0

---

initialize(...)

initialize(options?: InitializeOptions | undefined) => Promise<void>

Initialize the plugin with optional configuration.

This method is should be called before using any other methods of the plugin.

On Android and iOS, this method is a no-op.

On Web, this method allows you to pass a Worker instance that will be used for the SQLite WebAssembly initialization.

Param	Type
options	InitializeOptions

Since: 0.1.3

---

open(...)

open(options?: OpenOptions | undefined) => Promise<OpenResult>

Open a database with the specified options.

This method can be used to open an existing database or create a new one.

Param	Type
options	OpenOptions

Returns: Promise<OpenResult>

Since: 0.1.0

---

query(...)

query(options: QueryOptions) => Promise<QueryResult>

Query the database and return the result set.

This method can be used to execute SELECT statements and retrieve the result set.

Param	Type
options	QueryOptions

Returns: Promise<QueryResult>

Since: 0.1.0

---

rollbackTransaction(...)

rollbackTransaction(options: RollbackTransactionOptions) => Promise<void>

Rollback the current transaction on the specified database.

This method will undo all changes made in the current transaction.

Only available on Android.

Param	Type
options	RollbackTransactionOptions

Since: 0.1.0

---

vacuum(...)

vacuum(options: VacuumOptions) => Promise<void>

Runs the VACUUM command to rebuild the database file.

This command can be used to reclaim unused space and optimize the database file.

Param	Type
options	VacuumOptions

Since: 0.1.0

---

Interfaces

BeginTransactionOptions

Prop	Type	Description	Since
databaseId	string	The unique identifier for the database to begin a transaction on.	0.1.0

ChangeEncryptionKeyOptions

Prop	Type	Description	Since
databaseId	string	The unique identifier for the database to change the encryption key for.	0.1.0
encryptionKey	string	The new encryption key to set for the database. Attention: It's recommended to use a strong encryption key to protect sensitive data. This key should be kept secret and not hard-coded in your application. If you lose the encryption key, you will not be able to access the data in the database. Tip: Use the Secure Preferences plugin to securely store the encryption key.	0.1.0

CloseOptions

Prop	Type	Description	Since
databaseId	string	The unique identifier for the database to close.	0.1.0

CommitTransactionOptions

Prop	Type	Description	Since
databaseId	string	The unique identifier for the database to commit a transaction on.	0.1.0

ExecuteResult

Prop	Type	Description	Since
changes	number	The number of rows modified by the statement. This property is set for INSERT, UPDATE, and DELETE statements.	0.1.1
rowId	number	The row ID of the last inserted row. This property is only set when executing an INSERT statement.	0.1.1

ExecuteOptions

Prop	Type	Description	Default	Since
databaseId	string	The unique identifier for the database to execute the statement on.		0.1.0
returnChanges	boolean	Whether to return the number of rows modified by the statement. Disabling this option can improve performance for statements that do not require the number of modified rows.	true	0.1.2
returnRowId	boolean	Whether to return the row ID of the last inserted row. Disabling this option can improve performance for statements that do not require the row ID of the last inserted row.	true	0.1.2
statement	string	The SQL statement to execute. This can be any valid SQL statement, such as INSERT, UPDATE, DELETE, or CREATE TABLE.		0.1.0
values	Value[]	Only available on Android.

GetVersionResult

Prop	Type	Description	Since
version	string	The version of the SQLite library used by the plugin.	0.1.0

InitializeOptions

Prop	Type	Description	Since
worker	Worker	The Worker to use for the SQLite WebAssembly initialization. If provided, this worker will be passed to the sqlite3Worker1Promiser method for initializing the SQLite WebAssembly module in the web implementation. Only available on Web.	0.1.3

OpenResult

Prop	Type	Description	Since
databaseId	string	A unique identifier for the opened database.	0.1.0

OpenOptions

Prop	Type	Description	Default	Since
encryptionKey	string	The encryption key to use for the database. If provided, the database will be opened as an encrypted database using the specified key. If not provided, the database will be opened as an unencrypted database. Attention: It's recommended to use a strong encryption key to protect sensitive data. This key should be kept secret and not hard-coded in your application. If you lose the encryption key, you will not be able to access the data in the database. Tip: Use the Secure Preferences plugin to securely store the encryption key. Only available on Android and iOS.		0.1.0
readOnly	boolean	Whether the database should be opened in read-only mode. Only available on Android and iOS.	false	0.1.0
path	string	The path to the database file. If no file exists at the specified path, a new file will be created. If no path or URL is provided, the plugin will create a new in-memory database. On Android, the path can either be a simple filename or a file URI. If a simple filename is provided, the plugin will create the database in the default database directory (see getDatabasePath). On iOS, the path can either be a simple filename or a file URL. If a simple filename is provided, the plugin will create the database in the default documents directory (see documentsDirectory). On Web, the path should be a simple filename without a directory (e.g., mydb.sqlite3).		0.1.0
upgradeStatements	UpgradeStatement[]	An array of upgrade statements to apply when opening the database. Each statement should specify the version of the database schema it applies to and the SQL statements to execute for the upgrade. The current version of the database schema can be checked using the PRAGMA user_version; command.		0.1.0
version	number	The version of the database schema. If provided, the plugin will check the schema version and apply migrations if necessary. If not provided, the latest version of the upgrade statements will be used, if any. Attention: The version must be 1 or higher.	1	0.1.0

UpgradeStatement

Prop	Type	Description	Since
version	number	The version of the database schema that this statement applies to.	0.1.0
statements	string[]	The SQL statement to execute for the upgrade. This can be any valid SQL statement, such as ALTER TABLE, CREATE TABLE, or INSERT.	0.1.0

QueryResult

Prop	Type	Description	Default	Since
columns	string[]	The column names in the result set.	[]	0.1.0
rows	Value[][]	The rows returned by the query. Each row is represented as an object where the keys are column names and the values are the corresponding values in that row.	[]	0.1.0

QueryOptions

Prop	Type	Description	Default	Since
databaseId	string	The unique identifier for the database to query.		0.1.0
statement	string	The SQL statement to execute for the query. This should be a SELECT statement.		0.1.0
values	Value[]	An array of values to bind to the SQL statement. Each value corresponds to a placeholder in the SQL statement.	[]	0.1.0

RollbackTransactionOptions

Prop	Type	Description	Since
databaseId	string	The unique identifier for the database to rollback a transaction on.	0.1.0

VacuumOptions

Prop	Type	Description	Since
databaseId	string	The unique identifier for the database to run the VACUUM command on.	0.1.0

Type Aliases

Value

Represents a value that can be used in SQL statements.

This can include strings, numbers, arrays of numbers (for BLOBs), or null.

Attention: On Web, arrays of numbers (BLOBs) are not supported.

string | number | number[] | null

Limitations

This plugin has some limitations on certain platforms.

iOS

The iOS implementation of this plugin has the following limitations:

• Encryption: Encryption is only supported with CocoaPods and not with Swift Package Manager (SPM).

Web

The web implementation of this plugin has the following limitations:

• BLOBs: Arrays of numbers (BLOBs) are not supported. You can only use strings, numbers, and null as values in SQL statements.

Changelog

See CHANGELOG.md.

Breaking Changes

See BREAKING.md.

License

See LICENSE.