@capacitor-community/sqlite

The plugin supports the SQLite RETURNING clause for INSERT, DELETE, and UPDATE operations. This allows you to retrieve data from the rows that were modified by the statement.

This functionality is implemented via the run and executeSet methods and is controlled by a mode parameter. When using RETURNING, the capSQLiteChanges result object is amended to include a values field: {changes: {changes: number, lastId: number, values: any[]}}.

Available modes:

• 'all': Returns all modified rows in the values array.
• 'one': Returns only the first modified row in the values array.
• 'no': (Default) Does not return any modifications in the values array.

// Example of using 'all' mode with run
const resI: any = await db.run("INSERT INTO test (name,email) VALUES ('Jeepq','jeepq@example.com') RETURNING *;", [], true, 'all');
// resI.changes.values will contain the inserted rows

// Example of using 'one' mode with executeSet
let setUsers = [
  { statement: "INSERT INTO test (name,email) VALUES ('Valley','valley@example.com') RETURNING name;", values: [] }
];
const resS2 = await db.executeSet(setUsers, false, 'one');
// resS2.changes.values will contain the first modification

The @capacitor-community/sqlite plugin supports an incremental database upgrade process (introduced in version 4.1.0-6). Instead of the plugin attempting to automatically reconcile schema changes by copying tables, you define a list of incremental changes for each new version. This approach is similar to migrations in frameworks like Laravel or Doctrine.

Each version object in your upgrade array defines a toVersion and a statements array. The plugin executes these statements sequentially for every version between the current database version and the target version. Each set of statements is executed within a transaction to ensure atomicity. If any statement fails, the plugin restores the database from a backup created before the upgrade process started.

const version1 = {
  toVersion: 1,
  statements: [
    `CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY NOT NULL, email TEXT UNIQUE NOT NULL, name TEXT);`,
  ],
};

const version2 = {
  toVersion: 2,
  statements: [`ALTER TABLE users ADD COLUMN age INTEGER;`],
};

const version3 = {
  toVersion: 3,
  statements: [`UPDATE users SET name = 'Guest' WHERE name IS NULL;`],
};

By default, the methods execute, executeSet, and run are inherently transactional. This means each individual call is treated as a single transaction.

While this is secure and suitable for single operations triggered by UI components, it can be significantly slow when performing a large batch of commands because each command incurs the overhead of its own transaction.

On the Web platform, if you use the jeep-sqlite web component, the in-memory database is saved to the store after the execution of each method if the autosave property is enabled.

const testTransactionDefault = async (db: SQLiteDBConnection) => {
  if (db !== null) {
    // Each of these is its own transaction
    await db.execute('DELETE FROM DemoTable');
    await db.run('INSERT INTO DemoTable (name, score) VALUES (?, ?);', ['Sue', 102]);
    await db.execute("INSERT INTO DemoTable (name, score) VALUES ('Andrew',415);");
    const ret = await db.executeSet(setScores);
    const retQuery = await db.query("SELECT * FROM Demotable;");
  }     
}

The plugin automatically appends the suffix SQLite and the extension .db to your database name (e.g., foo becomes fooSQLite.db). If you provide a name with .db already included, the extension is removed before the suffix is added (e.g., foo.db becomes fooSQLite.db).

Platform Locations:

• Android: data/data/YOUR_PACKAGE/databases
• iOS:
  • Default: The Documents folder of your application.
  • Custom: The folder specified in capacitor.config.ts via plugins.CapacitorSQLite.iosDatabaseLocation. Note that custom locations are not visible to iTunes and are not backed up to iCloud.
• Electron:
  • Default (since 2.4.2-1): User/Databases/APP_NAME/
  • Custom (since 3.4.1): Set via capacitor.config.ts using electronMacLocation, electronWindowsLocation, or electronLinuxLocation.
• Web:
  • Stored in browser IndexedDB as a localforage store named jeepSqliteStore in a databases table.

// Example iOS custom location configuration
const config: CapacitorConfig = {
  plugins: {
    CapacitorSQLite: {
      "iosDatabaseLocation": "Library/CapacitorDatabase"
    }
  }
};

// Example Electron custom location configuration
const config: CapacitorConfig = {
  plugins: {
    CapacitorSQLite: {
      electronMacLocation: "/YOUR_DATABASES_PATH",
      electronWindowsLocation: "C:\\ProgramData\\CapacitorDatabases",
      electronLinuxLocation: "/home/CapacitorDatabases"
    }
  }
};