Sync Rules From First Principles: Partial Replication To SQLite

The purpose of this blog post is to explain why you need PowerSync’s “Sync Rules”, what they are, and how they can be implemented.

Why Have Sync Rules?

In typical cloud-first apps, your backend database (like Postgres or MongoDB) holds all your data, which is made available to users via an API. This approach requires data to be fetched by client apps through API calls.

By contrast, a sync engine like PowerSync syncs all data relevant to a specific user to a local database embedded in the client-side app, allowing queries to run locally on the user's device. Data is synced through the PowerSync Service, a backend service that client apps connect to (more info on this architecture here).

Users are typically only authorized to access certain data in the backend database, and on top of that, there is typically too much data to sync to every user’s device. Therefore, it’s necessary to control which data is synced to users. In PowerSync, Sync Rules provide a way to specify which data should be synced to which users.

Sync Buckets

PowerSync has a concept of Sync Buckets (or simply "Buckets") to organize data synced to users’ devices. The reason that Sync Buckets exist is to allow sharing sets of data between users where applicable. This allows for deduplication of work and high scalability.

These Buckets contain rows of data synced from one or more tables in your backend database, based on the defined Sync Rules:

How to Define Sync Rules

Sync Rules are specified in a %%yaml%% file. They must start with %%bucket_definitions%% and follow this structure:

# sync-rules.yaml
bucket_definitions:
  bucket_name: # name of the buckets e.g. user_lists
    parameters: # (optional) query used to determine which buckets are synced 
    data: # query used to determine the data in each bucket

How Sync Rules Work: An Example

Note: This example uses the terms “tables" and "rows” and assumes a SQL backend database. However, the same ideas can be applied to collections and documents in a NoSQL context.

Let’s illustrate how to define Sync Rules with an example:

1. Data Setup: Assume there is a %%lists%% table with three columns: %%id%%, %%owner_id%%, and %%name%%, containing seven rows.
2. User Ownership: %%user1%% owns all lists with odd IDs, while %%user2%% owns all lists with even IDs.

Types of Queries

You can use two types of queries in your Sync Rules when defining Buckets:

1. Data queries — define the content of a bucket
2. Parameter queries — define which buckets to sync
   

These queries are written with a SQL-like syntax (regardless of whether a SQL or NoSQL backend database is used with PowerSync). Note that not all SQL operators and functions are supported in PowerSync’s SQL-like query syntax — refer to limitations here.

Data Queries

Data queries specify what data is included in a Bucket. In the below example, the data query is %%SELECT * FROM lists%% and is the only query used. This creates a Bucket of the lists that will sync to every single user. We refer to buckets that sync to all users as “global buckets”. In this example, all 7 rows are added to the global bucket and will be synced to all users.

In the Sync Rules YAML file:

# sync-rules.yaml
bucket_definitions:
  lists:
    data:
      - SELECT * FROM lists

‍

Parameter Queries

Parameter queries determine which Buckets should be synced to the user’s device. In the below example, the parameter query is %%SELECT request.user_id() AS id%%. The %%request%% variable provides access to the contents of the JWT token sent when making a request to the PowerSync Service. It contains a user_id (the "subject" from the JWT token) which is used to determine which buckets are synced to which device.

Consider our example %%lists%% table with the 7 rows belonging to user1 and user2. Given this parameter query and the sample data, 2 buckets would end up existing — one for user1 and user2.

This is what our Sync Rules YAML file would look like after defining the parameter query in our example. We name our bucket %%user_lists%%:

# sync-rules.yaml

bucket_definitions:
  user_lists:
    parameters: SELECT request.user_id() AS id

‍

Adding the Data Query

Now let's add the data query that defines what data is contained within the %%user_lists%% buckets:

• Parameter query: %%SELECT request.user_id() AS id%%
• Data query: %%SELECT * FROM lists WHERE owner_id = bucket.id%%
  

As explained above, these Sync Rules will result in 2 Buckets being created given the sample data: one for %%user1%% containing only their lists, and another for %%user2%% with only their lists. Each user will only see a single Bucket synced to their device.

Note that the parameter %%id%% is used in the data query as %%bucket.id%%. Every bucket parameter must be used in every data query. This is a requirement inherent to the architecture of the Bucket system related to high performance.

This is what our resulting Sync Rules YAML file now looks like:

# sync-rules.yaml

bucket_definitions:
  user_lists:
    parameters: SELECT request.user_id() AS id
    data:
      - SELECT * FROM lists WHERE owner_id = bucket.id

‍

Join Tables

Two new tables, %%todos%% and %%user_lists%%, are introduced to explain how join operations work in PowerSync.

Todos:

1. Data Setup: Assume there is a %%todos%% table with three columns: %%id%%, %%list_id%%, and %%description%%, containing seven rows.
2. Ownership:  %%list%% with ID %%1%% owns all todos with odd IDs, while %%list%% with ID %%2%% owns all todos with even IDs.

User Lists:

1. Data Setup: Assume there is a %%user_lists%% table with three columns: %%id%%, %%list_id%%, and %%user_id%%, containing seven rows.
2. Ownership:  Each %%list_id%% is matched to the %%user_id%% that owns the list.

• Parameter query: %%SELECT list_id FROM user_lists WHERE user_lists.user_id = request.user_id()%%
• Data query:
  • %%SELECT * FROM lists WHERE id = bucket.list_id%%
  • %%SELECT * FROM todos WHERE list_id = bucket.list_id%%

These Sync Rules generate a Bucket for each list. The bucket for %%list_id=1%% will include the %%list%% row and its four odd-numbered %%todos%% rows, while the bucket for %%list_id=2%% will include the %%list%% row and its three even-numbered %%todos%% rows. Buckets for the lists with IDs %%3-7%% will contain only the %%list%% row.

Further Reading

More detailed information can be found in the Sync Rules section of our documentation.

Note that there are additional possibilities around parameter queries regarding:

• Using multiple bucket parameters
• Using multiple parameter queries
  

For details on handling more complex scenarios, see here:

• Many to many relations and join tables
• Passing parameters directly from the client (instead of via the JWT)