Exploring the Capacitor SQLite API¶

Modern mobile applications often require robust local data storage solutions to manage user information, cache data, and enable offline functionality. With the Capacitor SQLite plugin from Capawesome, developers can integrate powerful SQLite database management capabilities into their Ionic and Capacitor applications, providing a unified API that simplifies cross-platform database operations while offering advanced features like encryption, migrations, and transaction support across Android, iOS, and Web platforms.

Installation¶

To install the Capacitor SQLite plugin, please refer to the Installation section in the plugin documentation.

Usage¶

Let's explore the key features of the Capacitor SQLite API and how to implement robust database operations in your Ionic applications.

Opening a Database¶

Before performing any database operations, you need to open a database connection. The Capacitor SQLite API provides the open(...) method to establish a connection to your SQLite database:

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

const openDatabase = async () => {
  const { databaseId } = await Sqlite.open({
    path: 'myapp.sqlite3',
    upgradeStatements: [
      {
        version: 1,
        statements: [
          'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE)',
          'CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT, content TEXT, user_id INTEGER, FOREIGN KEY(user_id) REFERENCES users(id))'
        ]
      }
    ]
  });

  console.log('Database opened with ID:', databaseId);
  return databaseId;
};

The open(...) method returns a unique database identifier that you'll use for subsequent database operations. The upgradeStatements parameter allows you to define database schema migrations that will be executed automatically when the database is first created or when upgrading to newer versions.

Executing SQL Statements¶

Once you have a database connection, you can execute SQL statements using the execute(...) method. This method is ideal for INSERT, UPDATE, and DELETE operations, as well as executing raw SQL commands:

const insertUser = async (databaseId: string, name: string, email: string) => {
  const { rowId } = await Sqlite.execute({
    databaseId,
    statement: 'INSERT INTO users (name, email) VALUES (?, ?)',
    values: [name, email]
  });

  console.log('Inserted user with ID:', rowId);
};

const updateUser = async (databaseId: string, userId: number, name: string) => {
  const { changes } = await Sqlite.execute({
    databaseId,
    statement: 'UPDATE users SET name = ? WHERE id = ?',
    values: [name, userId]
  });

  console.log('Number of rows updated:', changes);
};

const deleteUser = async (databaseId: string, userId: number) => {
  const { changes } = await Sqlite.execute({
    databaseId,
    statement: 'DELETE FROM users WHERE id = ?',
    values: [userId]
  });

  console.log('Number of rows deleted:', changes);
};

Always use parameterized queries with the values array to prevent SQL injection attacks. This approach ensures that user input is properly escaped and your database remains secure.

Querying Data¶

To retrieve data from your database, use the query(...) method. This method executes SELECT statements and returns the result set:

const getAllUsers = async (databaseId: string) => {
  const result = await Sqlite.query({
    databaseId,
    statement: 'SELECT id, name, email FROM users ORDER BY name'
  });

  console.log('Found', result.values?.length, 'users');
  return result.values || [];
};

const getUserById = async (databaseId: string, userId: number) => {
  const result = await Sqlite.query({
    databaseId,
    statement: 'SELECT id, name, email FROM users WHERE id = ?',
    values: [userId]
  });

  return result.values?.[0] || null;
};

const searchUsers = async (databaseId: string, searchTerm: string) => {
  const result = await Sqlite.query({
    databaseId,
    statement: 'SELECT id, name, email FROM users WHERE name LIKE ? OR email LIKE ?',
    values: [`%${searchTerm}%`, `%${searchTerm}%`]
  });

  return result.values || [];
};

The query(...) method returns an object containing a values array with the retrieved rows. Each row is represented as an object with column names as keys.

Closing the Database¶

When you're done with database operations, it's important to close the database connection to free up system resources. Use the close(...) method:

const closeDatabase = async (databaseId: string) => {
  await Sqlite.close({
    databaseId
  });

  console.log('Database connection closed');
};

Always close database connections when your application is shutting down or when you no longer need the database to ensure optimal resource management.

Advanced¶

The Capacitor SQLite API provides advanced features for more sophisticated database operations and security requirements.

Encrypting the Database¶

For applications handling sensitive data, the Capacitor SQLite plugin supports 256-bit AES encryption. You can encrypt your database by providing an encryption key when opening it:

const openEncryptedDatabase = async () => {
  const { databaseId } = await Sqlite.open({
    path: 'secure.sqlite3',
    encryptionKey: 'your-secure-encryption-key',
    upgradeStatements: [
      {
        version: 1,
        statements: [
          'CREATE TABLE sensitive_data (id INTEGER PRIMARY KEY, data TEXT)'
        ]
      }
    ]
  });

  return databaseId;
};

The encryption key should be stored securely using platform-specific secure storage mechanisms. Never hardcode encryption keys in your application code. Consider using the Capacitor Secure Preferences plugin to store encryption keys safely.

To change the encryption key for an existing database, you can use the changeEncryptionKey(...) method:

const changeEncryptionKey = async (databaseId: string, newKey: string) => {
  await Sqlite.changeEncryptionKey({
    databaseId,
    encryptionKey: newKey
  });
  console.log('Encryption key changed successfully');
};

This method allows you to update the encryption key without needing to recreate the database, ensuring that your data remains secure while allowing for key rotation.

Transactions¶

For operations that require atomicity, use the beginTransaction(...) and commitTransaction(...) methods to manage transactions effectively:

const performTransaction = async (databaseId: string) => {
  // Begin a transaction
  await Sqlite.beginTransaction({ databaseId });
  // Perform multiple operations within the transaction
  await Sqlite.execute({
    databaseId,
    statement: 'INSERT INTO users (name, email) VALUES (?, ?)',
    values: ['Alice Smith', 'alice@example.com']
  });
  await Sqlite.execute({
    databaseId,
    statement: 'INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)',
    values: [1, 'My First Post', 'Hello world!']
  });
  // Commit the transaction
  await Sqlite.commitTransaction({ databaseId });
};

This ensures that either all operations succeed or none are applied, maintaining data integrity. If any operation fails, you can roll back the transaction to revert all changes:

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

const rollbackTransaction = async (databaseId: string) => {
  await Sqlite.rollbackTransaction({
    databaseId
  });
};

Vacuuming¶

To optimize database performance and reclaim unused space, use the vacuum(...) method periodically:

const optimizeDatabase = async (databaseId: string) => {
  await Sqlite.vacuum({
    databaseId
  });

  console.log('Database optimized');
};

Vacuuming rebuilds the database file, removing deleted data and defragmenting the database. This operation can improve query performance and reduce file size, especially after large amounts of data have been deleted.

Limitations¶

While the Capacitor SQLite plugin provides comprehensive cross-platform support, there are some platform-specific limitations to consider.

iOS¶

On iOS, database encryption is only available when using CocoaPods for dependency management. If you're using Swift Package Manager (SPM), encryption features will not be available.

Web¶

On the Web platform, BLOB data types are not fully supported due to browser restrictions. When targeting the Web platform, consider alternative approaches for handling binary data.

Best Practices¶

When working with the Capacitor SQLite API, consider these best practices to ensure optimal performance and security:

Use parameterized queries: Always use the values parameter in your SQL statements to prevent SQL injection attacks. Never concatenate user input directly into SQL strings, as this creates security vulnerabilities that can compromise your entire database.
Implement proper error handling: Wrap database operations in try-catch blocks and provide meaningful error messages to users. Database operations can fail due to various reasons such as disk space, permissions, or constraint violations, so robust error handling is essential.
Manage database connections efficiently: Open database connections when needed and close them when done to prevent resource leaks. Consider implementing a connection pool pattern for applications with frequent database access to optimize performance while managing resource usage effectively.

Conclusion¶

The Capacitor SQLite Plugin from Capawesome provides a comprehensive solution for integrating robust database management into Ionic applications. By offering a unified API across multiple platforms with advanced features like encryption, transactions, and migrations, it enables developers to create sophisticated data-driven applications without the complexity of platform-specific database implementations.

To stay updated with the latest updates, features, and news about the Capawesome, Capacitor, and Ionic ecosystem, subscribe to the Capawesome newsletter and follow us on X (formerly Twitter).

If you have any questions or need assistance with the Capacitor SQLite Plugin, feel free to reach out to the Capawesome team. We're here to help you implement robust database solutions in your Ionic applications.