Skip to main content
A major product principle that has guided us is to provide an open-source real database on the client-side, with a specific focus on SQLite. This is as opposed to some kind of cache, key-value store or non-standards-based relational datastore.This approach leverages the power of SQLite and its ecosystem:
  • SQL functionality & concepts: Millions of developers are already well-versed in SQL constructs and syntax, which means that there’s an instant familiarity with using SQLite. It also means having access to its rich functionality such as aggregations, joins, advanced indexing and JSON support.
  • Ecosystem & extensibility: SQLite brings a lot with it: You can use popular ORMs that you’re already familiar with, such as Drizzle, Kysely and Drift. You can use SQLite extensions such as SQLCipher for encryption and FTS5 for full-text search. You can use standard tools for inspecting the database and doing more in-depth debugging. You get all the benefits of the SQLite community and the innovation around it: SQLite just keeps becoming more popular, and people keep doing more new interesting things with it.
  • Performance & maturity: SQLite is also really fast, and extremely battle-tested: the SQLite team estimates that there are more than a trillion SQLite databases deployed, and every line in the codebase has 600 lines of test code.
The ubiquity of SQLite also creates opportunities for adopting PowerSync in the SQLite “installed base”: wherever you find SQLite, you can likely use PowerSync too. Usage of SQLite also means low lock-in. PowerSync is designed to be a “pluggable middleware” layer rather than a high lock-in monolithic system. It sits between popular backend databases on the server-side, and SQLite on the client-side. Replacing it with a different sync engine is fairly straightforward. Since PowerSync is built to work with open technologies and is itself open too, you can have an end-to-end stack optimized for low risk.
PowerSync uses near real-time streaming of changes to the client (< 1s delay).A persistent connection is used to continuously stream changes to the client.This is implemented using a standard HTTP/2 request with a streaming response, or WebSockets.A polling API will also be available for cases where the client only needs to update data periodically, and prefers to not keep a connection open.The real-time streaming is not designed for “update as you type” — it still depends on explicitly saving changes. Real-time collaboration is supported as long as users do not edit the same data (same columns of the same rows) at the same time.Concurrently working on text documents is not supported out of the box. This is solved better by CRDTs — see the CRDTs section.
See the section on Performance and Limits.
If no sync rule changes were deployed in this period, the user will only need to download the incremental changes that happened since the user was last connected.
For example, a new record should not be displayed until the server received it, or it should be displayed as pending, or the entire screen must block with a spinner.While PowerSync does not have out-of-the-box support for this due to the great variety of requirements, this is easy to build on top of the sync system. A simple approach is to store a “status” or “pending changes” column on the table, and set that whenever the client makes a change. When the server receives the change, it then sets it to “processed” / “no pending changes”. So when the server has processed the change, the client automatically syncs that status back.For more granular information, record individual changes in a separate table, as explained in Custom Conflict Resolution.Note: Blocking the entire screen with a spinner is not recommended, since the change may take a very long time to be processed if the user is offline.
Right now, we don’t have support for replicating data via APIs. A workaround would be to have custom code to replicate the data from the API to a PostgreSQL instance, then sync that with PowerSync. We may add a way in the future to replicate the data directly from an API to the PowerSync Service, without a database in between.
Yes. The PowerSync Client SDKs support real-time streaming of changes, and can automatically rerun a query if the underlying data changed. It does not support incrementally updating the result set yet, but it should be fast if the query is indexed appropriately, and the result set is small enough.
Client-side transactions are supported, and use standard SQLite locking to avoid conflicts. Client-server transactions are not supported. This would require online connectivity to detect conflicts and retry the transaction, which is not possible for changes made offline. Instead, it is recommended to model the data to allow atomic changes (see previous sections on conflict detection).
This is generally not recommended, but it can be used in some cases, with caveats.See the section on client ID for details.
An attachment sync or caching system can be built on top of PowerSync.See the section on Attachments for details.
Currently, PowerSync can only read from Postgres databases directly. GraphQL or REST APIs can be used for the write path by the PowerSync SDK.
By default PowerSync is not susceptible to SQL injection. The PowerSync execute API is parameterized, and as long as developers use that, SQL injection is not possible. It is however the developer’s responsibility to ensure that they use the parameterized API and don’t directly insert user-provided data into underlying SQLite tables.
getCrudBatch() getNextCrudTransaction()Use getCrudBatch() when you don’t care about atomic transactions, and want to do bulk updates for performance reasons.
PowerSync will only sync the difference (buckets added or removed).