Use raw tables for native SQLite functionality and improved performance.
0.8.0
, React-Native: 1.23.0
, Web: 1.24.0
)package:powersync
.FOREIGN KEY
and ON DELETE CASCADE
constraintsGENERATED
columns, etcSELECT 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 rowps_data__<table>.data
columnps_data__<table_name>
structure containing only an id
(TEXT) and data
(JSON) column.
PowerSync automatically creates views on that table that extract JSON fields to resemble standard tables reflecting your schema.
todo_lists
table instead of ps_data__
, PowerSync needs the SQL statements extracting
columns from the untyped JSON protocol used during syncing.
This involves specifying two SQL statements:
put
SQL statement for upserts, responsible for creating a todo_list
row or updating it based on its id
and data columns.delete
SQL statement responsible for deletions.delete
statements can reference the id of the affected row, while put
statements can also reference individual column values.
Declaring these statements and parameters happens as part of the schema passed to PowerSync databases:
Schema()
object. Instead, add them afterwards using withRawTables
.
For each raw table, specify the put
and delete
statement. The values of parameters are described as a JSON
array either containing:Id
to reference the id of the affected row.{ Column: name }
to reference the value of the column name
.CREATE TABLE
statement before connect()
-ing the database.
powersync_crud
with these columns:
ps_untyped
. When adding regular tables, PowerSync will automatically extract rows from ps_untyped
.
With raw tables, that step is your responsibility. To copy data, run these statements in a transaction after creating the table:
connect()
without them) - you only
need this for raw tables you already had locally.
Another workaround is to clear PowerSync data when changing raw tables and opt for a full resync.
ps_untyped
.INSERT FROM SELECT
statement to insert ps_untyped
data into your raw tables.disconnectAndClear()
, don’t affect raw tables.
This should be kept in mind when you’re using those methods - data from raw tables needs to be deleted explicitly.