Database API (SQLite)

Each resource gets its own SQLite database for structured data storage. The database is created automatically on first use — just start executing SQL.

Capability required: database:resource (plain) or database:resource:encrypted (SQLCipher encrypted)

Frontend (iframe)

import { createResourceClient } from "@rightplace/sdk";

const rp = createResourceClient();
await rp.ready();

// Create a table
await rp.db.execute(`
  CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    created_at INTEGER DEFAULT (unixepoch())
  )
`);

// Insert data with parameters
await rp.db.execute(
  "INSERT INTO notes (title, content) VALUES (?1, ?2)",
  ["My First Note", "Hello world!"]
);

// Query data
const notes = await rp.db.query("SELECT * FROM notes ORDER BY created_at DESC");
// → [{ id: 1, title: "My First Note", content: "Hello world!", created_at: 1713250000 }]

// Query with parameters
const results = await rp.db.query(
  "SELECT * FROM notes WHERE title LIKE ?1",
  ["%First%"]
);

// Update
const result = await rp.db.execute(
  "UPDATE notes SET content = ?1 WHERE id = ?2",
  ["Updated content", 1]
);
console.log(result.rowsAffected); // 1

// Delete
await rp.db.execute("DELETE FROM notes WHERE id = ?1", [1]);

Backend (Node.js)

import { createResourceServer } from "@rightplace/sdk/server";

const server = createResourceServer({
  onInit: async ({ rp }) => {
    await rp.db.execute(`
      CREATE TABLE IF NOT EXISTS items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price REAL,
        in_stock INTEGER DEFAULT 1
      )
    `);
  },

  methods: {
    addItem: async (params, { rp }) => {
      const result = await rp.db.execute(
        "INSERT INTO items (name, price) VALUES (?1, ?2)",
        [params.name, params.price]
      );
      return { id: result.lastInsertId };
    },

    getItems: async (_params, { rp }) => {
      return rp.db.query("SELECT * FROM items WHERE in_stock = 1 ORDER BY name");
    },
  },
});

server.start();

API Reference

rp.db.execute(sql, params?)

Execute a SQL statement (INSERT, UPDATE, DELETE, CREATE TABLE, etc.).

Parameter	Type	Description
sql	string	SQL statement
params	unknown[] (optional)	Positional parameters (?1, ?2, …)
Returns	Promise<{ rowsAffected: number, lastInsertId: number }>

rp.db.query(sql, params?)

Execute a SELECT query and return rows.

Parameter	Type	Description
sql	string	SQL SELECT statement
params	unknown[] (optional)	Positional parameters (?1, ?2, …)
Returns	Promise<Record<string, unknown>[]>	Array of row objects

Each row is an object with column names as keys.

Parameter Types

JavaScript Type	SQLite Type
string	TEXT
number (integer)	INTEGER
number (float)	REAL
boolean	INTEGER (1 or 0)
null	NULL

Encrypted Databases

Use the database:resource:encrypted capability for sensitive data. The database is encrypted with SQLCipher using the user’s encryption key.

{
  "capabilities": [
    "database:resource:encrypted"
  ]
}

The API is identical — encryption is transparent. The only difference is that the database file on disk is encrypted and cannot be opened with standard SQLite tools.

Plain Databases

Use database:resource for non-sensitive data. The database is a standard SQLite file.

{
  "capabilities": [
    "database:resource"
  ]
}

Storage Location

The database is stored at:

~/.rightplace/{user_id}/resources/{manifest_id}/resource.db

One database per resource. Use tables for logical separation. The database persists across app restarts and resource updates. It is deleted when the resource is uninstalled.

Tips

• Use CREATE TABLE IF NOT EXISTS — your resource may be opened multiple times
• Use parameterized queries (?1, ?2) — never concatenate user input into SQL
• Use INTEGER PRIMARY KEY AUTOINCREMENT for auto-incrementing IDs
• SQLite supports JSON functions: json(), json_extract(), json_array()
• Use unixepoch() for timestamps