Skip to content

Instantly share code, notes, and snippets.

@TouristShaun

TouristShaun/electricSQL.md

Last active November 30, 2023 04:19
Show Gist options
  • Save TouristShaun/eb8a089f1d003a715f1ed2856009ce50 to your computer and use it in GitHub Desktop.
Save TouristShaun/eb8a089f1d003a715f1ed2856009ce50 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
electricSQL.md

ElectricSQL

ElectricSQL is a powerful tool designed for building reactive, real-time, local-first apps directly on Postgres, facilitating data synchronization between Postgres in the cloud and SQLite in the local app. Here's a comprehensive overview of ElectricSQL's features, setup, and usage:

Key Features

  1. Access rules, Sync shapes, Live queries, Local writes: ElectricSQL offers advanced control over data access, dynamic partial replication, type-safe and reactive live queries that run purely against local SQLite data, and conflict-free local data writes【10†source】【11†source】【12†source】【13†source】.
  2. Modern UX: It emphasizes instant reactivity, real-time multi-user collaboration, and conflict-free offline operation using CRDTs, Rich-CRDTs, and strong eventual consistency【14†source】.
  3. Compatibility with Existing Stack: It's compatible with any Postgres-based system, is open-source under the Apache 2.0 license, and supports self-hosting with simple infrastructural requirements【15†source】【16†source】【17†source】.

Setup and Usage

  1. Initial Setup: To start using ElectricSQL, ensure you have Docker and Node.js (>=16.11). You can set up quickly using the create-electric-app starter app or install components manually【30†source】.
  2. Postgres Database: ElectricSQL requires Postgres with logical replication enabled. You can use hosted services like Crunchy Data or run Postgres using Docker【31†source】.
  3. Electric Sync Service: This service can be run using Docker. It's essential for managing data replication between Postgres and SQLite【32†source】.
  4. TypeScript Client: Add the electric-sql library to your app, and use a prebuild script to generate a type-safe database client【33†source】.
  5. Defining Schema: Use standard Postgres data model and DDLX statements to define and evolve your schema【34†source】.
  6. Data Exposition: Use DDLX rules to "electrify" tables, enabling them to sync, and assign roles and access permissions【35†source】.
  7. Authentication: Authenticate the local app with the replication protocol using a JSON Web Token (JWT)【36†source】.
  8. Instantiating Client: Wrap your SQLite driver with ElectricSQL’s database client【37†source】.
  9. Data Synchronization: Manage data syncing into the local database using Shapes【38†source】.
  10. Data Operations: Perform read and write operations on your data using Live queries and Local writes【39†source】.

Prerequisites

  • NodeJS: Version 16.11 or higher【48†source】.
  • Docker: Required for running the Electric sync service【48†source】.
  • Elixir: Optional, only needed for compiling and running the sync service directly【48†source】.

Data Modelling and Migrations

  1. ElectricSQL and Postgres Data Model: It works with and on top of a Postgres data model, using migrations to define, evolve, and expose parts of this data model【54†source】【55†source】.
  2. Migration Proxy: Schema migrations to electrified tables must be applied via a proxy server integrated into the Electric application, serving multiple purposes including managing DDLX syntax, capturing migrations, and validating changes【56†source】.
  3. Electrification Process: Tables must be electrified to be part of the replication machinery. This is done using the ALTER TABLE ... ENABLE ELECTRIC DDLX statement. Tables can also be unelectrified using ALTER TABLE ... DISABLE ELECTRIC【60†source】.

Adoption Pathways

  • Incremental Adoption: ElectricSQL is designed to provide an incremental adoption pathway for existing applications【20†source】.
  • Switching from REST, Redux, GraphQL: ElectricSQL offers alternatives to REST API endpoints, state management frameworks like Redux, and GraphQL layers, emphasizing real-time multi-user capabilities and local-writes without rollbacks【21†source】【22†source】【23†source】.

Getting Started

  • Quickstart Guide: A comprehensive guide is available to help new users get up and running with ElectricSQL【24†source】.
  • Community and Support: Join the ElectricSQL community on Discord and GitHub for support and collaboration【25†source】【26†source】.

This overview provides a solid foundation for utilizing ElectricSQL in your projects. The detailed setup instructions and comprehensive documentation available on the ElectricSQL website will guide you through the process of integrating this tool into your applications.

Yes, you can integrate ElectricSQL as part of a local note-taking app installation. Here's a general approach on how you might do it:

1. App Design and Architecture

  • Frontend: Choose a frontend framework suitable for your note-taking app, like React or Vue. Your frontend will interact with the local SQLite database through ElectricSQL.
  • Backend: Use Postgres as your backend database. ElectricSQL will sync data between the Postgres server and the local SQLite database in your app.
  • Sync Layer: ElectricSQL will serve as the sync layer, managing data synchronization between the local SQLite database and the remote Postgres database.

2. Setting Up ElectricSQL

  • Install Necessary Tools: Ensure Node.js and Docker are installed on the user's machine as part of the app installation process. You can automate this or provide instructions for users.
  • Database Setup: Your app's installer should set up a local SQLite database and configure it to sync with a remote Postgres database using ElectricSQL.
  • Electrify Tables: Use DDLX statements to electrify tables in your Postgres database that will store note-taking data, enabling them to sync with the local SQLite database.

3. App Features

  • Local First Design: Design the app to primarily interact with the local SQLite database for immediate responsiveness. Changes are synced back to the Postgres database via ElectricSQL.
  • Live Queries: Implement live queries for real-time updates in the note-taking app, ensuring that changes made on other devices are reflected in the app instantly.
  • Conflict Resolution: Leverage ElectricSQL's conflict resolution mechanisms to handle any data conflicts that might occur due to offline changes or simultaneous edits from multiple devices.

4. Packaging and Distribution

  • Automated Installation: Create an installation package that automates the setup of Node.js, Docker, SQLite, and the initial configuration of ElectricSQL.
  • User Authentication: Include a mechanism for user authentication to ensure secure access and syncing of personal note data.

5. User Experience

  • Offline Support: Design the app to function offline, with ElectricSQL syncing changes once the connection is restored.
  • Reactivity: Ensure that the app is reactive, with immediate updates to the UI upon data changes.

6. Testing and Deployment

  • Thorough Testing: Test the app extensively to ensure seamless synchronization between the local and remote databases, especially handling edge cases like network outages.
  • Documentation: Provide clear documentation for users on how to install and use the app, including troubleshooting common issues.

7. Updates and Maintenance

  • App Updates: Plan for delivering app updates, possibly including updates to the ElectricSQL setup.
  • User Support: Set up a support channel for users to report issues or seek help.

By integrating ElectricSQL into your note-taking app, you can offer a robust, real-time, and responsive user experience that leverages the power of local-first data access with the reliability of a Postgres backend.

When considering integrating ElectricSQL into your project, there are a few additional points and considerations that might be valuable:

Scalability and Performance

  • Horizontal Scalability: ElectricSQL is designed to be horizontally scalable. This means as your user base grows, you can scale the backend services to maintain performance.
  • Performance Implications: Syncing large amounts of data or handling numerous concurrent users might require optimization and tuning of both the ElectricSQL sync service and your Postgres setup.

Data Security and Privacy

  • Encryption: While ElectricSQL handles data synchronization, you should ensure that data at rest (in SQLite and Postgres) and in transit (between SQLite and Postgres) is encrypted according to best practices.
  • Compliance: Ensure your application complies with relevant data protection regulations (like GDPR or HIPAA), especially since you're dealing with potentially sensitive user data.

Conflict Management

  • Conflict Resolution Strategies: Understand and define how your application will handle conflicts in data synchronization. ElectricSQL provides mechanisms for conflict-free synchronization, but you may need to implement additional logic specific to your app's use case.

Customization and Extensibility

  • Custom Sync Logic: Depending on your app’s requirements, you might need to implement custom sync logic or modify data before it's synced to ensure consistency and integrity.
  • Extending Functionality: Investigate how you can extend ElectricSQL's functionality, such as integrating with additional services or using plugins, if available.

Community and Support

  • Community Resources: Being an open-source project, leveraging community resources like forums, GitHub repositories, or Discord channels can be invaluable for support, tips, and staying updated on new features or changes.
  • Contributing to the Project: As a user of ElectricSQL, you might encounter scenarios or develop solutions that could benefit others. Contributing these back to the community can help improve the tool.

Version Compatibility

  • Keeping Up-to-date: Stay informed about updates to ElectricSQL, Node.js, Docker, and any other integral parts of your tech stack to ensure compatibility and security.
  • Deprecation Policies: Be aware of ElectricSQL’s deprecation policies and update strategies to avoid disruptions in your application.

Monitoring and Analytics

  • Sync Monitoring: Implement monitoring to track the performance of data synchronization, identify bottlenecks, and detect issues early.
  • Usage Analytics: Collect analytics on how users interact with the sync features, which can be invaluable for future improvements.

Backup and Recovery

  • Data Backup: Regularly back up both the Postgres and SQLite databases to prevent data loss.
  • Disaster Recovery Plan: Have a disaster recovery plan in place, especially for scenarios where data synchronization could lead to data corruption or loss.

Incorporating these considerations into your development and maintenance plans can help ensure that your integration of ElectricSQL into your note-taking app is robust, secure, and provides an excellent user experience.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment