Announcing the SQLite Plugin for Capacitor

We are thrilled to announce the launch of our comprehensive Capacitor SQLite plugin. This powerful database solution brings enterprise-grade SQLite functionality to your Capacitor applications with support for encryption, transactions, schema migrations, and seamless cross-platform compatibility across Android, iOS, and web. The plugin is now available for all Capawesome Insiders.

Let's explore the API and key features that make this plugin a must-have for modern app development.

Installation

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

Usage

The Capacitor SQLite plugin offers a complete database management solution with intuitive APIs for all your data operations. Let's walk through the essential features that make this plugin indispensable for modern app development.

Opening a database

Start by opening a database connection with the open(...) method. This method supports both file-based and in-memory databases, with optional encryption and automatic schema migrations:

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

const openDatabase = async () => {
  const { databaseId } = await Sqlite.open({
    encryptionKey: 'your-secret-key',
    path: 'db.sqlite3',
    upgradeStatements: [
      {
        version: 1,
        statements: [
          'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE)',
          'CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT, content TEXT)'
        ]
      }
    ],
    version: 1
  });

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

If no database file exists, it will be created automatically. The encryptionKey parameter enables database encryption, ensuring your data remains secure. The upgradeStatements parameter enables seamless database migrations, automatically applying schema changes when your app updates.

If you omit the path option, the plugin will create an in-memory database, which is extremely useful for testing or temporary data storage.

Executing SQL statements

Execute data modification operations like INSERT, UPDATE, and DELETE using the execute(...) method. This method supports parameterized queries to prevent SQL injection attacks and ensure data integrity:

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

const insertUser = async (databaseId: string) => {
  const { rowId } = await Sqlite.execute({
    databaseId,
    statement: 'INSERT INTO users (name, email) VALUES (?, ?)',
    values: ['John Doe', 'john.doe@example.com']
  });
  console.log('Inserted user with ID: ', rowId);
};

const updateUser = async (databaseId: string, userId: number) => {
  const { changes } = await Sqlite.execute({
    databaseId,
    statement: 'UPDATE users SET email = ? WHERE id = ?',
    values: ['john.updated@example.com', userId]
  });
  console.log('Updated user, number of rows affected: ', changes);
};

For multiple operations, leverage transaction support to ensure data consistency:

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 });
};

You can also roll back transactions using the rollbackTransaction(...) method if an error occurs during the transaction.

Querying data

To retrieve data, use the query(...) method. This method supports complex SQL queries, including joins and aggregations, and returns results in a structured format:

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

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

  console.log('Found users:', result.values);
  // Output: [{ id: 1, name: 'John Doe', email: 'john.doe@example.com' }]

  return result.values;
};

const getUserWithPosts = async (databaseId: string, userId: number) => {
  const result = await Sqlite.query({
    databaseId,
    statement: `
      SELECT u.name, u.email, p.title, p.content 
      FROM users u 
      LEFT JOIN posts p ON u.id = p.user_id 
      WHERE u.id = ?
    `,
    values: [userId]
  });

  return result.values;
};

Closing the database

As soon as you are done with the database operations, it is a good practice to close the database connection using the close(...) method. This helps free up resources and ensures data integrity:

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

const closeDatabase = async (databaseId: string) => {
  await Sqlite.close({ databaseId });
  console.log('Database connection closed');
};

FAQ

Is the Capawesome SQLite plugin a fork of another plugin?

No, it is NOT a fork. The Capawesome SQLite plugin was developed from scratch over a period of 2 months, with a focus on performance, reliability, and ease of use. It is designed to be fully compatible with the Capacitor ecosystem while providing additional features and optimizations.

How does the Capawesome SQLite plugin compare to other SQLite plugins?

Most other SQLite plugins are maintained by the community under an open-source license. While this makes it possible to offer the plugin free of charge, it does not ensure that the plugin receives regular updates and support. This can be a significant drawback, especially for commercial applications that require ongoing maintenance, feature enhancements and top-notch support. With the end of Ionic Secure Storage, there is now a need for a high-quality solution.

Is the Capawesome SQLite plugin compatible with the Capacitor Community SQLite plugin?

Yes, the Capawesome SQLite plugin is designed to be compatible with the Capacitor Community SQLite plugin. This means you can easily migrate your existing applications to the Capawesome SQLite plugin without significant code changes. We will provide a migration guide to help you transition smoothly.

Conclusion

The Capacitor SQLite plugin delivers a comprehensive database solution that scales with your application's needs. From simple data storage to complex relational operations with encryption and migrations, this plugin provides the foundation for robust data management in your Capacitor applications.

Explore the complete API Reference to discover advanced features like read-only databases, custom encryption configurations, and platform-specific optimizations. Have suggestions for new features? Create a feature request in our GitHub repository.

Stay connected with us on X for the latest updates and announcements.