Storing Data in SQLite

For storing data generated by your plugin, Caido utilizes SQLite databases.

SQLite is a lightweight database engine made available via a small library. It requires no setup, administration or separate server. Instead, all data is stored in a single file.

Getting a Database

The sdk.meta.db() utility provides a SQLite database specific to your plugin. You can view the location of the generated file using sdk.meta.path():

const db = await sdk.meta.db();

const dataPath = sdk.meta.path();
sdk.console.log(`Database will be stored in: ${dataPath}`);

TIP

To create a database at different location, use open:

import { open } from 'sqlite'

async function newDatabase() {
  const db = await open({ filename: "path/to/database.sqlite" });
  await db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);");
  await db.exec("INSERT INTO test (name) VALUES ('foo');");
}

Creating Tables

You can run direct SQL statements by supplying them as an arguement to the .exec() method:

// Create a new table if it doesn't exist.
// This will create a table named "test" with two columns: id and name.
await db.exec(`
  CREATE TABLE IF NOT EXISTS test (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
  );
`);

Inserting Rows

Instead of using direct statements every time you want to add to a table, you can use .prepare() to create predefined statements with placeholders, marked with (?) for new entry values.

Then, you can execute these prepared statements with the .run() method that takes the placeholder values as arguements:

const insertStatement = await db.prepare("INSERT INTO test (name) VALUES (?)");

// Execute the insert statement to add "Ninjeeter" as a name entry.
const result = await insertStatement.run("Ninjeeter");

Using .lastInsertRowid will return the ID of the last inserted row:

console.log(`Inserted row with ID: ${result.lastInsertRowid}`);

Retrieving Data

To select all the columns in a table and return every row, use the wildcard character * and the .all() method:

const selectStatement = await db.prepare("SELECT * FROM test");
const rows = await selectStatement.all();
sdk.console.log("Current records: " + JSON.stringify(rows));

You can return specific rows by preparing a statement and using the .get() method which takes an arguement that will be used to match the table entry:

// Prepare a statement to get a single row by ID
const getByIdStatement = await db.prepare("SELECT * FROM test WHERE id = ?");

// Returns the first matching row or undefined if none found.
const row = await getByIdStatement.get(1); // Get row with ID 1.

if (row) {
    sdk.console.log(`Found record: ${JSON.stringify(row)}`);
} else {
    sdk.console.log("No record found with that ID");
}

/packages/backend/src/index.ts

import type { DefineAPI, SDK } from "caido:plugin";

async function initDatabase(sdk: SDK) {
  try {
    const db = await sdk.meta.db();
    
    const dataPath = sdk.meta.path();
    sdk.console.log(`Database will be stored in: ${dataPath}`);
    
    await db.exec(`
      CREATE TABLE IF NOT EXISTS test (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL
      );
    `);

    const insertStatement = await db.prepare("INSERT INTO test (name) VALUES (?)");
    const result = await insertStatement.run("foo");
    sdk.console.log(`Inserted row with ID: ${result.lastInsertRowid}`);

    const selectStatement = await db.prepare("SELECT * FROM test");
    const rows = await selectStatement.all();
    sdk.console.log("Current records: " + JSON.stringify(rows));

    const getByIdStatement = await db.prepare("SELECT * FROM test WHERE id = ?");

    const row = await getByIdStatement.get(1);

    if (row) {
        sdk.console.log(`Found record: ${JSON.stringify(row)}`);
    } else {
        sdk.console.log("No record found with that ID");
    }

    return db;
  } catch (error) {
    sdk.console.error(`Database initialization failed: ${error}`);
    throw error;
  }
}

export type API = DefineAPI<{
}>;

export async function init(sdk: SDK<API>) {
  await initDatabase(sdk);
  sdk.console.log("Database initialized.");
}

The Result

In your backend logs, you will see the following entries:

Database will be stored in: [~]\Caido\data\plugins\[PLUGIN UUID]
Inserted row with ID: 1
Current records: [{"id":1,"name":"Ninjeeter"}]
Found record: {"id":1,"name":"Ninjeeter"}