Using ElectricSQL to build a local-first application
Tags: postgres, webdev
Published: 2023-12-07 15:18:53

**Written by [Rahul Padalkar](https://blog.logrocket.com/author/rahulpadalkar/)✏️**

ElectricSQL is a sync layer between your apps and PostgreSQL database. It allows developers to build local-first and reactive applications. This platform seamlessly syncs the data between the local device and the remote database without leading to any merge conflicts. 

In this tutorial, we’ll explore how ElectricSQL works and use it to build an offline-first React app. To keep the focus on ElectricSQL and its features, we’ll build on the UI from my previous article, "[Build a Kanban board with dnd kit and React](https://blog.logrocket.com/build-kanban-board-dnd-kit-react/)." You can find the full code for this ElectricSQL project on GitHub.

## How does ElectricSQL work?

ElectricSQL sits between the Postgres database and the local application. This is how the architecture looks at a high level: ![High Level Depiction Of Electricsql Architecture Showing Electric Service Sitting Between Postgres Database And Local App And Interacting With Each Through Logical Replication And Satellite Protocol, Respectively](https://blog.logrocket.com/wp-content/uploads/2023/11/ElectricSQL-architecture-high-level.png) The Electric sync service is connected with the Postgres database over logical replication. It uses logical replication and the satellite protocol to sync data between the two data stores. 

When the Postgres database updates, it streams that update to the Electric service, which then passes it down to the local app using the satellite protocol over WebSockets. Similarly, when the local app’s data changes, it sends a message over WebSockets to the Electric service, which then updates the Postgres database. 

You can actually look at the messages sent by the local application to the Electric service. To see them, follow these steps:

1.  Open Chrome DevTools and open the **Network** tab
2.  In the search bar, type "ws" and then click on the request
3.  Head over to the **Messages** tab

You will then be able to see the messages: ![List Of Messages Sent To Local App Via Electric Service Shown In Chrome Devtools](https://blog.logrocket.com/wp-content/uploads/2023/11/Messages-sent-local-app-Electric-service.png) Cool, right? Here’s the catch: though ElectricSQL is really awesome, it is still too early to use it in production. As of this writing, it’s still in public alpha and has some serious limitations, like:

*   No support for `SERIAL`, `SEQUENCE`, and `UNIQUE`
*   Primary keys cannot be changed once created
*   Foreign keys can only be defined on primary keys

At this point in time, it may be stable enough to build POCs or MVPs. However, it’s definitely still worth learning how to use ElectricSQL at this time to get familiar with the tool and think of ideas to use it in production when it’s ready. You can stay up to date with its development [via the official roadmap](https://electric-sql.com/docs/reference/roadmap). 

Now that we know how ElectricSQL works, let’s try to build a Kanban board using ElectricSQL. You can use ElectricSQL with any Postgres-compatible framework or library — [we’ll be using React](https://blog.logrocket.com/getting-started-postgres-react-app/).

## Getting started with ElectricSQL

There are two ways to get started with ElectricSQL. The easiest is to use the starter kit developed by the folks at ElectricSQL. The other way is to set up Postgres, the Electric sync service, and the TypeScript client, all manually. 

For this tutorial, we will use the starter kit. A guide to getting started with ElectricSQL manually is [available in the docs](https://electric-sql.com/docs/quickstart?setup=manual). 

To use the starter, you’ll need any Node.js version above 16.11 and Docker. Both are available for free and are easy to set up and install. 

Once these prerequisites are installed, let’s set up the starter kit. Open your terminal window and execute the following command:

```bash
npx create-electric-app@latest kanban-electric-board
```

This command will download the starter kit code and install all the required dependencies. It’s extremely easy! 

Before we start building our app, let's quickly look at the different parts of the starter kit code:

*   The `backend` folder contains all the files necessary to start an Electric service and a Postgres database. It has a few JavaScript scripts that trigger the `docker compose` command. There is also a `.envrc` file that helps us set environment variables when running the Docker containers. We will visit this file later and add a few things
*   The `db` folder contains all the database migrations. We can run `npm run db:migrate` or `yarn db:migrate` to run all the migrations. Migrations are basically commands to perform database operations. Database migrations are generally idempotent — in other words, you can run them multiple times without affecting the state of the database
*   The `src` folder contains our React application. We will spend most of our time in this folder

With the folder structure now clear, let’s run everything together and see if it works correctly. To start the backend, run one of the following commands:

```bash
npm run backend:start
# or
yarn backend:start
```

This command will spin up two Docker containers and run the respective images in them. You can see the status of the containers via Docker Desktop: ![Status Of Docker Containers Shown In Docker Desktop](https://blog.logrocket.com/wp-content/uploads/2023/11/Status-Docker-containers-Docker-Desktop-e1701380964825.png) Next, to start the React application, run one of the following commands:

```bash
npm run start
# or
yarn start
```

The last thing we need to do before we start developing is to generate a type-safe database client. We will use this generated client library to access data models inside our React application. The generated client library gives autocomplete suggestions when accessing the data model, which is pretty cool! 

To generate the client, run one of the following commands:

```bash
npm run client:generate
# or
yarn client:generate
```

And now, we are ready to build the greatest local-first offline React application ever!

## Creating the database schema

Let’s create database tables for saving data in our remote Postgres database. To do that, we’ll need to write a database migration. Create a file under `db/migrations` and add the code below:

```sql
# db/migrations/02-create_todos_table.sql
CREATE TABLE IF NOT EXISTS todos (
    id UUID PRIMARY KEY, 
    todo_description TEXT,
    status TEXT
);
ALTER TABLE todos ENABLE ELECTRIC;
```

Then run this command:

```bash
npm run db:migrate
```

This command should create the `todos` table and expose it to the Electric sync service. You can control which tables are visible to ElectricSQL by enabling visibility for relevant tables.

## Overview of UI components

Remember, to build the UI, we‘ll be using the code in one of my previous tutorials on building a Kanban board with dnd kit and React. 

If you haven’t read that tutorial yet, essentially, we have four main UI components to work with: `KanbanBoard`, `KanbanLane`, `KanbanCard`, and `App`. Let’s review the code for each one.

### The `KanbanBoard` component

`KanbanBoard` is the main component of our application. The code looks like this:

```typescript
// src/KanbanBoard.tsx
import { DndContext, rectIntersection } from "@dnd-kit/core";
import KanbanLane from "./KanbanLane";
import AddCard from "./AddCard";
import { Flex } from "@chakra-ui/react";
import { useState } from "react";
import { Cards } from "./types";
export default function KanbanBoard() {
  const [todoItems, setTodoItems] = useState<Array<Cards>>([]);
  const [doneItems, setDoneItems] = useState<Array<Cards>>([]);
  const [inProgressItems, setInProgressItems] = useState<Array<Cards>>([]);
  const [uItems, setuItems] = useState<Array<Cards>>([]);
  const addNewCard = (title: string) => {
    setuItems([...uItems, { title }]);
  };
  return (
    <DndContext
      collisionDetection={rectIntersection}
      onDragEnd={(e) => {
        const container = e.over?.id;
        const title = e.active.data.current?.title ?? "";
        const index = e.active.data.current?.index ?? 0;
        const parent = e.active.data.current?.parent ?? "ToDo";
        if (container === "ToDo") {
          setTodoItems([...todoItems, { title }]);
        } else if (container === "Done") {
          setDoneItems([...doneItems, { title }]);
        } else if (container === "Unassigned") {
          setuItems([...uItems, { title }]);
        } else {
          setInProgressItems([...inProgressItems, { title }]);
        }
        if (parent === "ToDo") {
          setTodoItems([
            ...todoItems.slice(0, index),
            ...todoItems.slice(index + 1),
          ]);
        } else if (parent === "Done") {
          setDoneItems([
            ...doneItems.slice(0, index),
            ...doneItems.slice(index + 1),
          ]);
        } else if (parent === "Unassigned") {
          setuItems([...uItems.slice(0, index), ...uItems.slice(index + 1)]);
        } else {
          setInProgressItems([
            ...inProgressItems.slice(0, index),
            ...inProgressItems.slice(index + 1),
          ]);
        }
      }}
    >
      <Flex flexDirection="column">
        <AddCard addCard={addNewCard} />
        <Flex flex="3">
          <KanbanLane title="ToDo" items={todoItems} />
          <KanbanLane title="In Progress" items={inProgressItems} />
          <KanbanLane title="Done" items={doneItems} />
          <KanbanLane title="Unassigned" items={uItems} />
        </Flex>
      </Flex>
    </DndContext>
  );
```

This component houses a `KanbanLane` component. We will have `KanbanCard` components inside this `KanbanLane` component. 

Our `KanbanBoard` also has a `DndContext` component as its root. You can read more about the [`DndContext` component in the other article](https://blog.logrocket.com/build-kanban-board-dnd-kit-react/#dndcontext) if you’d like. To keep things simple in this tutorial, the `DndContext` supports our drag-and-drop feature so we can move cards between lanes. 

As we progress, we will modify this `KanbanBoard` component.

### The `KanbanLane` component

`KanbanLane` is a simple React component that lists a bunch of `KanbanCard` components in a lane, which will appear as a column in our UI. Here’s how the code looks:

```typescript
// src/KanbanLane.tsx
import { Flex, Text } from "@chakra-ui/react";
import { useDroppable } from "@dnd-kit/core";

interface KanbanLaneProps {
  title: string;
  items: Cards[];
}

export default function KanbanLane({ title, items }: KanbanLaneProps) {
  const { setNodeRef } = useDroppable({
    id: title,
  });
  return (
    <Flex flex="3" padding="5" flexDirection="column" minH="10rem">
      <Text fontWeight="bold">{title}</Text>
      <Flex
        ref={setNodeRef}
        backgroundColor="gray.200"
        borderRadius="8"
        flex="1"
        padding="2"
        flexDirection="column"
      >
        {items.map(({ title: cardTitle }, key) => (
          <KanbanCard title={cardTitle} key={key} index={key} parent={title} />
        ))}
      </Flex>
    </Flex>
  );
}
```

As mentioned earlier, this component is housed inside the `KanbanBoard` component. We can have multiple `KanbanLane` components within our `KanbanBoard`.

### The `KanbanCard` component

The `KanbanCard` is housed in the `KanbanLane` component. We can drag and drop a `KanbanCard` between different `KanbanLane` components. Here’s the code:

```typescript
// src/KanbanCard.tsx
import { Flex, Text } from "@chakra-ui/react";
import { useDraggable } from "@dnd-kit/core";
import { CSS } from "@dnd-kit/utilities";

const KanbanCard = ({
  title,
  index,
  parent,
}: {
  title: string;
  index: number;
  parent: string;
}) => {
  const { attributes, listeners, setNodeRef, transform } = useDraggable({
    id: title,
    data: {
      title,
      index,
      parent,
    },
  });
  const style = {
    transform: CSS.Translate.toString(transform),
  };
  return (
    <Flex
      padding="3"
      backgroundColor="white"
      margin="2"
      borderRadius="8"
      border="2px solid gray.500"
      boxShadow="0px 0px 5px 2px #2121213b"
      transform={style.transform}
      {...listeners}
      {...attributes}
      ref={setNodeRef}
    >
      <Text>{title}</Text>
    </Flex>
  );
};
```

We can have multiple `KanbanCard` components within our `KanbanBoard` as well, but any one particular `KanbanCard` component can only be in one `KanbanLane` at a time.

### The `App` component

The `App` component is a familiar one to us developers — it’s the entry point of our app. We’ll use this top-level component to build and structure the rest of our application. Here’s the code:

```typescript
// src/App.tsx
import { ChakraProvider, theme, Text } from "@chakra-ui/react";
import KanbanBoard from "./KanbanBoard";
import { ElectricWrapper } from "./ElectricProvider1";
export const App = () => {
  return (
    <ChakraProvider theme={theme}>
        <Text fontSize="2xl" padding="5" fontWeight="bold" fontStyle="italic">
          Simple Kanban
        </Text>
        <KanbanBoard />
    </ChakraProvider>
  );
};
```

Now, with the UI components out of the way, let’s “electrify” our Kanban board with ElectricSQL.

## Adding ElectricSQL to our React app

We need to use the type-safe Electric client library that we generated previously to read from and write to the local database. Once per app, preferably when the app starts, we need to initialize the client by passing it a few things. 

So, let’s write an Electric wrapper that will initiate the client and make ElectricSQL available throughout the application. Electric provides a React integration out of the box that we will use to build our wrapper:

```typescript
// src/ElectricWrapper.tsx
import { useEffect, useState } from "react";
import { makeElectricContext } from "electric-sql/react";
import { ElectricDatabase, electrify } from "electric-sql/wa-sqlite";
import { Electric, schema } from "./generated/client";
export const { ElectricProvider, useElectric } =
  makeElectricContext<Electric>();
export const ElectricWrapper = ({ children }: { children: any }) => {
  const [electric, setElectric] = useState<Electric>();
  useEffect(() => {
    let isMounted = true;
    const init = async () => {
      const config = {
        auth: {
          token:
            "jwt-token",
        },
      };
      const conn = await ElectricDatabase.init("todos.db", "");
      const electric = await electrify(conn, schema, config);
      if (!isMounted) {
        return;
      }
      setElectric(electric);
    };
    init();
    return () => {
      isMounted = false;
    };
  }, []);
  if (electric === undefined) {
    return null;
  }
  return <ElectricProvider db={electric}>{children}</ElectricProvider>;
};
```

Once mounted, this `ElectricWrapper` component initializes an Electric client. Let’s summarize what we did in the code above. 

To create a client, we first create a connection. We use the `init` method from `ElectricDatabase` and pass it a database name. We then create an `electric` instance by passing to it the connection, the schema, and the config. 

We are using ElectricSQL in insecure mode, which just means we’re using static claims in [our JWT authentication](https://blog.logrocket.com/jwt-authentication-best-practices/). Any JWT that hasn’t expired with a `user_id` claim should work fine. Here, we use the `ElectricProvider` exported by the `electric-sql` package and wrap the `children` inside this provider. 

Here’s a tip: to use ElectricSQL in insecure mode, open the `.envrc` under `backend/compose` and add `export AUTH_MODE=insecure`. You’ll need to switch to secure mode when using the app in production, but for this article, I wanted to focus more on demonstrating the capabilities of ElectricSQL. 

Now, let’s wrap our root component with this `ElectricWrapper` that we just created:

```typescript
import { ChakraProvider, theme, Text } from "@chakra-ui/react";
import KanbanBoard from "./KanbanBoard";
import { ElectricWrapper } from "./ElectricWrapper";
export const App = () => {
  return (
    <ChakraProvider theme={theme}>
      <ElectricWrapper>
        <Text fontSize="2xl" padding="5" fontWeight="bold" fontStyle="italic">
          Simple Kanban
        </Text>
        <KanbanBoard />
      </ElectricWrapper>
    </ChakraProvider>
  );
};
```

## Writing to a data store

Now, let’s add code to write data to the local database. As we discussed earlier, the local database will then automatically sync with the remote database thanks to ElectricSQL. 

Let’s modify the `addNewCard` method in our `KanbanBoard` component:

```typescript
// src/KanbanBoard.tsx
.
.
export default function KanbanBoard() {
.

  const { db } = useElectric()!;
  const addNewCard = async (title: string) => {
    const newCard = {
      id: genUUID(),
      title,
      status: CardStatus.UNASSIGNED,
    };
    await db.todos.create({
      data: {
        id: newCard.id,
        todo_description: newCard.title,
        status: CardStatus.UNASSIGNED,
      },
    });
    setuItems([...uItems, newCard]);
  };
  return (....);
}
```

Since we have wrapped our application in the `ElectricProvider`, we can now access the Electric client by using the `useElectric` Hook. 

In the `addNewCard` method, we take the title of the new card and create a new card with an `id`, `title`, and `status`. Then, we use the `create` method and pass in the new `card` object. 

And that’s it! 

You can now check in the remote Postgres instance for the newly added card. Remember, the Electric instance just writes the data to the local database inside the browser, so it should work even if you’re offline. Once you’re online, Electric will update the remote Postgres database.

## Syncing data between data stores

The cool part of ElectricSQL is its seamless data syncing between the local database and the remote Postgres database. To sync data between these two data stores, ElectricSQL uses something called shapes, a core primitive in ElectricSQL. 

Shapes can be a table in the database or a query. Anytime data relevant to the shape changes, the data on the local device is updated to keep things in sync. A shape is basically a subscription made by the local device to the remote database. 

Now, let’s try to use shapes in our Kanban board. We will create a table-level shape. Whenever data in the `todos` table changes in the remote database, the local device will get updated automatically through this shape subscription. 

Add the following code to the `KanbanBoard` component:

```typescript
// src/KanbanBoard.tsx
export default function KanbanBoard() {
.
.
  const syncCards = async () => {
    const cardShape = await db.todos.sync();
    await cardShape.synced;
    setReady(true);
  };
.
.
  useEffect(() => {syncCards()} [])
.
.
.
}
```

Once the component is mounted, the code we just wrote will update the local copy with the remote database records and monitor for any upstream changes. 

Now that we have ensured that the local copy is always up to date, let’s see how to show the changes in real time in our React app. Electric exports a `useLiveQuery` Hook that we can use to get any changes made remotely in real time. This Hook acts like a subscription to remote database changes. 

This is how the `KanbanBoard` component code looks with everything in place:

```typescript
import { DndContext, rectIntersection } from "@dnd-kit/core";
import KanbanLane from "./KanbanLane";
import AddCard from "./AddCard";
import { Button, Flex } from "@chakra-ui/react";
import { useEffect, useState } from "react";
import { Cards } from "./types";
import { v4 as genUUID } from "uuid";
import { useElectric } from "./ElectricProvider1";
import { useLiveQuery } from "electric-sql/react";
export enum CardStatus {
  UNASSIGNED = "Unassigned",
  TODO = "ToDo",
  DONE = "Done",
  INPROGRESS = "InProgress",
}
export default function KanbanBoard() {
  const [todoItems, setTodoItems] = useState<Array<Cards>>([]);
  const [doneItems, setDoneItems] = useState<Array<Cards>>([]);
  const [inProgressItems, setInProgressItems] = useState<Array<Cards>>([]);
  const [uItems, setuItems] = useState<Array<Cards>>([]);
  const [ready, setReady] = useState<boolean>(false);
  const { db } = useElectric()!;
  const { results } = useLiveQuery(db.todos.liveMany());
  const syncCards = async () => {
    const cardShape = await db.todos.sync();
    await cardShape.synced;
    setReady(true);
  };
  const addNewCard = async (title: string) => {
    const newCard = {
      id: genUUID(),
      title,
      status: CardStatus.UNASSIGNED,
    };
    await db.todos.create({
      data: {
        id: newCard.id,
        todo_description: newCard.title,
        status: CardStatus.UNASSIGNED,
      },
    });
    setuItems([...uItems, newCard]);
  };
  useEffect(() => {
    syncCards();
  }, []);
  const updateStatus = async (id: string, status: string) => {
    await db.todos.update({
      data: {
        status,
      },
      where: {
        id,
      },
    });
  };
  const extractCards = (res: any, s: CardStatus) => {
    return (
      res
        .filter((card: any) => card.status === s)
        //@ts-ignore
        .map(({ todo_description, status, id }) => ({
          title: todo_description,
          status,
          id,
        }))
    );
  };
  const todoCards =
    results && results?.length > 0
      ? extractCards(results, CardStatus.TODO)
      : [];
  const inProgressCards =
    results && results?.length > 0
      ? extractCards(results, CardStatus.INPROGRESS)
      : [];
  const doneCards =
    results && results?.length > 0
      ? extractCards(results, CardStatus.DONE)
      : [];
  const unassignedCards =
    results && results?.length > 0
      ? extractCards(results, CardStatus.UNASSIGNED)
      : [];
  return (
    ready && (
      <DndContext
        collisionDetection={rectIntersection}
        onDragEnd={async (e) => {
          const container = e.over?.id;
          const card = e.active.data.current?.cardData ?? "";
          const index = e.active.data.current?.index ?? 0;
          const parent = e.active.data.current?.parent ?? "ToDo";
          if (container === CardStatus.TODO) {
            await updateStatus(card.id, CardStatus.TODO);
          } else if (container === CardStatus.DONE) {
            await updateStatus(card.id, CardStatus.DONE);
          } else if (container === CardStatus.UNASSIGNED) {
            await updateStatus(card.id, CardStatus.UNASSIGNED);
          } else {
            await updateStatus(card.id, CardStatus.INPROGRESS);
          }
        }}
      >
        <Flex flexDirection="column">
          <AddCard addCard={addNewCard} />
          <Flex flex="3">
            <KanbanLane title="ToDo" items={todoCards} />
            <KanbanLane title="In Progress" items={inProgressCards} />
            <KanbanLane title="Done" items={doneCards} />
            <KanbanLane title="Unassigned" items={unassignedCards} />
          </Flex>
        </Flex>
      </DndContext>
    )
  );
}
```

As you can see, we have added a few things and removed some others. Now, based on the code above, our `KanbanBoard` works like this:

*   Once the component is mounted, a sync call is made to the remote database
*   The sync call updates the local copy with all the fresh changes and returns the result to the live query
*   We use the data from the live query to perform a `filter` method and pass that data to be rendered in the respective `KanbanLane` components

That’s pretty much it! One interesting feature that I still want to talk about is offline support. Let’s get into it in the next section.

## Running the app in offline mode

Since our React app writes to the local database, we can use some parts of our app even if you are offline and therefore not able to reach the server and, in turn, the remote database. 

To see this in action, follow these steps:

*   Open the app in two different browsers — Chrome and Firefox, for example
*   Open Chrome DevTools and open the **Network** tab
*   Click on the dropdown next to **Disable cache** and select **Offline**
*   Add a new card to the board in the app opened in Firefox and then in Chrome
*   Set the dropdown value to **No throttling**

And voila! You will be able to see both the cards in both browsers — no conflicts! ElectricSQL uses CRDTs under the hood to reconcile the changes and merge them into a single result without any merge conflicts, just like magic. 🪄

## Conclusion

ElectricSQL is a cool piece of software with immense potential. It gives developers the ability to build a true local-first application. With shapes and the use of CRDTs under the hood, ElectricSQL ensures that the user always sees the latest data without any merge conflicts. 

Remember, ElectricSQL is not yet ready for apps in production. However, it’s worth learning about it and staying up-to-date with it so you’re ready to electrify your apps as soon as it hits a stable version. 

That’s it! Thanks for reading.

---

## Get set up with LogRocket's modern error tracking in minutes:

1. Visit https://logrocket.com/signup/ to get an app ID.
2. Install LogRocket via NPM or script tag. `LogRocket.init()` must be called client-side, not server-side.

NPM:
```bash
$ npm i --save logrocket 

// Code:

import LogRocket from 'logrocket'; 
LogRocket.init('app/id');
```
Script Tag:
```javascript
Add to your HTML:

<script src="https://cdn.lr-ingest.com/LogRocket.min.js"></script>
<script>window.LogRocket && window.LogRocket.init('app/id');</script>
```

3.(Optional) Install plugins for deeper integrations with your stack:
*   Redux middleware
*   ngrx middleware
*   Vuex plugin

[Get started now](https://lp.logrocket.com/blg/signup)