Raw SQLite Tables to Bypass JSON View Limitations

​

When to Use Raw Tables

• Indexes - PowerSync’s default schema has basic support for indexes on columns, while raw tables give you complete control to create indexes on expressions, use GENERATED columns, etc
• Improved performance for complex queries (e.g., SELECT SUM(value) FROM transactions) - raw tables more efficiently get these values directly from the SQLite column, instead of extracting the value from the JSON object on every row
• Reduced storage overhead - eliminate JSON object overhead for each row in ps_data__<table>.data column
• To manually create tables - Sometimes you need full control over table creation, for example when implementing custom triggers

​

How Raw Tables Work

​

Current JSON-Based System

1. Download sync bucket operations from the PowerSync Service
2. Once the client has a complete checkpoint and no pending local changes in the upload queue, sync the local database with the bucket operations

​

Raw Tables Approach

1. Define how PowerSync’s schemaless protocol maps to your raw tables — see Define sync mapping for raw tables
2. Define triggers that capture local writes from raw tables — see Capture local writes with triggers

CREATE TABLE todo_lists (
   id TEXT NOT NULL PRIMARY KEY,
   created_by TEXT NOT NULL,
   title TEXT NOT NULL,
   content TEXT
) STRICT;

​

Define sync mapping for raw tables

1. A put SQL statement for upserts, responsible for creating a todo_list row or updating it based on its id and data columns.
2. A delete SQL statement responsible for deletions.

const mySchema = new Schema({
   // Define your PowerSync-managed schema here
   // ...
});
mySchema.withRawTables({
  // The name here doesn't have to match the name of the table in SQL. Instead, it's used to match
  // the table name from the backend database as sent by the PowerSync service.
  todo_lists: {
    put: {
      sql: 'INSERT OR REPLACE INTO todo_lists (id, created_by, title, content) VALUES (?, ?, ?, ?)',
      params: ['Id', { Column: 'created_by' }, { Column: 'title' }, { Column: 'content' }]
    },
    delete: {
      sql: 'DELETE FROM lists WHERE id = ?',
      params: ['Id']
    }
  }
});

​

Capture local writes with triggers

CREATE VIRTUAL TABLE powersync_crud(
   -- The type of operation: 'PUT' or 'DELETE'
   op TEXT,
   -- The id of the affected row
   id TEXT,
   type TEXT,
   -- optional (not set on deletes): The column values for the row
   data TEXT,
   -- optional: Previous column values to include in a CRUD entry
   old_values TEXT,
   -- optional: Metadata for the write to include in a CRUD entry
   metadata TEXT,
);

CREATE TRIGGER todo_lists_insert
   AFTER INSERT ON todo_lists
   FOR EACH ROW
   BEGIN
      INSERT INTO powersync_crud (op, id, type, data) VALUES ('PUT', NEW.id, 'todo_lists', json_object(
         'created_by', NEW.created_by,
         'title', NEW.title,
         'content', NEW.content
      ));
   END;

CREATE TRIGGER todo_lists_update
   AFTER INSERT ON todo_lists
   FOR EACH ROW
   BEGIN
      INSERT INTO powersync_crud (op, id, type, data) VALUES ('PUT', NEW.id, 'todo_lists', json_object(
         'created_by', NEW.created_by,
         'title', NEW.title,
         'content', NEW.content
      ));
   END;

CREATE TRIGGER todo_lists_delete
   AFTER DELETE ON todo_lists
   FOR EACH ROW
   BEGIN
      INSERT INTO powersync_crud (op, id, type) VALUES ('DELETE', OLD.id, 'todo_lists');
   END;

​

Migrations

​

Adding raw tables as a new table

INSERT INTO my_table (id, my_column, ...)
   SELECT id, data ->> 'my_column' FROM ps_untyped WHERE type = 'my_table';
DELETE FROM ps_untyped WHERE type = 'my_table';

​

Migrating to raw tables

1. Open the database with the new schema mentioning raw tables. PowerSync will copy data from tables previously managed by PowerSync into ps_untyped.
2. Create raw tables.
3. Run the INSERT FROM SELECT statement to insert ps_untyped data into your raw tables.

​

Migrations on raw tables

​

Deleting data and raw tables