Using ElectricSQL to build a local-first application

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.” 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:

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:

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. For a deeper dive into how ElectricSQL works, you can also check out this PodRocket episode with James Arthur, ElectricSQL’s co-founder and CEO:

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.

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.

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:

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:

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:

Next, to start the React application, run one of the following commands:

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:

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:

# 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:

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:

// 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 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:

// 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:

// 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:

// 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:

// 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. 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:

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:

// 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:

// 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:

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.