Good Bye CRUD APIs, Hello Sync: Realtime PostgreSQL with ElectricSQL

The Tangle of APIs: Are We Over-Complicating Interactions?

Every action on a web application triggers a cascade of network calls. Take the simple act of engaging with a post on a social media platform like Instagram. Opening a post fires off a GET API call, hitting 'like' triggers a POST API call, and commenting sparks another POST API call. Each interaction, each click, and each submission reels out a string of API calls from the frontend to the backend, fetching or updating data in the database.

This traditional setup, while functional, piles up layers of interaction between the user interface and the backend. Every added layer is a potential point of failure, a delay, or a bottleneck. For Developers and Engineers, managing this entanglement of APIs can be a daily grind. Each API call is crafted, tested, and maintained. The overhead is real, both in time and resources.

Moreover, the users on the other end, often find themselves waiting, even if it’s just for a split second, for these network calls to complete. In an era where speed and real-time interactions define user experience, these micro-delays can add up, affecting user engagement and satisfaction.

Now, imagine a setup that trims down these numerous API calls, a setup that allows direct interaction with the database, cutting through the delay and the clutter. This is where ElectricSQL (ESQL) steps into the picture…

ElectricSQL: Cutting Through the API Clutter

ESQL proposes a cleaner, streamlined approach. It operates alongside your Postgres DB, reducing the need for these multiple API calls. With ESQL, actions like commenting or upvoting are handled in dedicated tables within the browser’s local storage(SQLite), which then syncs with Postgres. This setup eliminates the need for a backend API call to store data to the Postgres, thereby reducing the layers of communication and the associated delay. The front end and the database can now interact more directly and efficiently. This simplifies the workflow for developers and speeds up user interactions on the front end. Now let's see how this is achieved and what goes on internally.

Interaction Between Browser and Postgres DB using Electric

The diagram demonstrates the communication pathway between the browser and the Postgres database through the Electric service. Essentially, Electric Sync Service, an Elixir application, orchestrates active-active data replication between the user's local DB and Postgres.

The browser interacts with the local DB through the electric-sql library. The local DB is connected to the Electric service, there is a satellite present in the electric-sql which is used to communicate with the electric service, and used for data replication between the local DB and Postgres.

Whenever data alterations occur in either the local DB or Postgres, the Electric service acts as a conflict resolver, ensuring seamless data replication. This configuration eliminates the need for API calls to store new data entries like comments or updates, thereby streamlining the data interaction process.

Backend: A Necessary Entity?

When we talk about cutting down on API calls, it's not about bidding farewell to the backend. We still need it for the heavy lifting, like handling internal logic or managing payment gateway integrations. It's the backbone that supports complex operations within our application.

However, what about the simple tasks? Consider the usual way we handle data updates: a user clicks 'like' on a post, which triggers a POST API call to the backend, which then updates the database. That’s three steps for a simple action. Now, imagine a scenario where the user’s action directly updates the database, cutting out the middle step. That’s ElectricSQL (ESQL) in action.

With ESQL, we're not doing away with the backend; we're simplifying its workload. The routine CRUD (Create, Read, Update, Delete) operations that usually require a bunch of API calls can be handled more directly. So, while the backend continues to manage the heavy tasks, ESQL helps in reducing the routine chatter between the front end and the database. It’s about making interactions smoother, quicker, and less tangled in a web of APIs. Who Stands to Gain? Suppose you're an engineer or developer leading a project. Every time a user wants to add a new comment on a post in your app, traditionally, it triggers a backend API call to save this data. Now, multiply this by the number of users and the various interactions they have with the app. That's a lot of API calls, right?

With ESQL setup, that new comment is directly saved to the database, skipping the usual round trip to the backend. It’s like having a shortcut that not only saves time but also reduces the workload on the backend.

Whether you're at the outset of a project or deep into the development process, integrating ESQL could offer some benefits. It aims to reduce the need for creating and managing numerous API calls for simple CRUD operations. For those who already have a setup with Postgres and a backend, it presents an opportunity to declutter the system from excessive CRUD APIs, potentially leading to a more straightforward and efficient setup.

Potential Applications

Here are five potential ways ElectricSQL could ease and enhance various applications. By reducing the amount of API calls and ensuring real-time data updates, it could be beneficial for both users and developers. Whether it's a social media platform, an online shopping site, or a startup creating a new app, integrating ElectricSQL might prove useful.

Real-Time Updates:

In an app resembling Twitter, ElectricSQL has the potential to display new tweets as they come in, without requiring a manual refresh.

Offline Work:

A note-taking app like Evernote, could allow for offline edits and note creation, syncing everything once back online.

Less Stress on the Backend:

On a platform like Amazon, handling basic inventory updates directly could potentially free up backend resources for other critical tasks.

Better User Experience:

In a voting app like Strawpoll, real-time vote count updates could potentially enhance user engagement.

Easier Development:

A startup, for instance, a food delivery platform like Zomato, might find ElectricSQL useful in speeding up the development process by minimizing the number of necessary CRUD APIs for menu updates, order placements, etc. This simplification could potentially lead to quicker feature rollouts and a more responsive user interface, enhancing the overall user experience.

Technical Deep Dive: Embracing ESQL

Understanding:

Transitioning to ElectricSQL entails a deep understanding of its architecture and how it facilitates real-time synchronization between your front end, the local database, and your central Postgres DB. Here, we delve into the core components and the flow of data within the ElectricSQL system.

Core Components:

ElectricSQL comprises three primary components:

1. Postgres Database (Postgres): The central database where your data resides.
2. Electric Sync Service (Electric): An intermediary service that orchestrates data synchronization between the local database and Postgres.
3. Client-side Library (Client): Loaded in the local-first app, this library facilitates interaction with the local database.

Getting the Electric Service Up and Running with Existing Postgres:

In the beginning, I aimed to integrate ElectricSQL with my existing Postgres setup to achieve real-time data synchronization between the front end of my application and the Postgres. I started with a clear goal but wasn’t sure about all the steps.

The Initial Steps:

With the help of documentation, I began by trying to connect the Electric service to my Postgres. The steps seemed simple at first.

 docker run \
  --name electric \
  --init \
  -e DATABASE_URL=postgresql://<user_name>:password@<host>:<port>/<db_name> \
  -e LOGICAL_PUBLISHER_HOST=electric \
  -e AUTH_MODE=insecure \
  -p 5133:5133 \
  -v electric_data:/app/data \
  --link postgres:postgres \
  electricsql/electric:latest

The Problem:

Soon, I faced a problem. The Electric service wasn't syncing with the Postgres. After some research, and discussion with the Electric team, I missed a crucial step - setting the Postgres to logical configuration.

Fixing the Problem:

I fixed the configuration of Postgres to set the wallevel to logical, which was necessary for the _Electric service to interact with the Postgres.

lovestaco@i3nux-whiz:~/repos/meta$ docker exec -i -t postgres sh

# psql -U postgres \

    -c 'ALTER SYSTEM SET wal_level = logical'

> ALTER SYSTEM

Connecting Again:

With the Postgres now logically configured, I restarted the Electric service, and this time it connected successfully to the Postgres.

The Electric service started to monitor the changes. If any data changes occurred in either the local DB or Postgres, Electric helped in resolving conflicts and ensuring data consistency and integrity.

Electric continuously monitors the changes, if they occur, they are streamed to the client, ensuring real-time updates. As data alterations occur in either the local DB or Postgres, Electric acts as a conflict resolver, applying necessary merge logic to ensure data consistency and integrity.

Electric service was ready to communicate with the electric-sql frontend library. The communication happens over the Satellite Protocol from the Satellite present in the electric-sql and the Electric Service.

Initial Data Sync:

When a browser establishes a subscription, Electric executes a query against Postgres to identify all the relevant rows, which are then streamed to the front end. This ensures an efficient initial sync of data. When I connected with the frontend example the expected replication logic was working.

Reactivity System:

The frontend library in ElectricSQL has a reactivity system integrated with frontend framework adaptations. This system keeps the data on the screen live and reactive to changes in the database, enhancing real-time user interaction.

Setting Up the Linear Example with ElectricSQL without existing Postgres:

Transitioning to a practical setup with ElectricSQL involved a series of steps, each with its own set of challenges. Here's how I navigated through them:

Clone the Frontend Repository:

git clone https://github.com/electric-sql/electric
cd electric/examples/linearlite
npm install

Prepare the Services:

1. PostgreSQL Database: Ensuring it's configured with wal_level = logical to enable table electrification was a crucial step.
2. Electric Service: Setting up the service to bridge the front end and Postgres for real-time data sync was the next hurdle. The necessary configurations were already outlined in the docker-compose file within the repository.

 docker compose --env-file backend/compose/.envrc -f backend/compose/docker-compose.yaml up --detach

Upon completion, verifying both containers were running was a moment of accomplishment:

docker ps

The image below reflects the running Postgres and electric service, a sight of progress.

Database Migrations:

This is where I stumbled a bit. Knowing where and when to apply the generate and migrate commands was a bit of a puzzle. If there are no tables in Postgres then you can run the migrate command which will create the Tables in Postgres. The migrate command will read the create_table.sql file and apply.

npm run db:migrate

There should be at least one “electrified” table to have the functionality of electric. You can simply electrify a table by running the DDL command in Postgres or place it in the SQL file and rerun the migrate command.

CALL electric.electrify('issues');
CALL electric.electrify('comments');
CALL electric.electrify('projects');

Generate Type-Safe Client:

Utilize the database schema to generate a type-safe client for the frontend to perform DB operations:

npx electric-sql generate

This will generate an electric client in the root of project ./src/generated/client This can be further used to make queries in the application.

Kickstart the Application

Finally, seeing the application come to life with

npm run start

The Linear Example setup was a hands-on experience, shedding light on the practical aspects of setting up ElectricSQL for real-time data synchronization in a new project. It was a blend of small challenges, learning, and eventually, accomplishment.

Real-Time Display of Database Updates:

After setting up ElectricSQL and the data is synced into your app, you can read it from the local database. You have the flexibility of using the Prisma client API which is used to make queries for the data, or if you prefer, you can use raw SQL.

Using the Client:

The client API is quite handy. It supports both static and live queries which are table-scoped. You can select specific columns, sort, filter, and work with relations.

Static queries in ElectricSQL are utilized to read data from the local database using functions like findUnique, findFirst, and findMany. These queries are executed once and return the data directly. For example to get a project by unique ID:

const result = await db.projects.findUnique({
  where: {
    id: "abcd",
  },
});

Live queries are a bit special. Unlike static queries, they return a function instead of direct results. You can call this function whenever you want to run or re-run the query, keeping a channel open to fetch the latest data whenever there's a change. You can use a liveMany query along with the useLiveQuery hook to bind live results to a state variable.

const MyComponent = () => {
  const { db } = useElectric();
  const { results } = useLiveQuery(
    db.projects.liveMany({
      where: {
        status: "active",
      },
    })
  );

  // Your component code here...
};

In this setup, whenever there's an update in your Postgres database, ElectricSQL ensures that the latest data is fetched and your UI is updated in real-time without any additional effort on your part.

Conclusion:

Transitioning to ElectricSQL helps reduce the extra work developers often encounter when managing communication between the existing Postgres DB and the front end due to unnecessary API calls.

ElectricSQL presents a cleaner, more streamlined approach by operating alongside your Postgres to reduce these multiple API calls. When you comment or upvote in tables on a local database, the local database syncs with Postgres, so you don't need to make a backend API call to save this data. This setup simplifies the workflow for developers and speeds up user interactions on the front end.

We also looked at how the local DB and the central Postgres DB can sync in real time using ElectricSQL. The setup process demonstrated how easily ElectricSQL can work with current systems. It reduces the work needed to switch and provides real-time data syncing and less API load.

We also looked at live queries and how they can be used to keep a channel open. This allows us to fetch the latest data whenever there is a change. This is especially beneficial when there's an update in your Postgres database, as ElectricSQL ensures that the latest data is fetched and your UI is updated in real time without any additional effort on your part.

In summary, ElectricSQL is a tool that makes the UI faster and the development workflow easier. It's a step towards reducing the clutter of CRUD APIs, making the system more straightforward and efficient. Whether you are just starting out or are deep into the development process, integrating ElectricSQL can be a game-changer. It significantly cuts down the need for creating and managing numerous API calls for simple CRUD operations, making interactions smoother, quicker, and less tangled in a web of APIs.