Using SQLite Databases in Routzie Machines

Routzie uses SQLite databases to store and manage data for your machines. You can create a database, assign it to one or more environments, and use standard SQL queries to interact with your data.

Creating and Assigning a Database

• Create a Database: Use the machine settings button (top right corner) to create a new SQLite database for your machine.

• Assign a Database: Go to the environment settings in the environment section and select the database you want to use. Multiple environments can share the same database.

Example Usage

Here are several practical examples showing how to use the Routzie database API with SQLite:

1. Create a Table and Insert a Timestamp

bootstrap(async () => {
  // Create a table for logs
  await database.query(`
    CREATE TABLE IF NOT EXISTS logs (
      timestamp INTEGER NOT NULL
    );
  `);

  // Insert the current timestamp
  await database.query(`
    INSERT INTO logs (timestamp) VALUES (?);
  `, [Date.now()]);

  // Retrieve all log entries
  const logs = await database.query(`SELECT * FROM logs;`);
  console.log('All log entries:', logs);
});

2. Store and Retrieve Key-Value Pairs

bootstrap(async () => {
  // Create a key-value table
  await database.query(`
    CREATE TABLE IF NOT EXISTS kv (
      key TEXT PRIMARY KEY,
      value TEXT
    );
  `);

  // Insert a value
  await database.query(`
    INSERT OR REPLACE INTO kv (key, value) VALUES (?, ?);
  `, ['username', 'routzie']);

  // Get a value by key
  const result = await database.query(`SELECT value FROM kv WHERE key = ?;`, ['username']);
  console.log('Username:', result[0]?.value);
});

3. Count Records in a Table

bootstrap(async () => {
  // Count log entries
  const count = await database.query(`SELECT COUNT(*) as total FROM logs;`);
  console.log('Total log entries:', count[0]?.total);
});

4. Delete Old Records

bootstrap(async () => {
  // Delete logs older than 24 hours
  const cutoff = Date.now() - 24 * 60 * 60 * 1000;
  await database.query(`DELETE FROM logs WHERE timestamp < ?;`, [cutoff]);
});

Example Output

Create [ { changes: 0, lastInsertRowid: 1 } ]
Insert [ { changes: 1, lastInsertRowid: 2 } ]
All log entries [ { timestamp: 1756728599990 }, { timestamp: 1756728606033 } ]
Username: routzie
Total log entries: 2

API Reference

• database.query(sql: string, params?: any[]): Promise<any[]> Executes an SQL query on the SQLite database. Use ? placeholders for parameters.

Tips

• Routzie uses SQLite, so you can use any valid SQLite query.
• The database is persistent and can be shared across environments.
• For more advanced usage, refer to the Routzie documentation or the official SQLite documentation.

API Reference

• database.query(sql: string, params?: any[]): Promise<any[]> Executes an SQL query. Use ? placeholders for parameters.

Tips

• You can use any valid SQLite query.
• The database is persistent and can be shared across environments.
• For more advanced usage, refer to the Routzie documentation or SQLite documentation.