PowerSync now syncs MySQL with local SQLite databases, enabling developers to create zero-latency, local-first apps for desktop, mobile, and the web.
The PowerSync MySQL database connector (alpha) is now available on both PowerSync Cloud and self-hosted PowerSync.
Bidirectional Sync to Bring Local-First to MySQL
PowerSync enables bidirectional sync between a MySQL database on the backend, and SQLite databases embedded in client applications. These applications can read and write directly on the local SQLite database which is kept in sync with MySQL through the PowerSync Service, enabling a local-first architecture which allows:
- Zero-latency UX: The UI responds instantly to the user, since there is no network latency on the critical path. This applies both to loading data and updating data.
- Real-time streaming: MySQL data updates are streamed to local databases in real-time, and reactive queries running against the local SQLite automatically update with fresh data.
- Multi-user collaboration: Users can immediately see updates made to shared data by other users.
- Offline resilience: With data available locally and writes performed locally, apps are resilient to any network interruptions, or even complete downtime of the network connection or backend.
What does PowerSync provide, and how is it used?
Here’s a quick rundown of how PowerSync works and the key features it provides:
- The PowerSync Service connects to standard MySQL databases to perform dynamic partial replication of data to users. This is controlled through Sync Rules which contain SQL-like queries that can make use of dynamic parameters.
- When Sync Rules are created, the PowerSync Service pre-processes and caches data and operation history from the MySQL database to efficiently and scalably sync to users. The PowerSync Service initially takes a snapshot of data defined in the Sync Rules, and then incrementally updates its cache whenever the data changes, using a MySQL binlog listener.
- On the client side, the developer includes a PowerSync client SDK in the app project. The developer wires up the SDK to fetch a JWT from their application backend, which is then used for authentication between the PowerSync Service and the client SDK.
- The client SDK keeps a local SQLite database in sync with MySQL in real-time, based on the Sync Rules configuration. The SDK allows for live queries from SQLite (which re-run whenever underlying data changes), allowing the developer to enable real-time reactivity in their app UI. It generally eliminates the need for a state management library.
- Whenever the app performs writes to the local SQLite database, the write transactions are also placed in an upload queue [1]. The developer defines a function used by the client SDK to upload the writes to their backend application, where custom business logic can be applied before committing the changes to the MySQL database. If the user is offline or network connectivity is unreliable, the PowerSync client SDK will automatically retry uploading the changes in the queue.
A simpler developer experience
In addition to enabling a high-quality UX for end-users, sync engines also provide a better experience for developers, by replacing imperative API calling with a declarative system.
PowerSync simplifies data transfer and state management: developers define Sync Rules and the PowerSync Service ensures that each local SQLite database contains the latest relevant data. Developers no longer need to worry about how and when APIs are called, handling error states and retries, or implementing caching and purging strategies.
Developers can work with a real local database in the front-end (SQLite) and PowerSync ensures it stays in sync with the MySQL backend database. Developers get the full power of SQLite — including joins and aggregations.
How MySQL’s binlog enables real-time sync with SQLite
PowerSync uses MySQL’s binary log and a listener that emits events for database changes in order to achieve similar functionality to Postgres’s Logical Replication.
The ZongJi Node.js library is used to connect to a MySQL server, read the binary log and emit events for database changes, including:
- INSERT: Rows added to a table.
- UPDATE: Changes to existing rows.
- DELETE: Rows removed from a table.
The PowerSync Service partially replicates data from MySQL according to Sync Rules defined by the developer, as described above. This data is then streamed to SQLite databases embedded in users’ applications, by means of the PowerSync client SDK. Client SDKs are currently available for web (JavaScript), React Native, Flutter, Kotlin Multiplatform and Swift.
Control how client-side uploads are applied to MySQL
PowerSync gives developers control over how writes from clients are applied to MySQL, by routing them through the developer’s own backend API.
On the client-side, whenever writes are made to the local SQLite database, PowerSync also places those writes into an upload queue, and then attempts to upload the transactions in the queue sequentially to the developers’ backend API, based on custom logic defined by the developer. Developers can perform server-side validations, authorization and conflict resolution in the backend API implementation that accepts the writes. The simplest backend API implementation accepts all client uploads for last-write-wins conflict resolution behavior.
PowerSync treats the server as authoritative and automatically ensures all clients converge to the server state with causal consistency.
Design goals
PowerSync has been developed with specific design goals in mind, which have remained the same as we added MySQL support:
- It should be easy to adopt for new (greenfield) and existing (brownfield) systems. It should not be invasive to the developer’s stack, especially the database: it should not require substantial reconfiguration or additions to the MySQL database.
- Dynamic partial replication is a key capability, and it should be possible to adapt the sync controls at any time without complexity.
- The database is sacrosanct: don’t write directly to their database. Don’t bypass the developer’s existing business logic, authorization and validation.
- Low maintenance. In particular, schema and data migrations should be as painless as possible.
- A simple, robust and proven architecture, not over-engineered.
- Provide strong consistency guarantees.
Get Started
Start building today! Check out PowerSync’s MySQL documentation for quick setup instructions.
Create a free PowerSync Cloud account, or self-host using Docker (note that the PowerSync Dashboard is currently not available in the self-hosted PowerSync Open Edition).
