Conflict-free offline

As we've seen, first you write to a local database, then data syncs in the background. This introduces the potential for different users to change the same data at the same time. These writes need to be handled consistently and without conflicts.

Merging offline writes

Below, again we have two local-first demo apps with multi-user sync. This time, we've added a connectivity toggle. So you can switch the network on and off for each of the users.

Live Editor

/* prettier-ignore */ const newItem = (demo) => { return { id: genUUID(), inserted_at: `${Date.now()}`, demo_id: demo.id, demo_name: demo.name, electric_user_id: demo.electric_user_id, } } const RealtimeWithConnectivity = ({ itemColor, slider, userId }) => { const { db } = useElectric() const { demo } = useDemoContext() const [sliderValue, setSliderValue] = useState(slider.value) const { results: liveItems } = useLiveQuery( db.items.liveMany({ where: { demo_name: demo.name, electric_user_id: demo.electric_user_id, }, orderBy: { inserted_at: 'asc', }, take: 24, }), ) const { results: liveSlider } = useLiveQuery( db.sliders.liveFirst({ where: { demo_name: demo.name, electric_user_id: demo.electric_user_id, }, select: { id: true, value: true, }, orderBy: { id: 'asc', }, }), ) useEffect(() => { if (liveSlider === undefined) { return } setSliderValue(liveSlider.value) }, [liveSlider]) const add = async () => { await db.items.create({ data: newItem(demo), }) } const clear = async () => { await db.items.deleteMany({ where: { demo_name: demo.name, electric_user_id: demo.electric_user_id, }, }) } const syncSlider = async (value) => { await db.sliders.updateMany({ where: { demo_name: demo.name, electric_user_id: demo.electric_user_id, }, data: { value: value, }, }) } if (liveItems === undefined) { return null } return ( <div className="mb-4"> <div className="flex flex-row items-center justify-between pb-3 mb-5" style={{ borderBottom: '1px solid var(--card-border)' }} > <label className={clsx('section-label text-small', itemColor)}> User: {userId} </label> <ConnectivityControl /> </div> <div className={clsx('my-8', itemColor)}> <SliderInput min={0} max={100} step={1} value={sliderValue} onChange={setSliderValue} onChangeComplete={syncSlider} /> </div> <ItemsWidget add={add} clear={clear} items={liveItems} itemColor={itemColor} disableWhenInProgress={false} /> </div> ) } // Setup the slider in a Wrapper rather than directly // in the Demo below so we can use the db and // demoContext setup by the App component. const Wrapper = ({ itemColor, userId }) => { const { db } = useElectric() const { demo } = useDemoContext() const [slider, setSlider] = useState() useEffect(() => { let isMounted = true const ensureSlider = async () => { const slider = await bootstrapSlider(db, demo) if (!isMounted) { return } setSlider(slider) } ensureSlider() return () => { isMounted = false } }, []) if (slider === undefined) { return null } return ( <RealtimeWithConnectivity slider={slider} userId={userId} itemColor={itemColor} /> ) } const Demo = () => ( <div className="grid grid-cols-1 sm:grid-cols-2 gap-6" data-testid="offline-connectivity-demo"> <div className="px-3 md:px-4" data-testid="user1"> <App dbName="user1" demoName="offline-connectivity" bootstrapItems={2}> <Wrapper userId={1} itemColor="electric-green" /> </App> </div> <div className="px-3 md:px-4" data-testid="user2"> <App dbName="user2" demoName="offline-connectivity" bootstrapItems={2}> <Wrapper userId={2} itemColor="script-purple" /> </App> </div> </div> ) render(<Demo />)

/* prettier-ignore */




const newItem = (demo) => {
  return {
    id: genUUID(),
    inserted_at: `${Date.now()}`,
    demo_id: demo.id,
    demo_name: demo.name,
    electric_user_id: demo.electric_user_id,
  }
}

const RealtimeWithConnectivity = ({ itemColor, slider, userId }) => {
  const { db } = useElectric()
  const { demo } = useDemoContext()
  const [sliderValue, setSliderValue] = useState(slider.value)

  const { results: liveItems } = useLiveQuery(
    db.items.liveMany({
      where: {
        demo_name: demo.name,
        electric_user_id: demo.electric_user_id,
      },
      orderBy: {
        inserted_at: 'asc',
      },
      take: 24,
    }),
  )

  const { results: liveSlider } = useLiveQuery(
    db.sliders.liveFirst({
      where: {
        demo_name: demo.name,
        electric_user_id: demo.electric_user_id,
      },
      select: {
        id: true,
        value: true,
      },
      orderBy: {
        id: 'asc',
      },
    }),
  )

  useEffect(() => {
    if (liveSlider === undefined) {
      return
    }

    setSliderValue(liveSlider.value)
  }, [liveSlider])

  const add = async () => {
    await db.items.create({
      data: newItem(demo),
    })
  }

  const clear = async () => {
    await db.items.deleteMany({
      where: {
        demo_name: demo.name,
        electric_user_id: demo.electric_user_id,
      },
    })
  }

  const syncSlider = async (value) => {
    await db.sliders.updateMany({
      where: {
        demo_name: demo.name,
        electric_user_id: demo.electric_user_id,
      },
      data: {
        value: value,
      },
    })
  }

  if (liveItems === undefined) {
    return null
  }

  return (
    <div className="mb-4">
      <div
        className="flex flex-row items-center justify-between pb-3 mb-5"
        style={{ borderBottom: '1px solid var(--card-border)' }}
      >
        <label className={clsx('section-label text-small', itemColor)}>
          User: {userId}
        </label>
        <ConnectivityControl />
      </div>
      <div className={clsx('my-8', itemColor)}>
        <SliderInput
          min={0}
          max={100}
          step={1}
          value={sliderValue}
          onChange={setSliderValue}
          onChangeComplete={syncSlider}
        />
      </div>
      <ItemsWidget
        add={add}
        clear={clear}
        items={liveItems}
        itemColor={itemColor}
        disableWhenInProgress={false}
      />
    </div>
  )
}

// Setup the slider in a Wrapper rather than directly
// in the Demo below so we can use the db and
// demoContext setup by the App component.
const Wrapper = ({ itemColor, userId }) => {
  const { db } = useElectric()
  const { demo } = useDemoContext()
  const [slider, setSlider] = useState()

  useEffect(() => {
    let isMounted = true

    const ensureSlider = async () => {
      const slider = await bootstrapSlider(db, demo)

      if (!isMounted) {
        return
      }

      setSlider(slider)
    }

    ensureSlider()

    return () => {
      isMounted = false
    }
  }, [])

  if (slider === undefined) {
    return null
  }

  return (
    <RealtimeWithConnectivity
      slider={slider}
      userId={userId}
      itemColor={itemColor}
    />
  )
}

const Demo = () => (
  <div className="grid grid-cols-1 sm:grid-cols-2 gap-6" data-testid="offline-connectivity-demo">
    <div className="px-3 md:px-4" data-testid="user1">
      <App dbName="user1" demoName="offline-connectivity" bootstrapItems={2}>
        <Wrapper userId={1} itemColor="electric-green" />
      </App>
    </div>
    <div className="px-3 md:px-4" data-testid="user2">
      <App dbName="user2" demoName="offline-connectivity" bootstrapItems={2}>
        <Wrapper userId={2} itemColor="script-purple" />
      </App>
    </div>
  </div>
)

render(<Demo />)

Play around to verify the sync when connected. Then switch off the network in one or more of the apps. Play around with both to generate and stack up some changes. Then re-enable the network and see how they sync.

The first thing to note is that the changes sync and the apps are resilient to going in and out of connectivity. The second thing to note is how the apps always resolve to the same state.

Preserving data integrity

We've seen above how offline writes sync resiliently without conflicts. But what about cases where concurrent writes could result in inconsistent data?

For example, the app below allows you to enroll players in tournaments. Drag the player icons into and out-of the tournaments to enroll and unenroll them. Use the add button to add a tournment and the little x button to delete them.

Now, what happens if one user enrolls a player in a tournament whilst another user concurrently deletes the same tournament?

Live Editor

/* prettier-ignore */ /* prettier-ignore */ const newTournament = (name, demo) => { const ts = `${Date.now()}` return { id: genUUID(), name: name, demo_id: demo.id, demo_name: demo.name, inserted_at: ts, updated_at: ts, electric_user_id: demo.electric_user_id, } } const Integrity = ({ scopedTournmentCounter, userId, userColor }) => { const { db } = useElectric() const { demo } = useDemoContext() const [scopedCounter, setScopedCounter] = useState(scopedTournmentCounter) const { results: tournaments } = useLiveQuery( db.tournaments.liveMany({ where: { demo_name: demo.name, electric_user_id: demo.electric_user_id, }, include: { players: true, }, orderBy: { inserted_at: 'asc', }, take: 24, }), ) const { results: unEnrolledPlayers } = useLiveQuery( db.players.liveMany({ where: { demo_name: demo.name, electric_user_id: demo.electric_user_id, tournament_id: null, }, orderBy: { updated_at: 'asc', }, take: 24, }), ) const addTournament = async () => { const counter = scopedCounter + 1 const name = `${userId}:${counter}` setScopedCounter(counter) await db.tournaments.create({ data: newTournament(name, demo), }) } const deleteTournament = async (tournament) => { await db.tournaments.delete({ where: { id: tournament.id, }, }) } const enroll = async (player, tournament) => { const ts = `${Date.now()}` await db.players.update({ data: { tournament_id: tournament.id, updated_at: ts, }, where: { id: player.id, }, }) } const unenroll = async (player) => { const ts = `${Date.now()}` const tournament_id = player.tournament_id if (tournament_id === null) { return } await db.players.update({ data: { tournament_id: null, updated_at: ts, }, where: { id: player.id, }, }) } if (tournaments === undefined || unEnrolledPlayers === undefined) { return null } return ( <div className="mb-4"> <div className="flex flex-row items-center justify-between pb-3 mb-5" style={{ borderBottom: '1px solid var(--card-border)' }} > <label className={clsx('section-label text-small', userColor)}> User: {userId} </label> <ConnectivityControl /> </div> <div className="flex flex-row my-4"> <div className="basis-1/2"> <PlayersWidget players={unEnrolledPlayers} dndDiscriminator={userId} onDrop={(player) => unenroll(player)} /> </div> <div className="basis-1/2"> <div className="relative block"> {tournaments.map((tournament) => ( <TournamentWidget key={tournament.id} tournament={tournament} dndDiscriminator={userId} onDrop={(player) => enroll(player, tournament)} deleteTournament={() => deleteTournament(tournament)} /> ))} <div> <button className="button button--secondary button--outline button--sm me-2" onMouseDown={addTournament} > Add </button> </div> </div> </div> </div> </div> ) } const Wrapper = ({ bootstrappedPlayerColors, userColor, userId }) => { const { db } = useElectric() const { demo } = useDemoContext() const [scopedCounter, setScopedCounter] = useState(undefined) const [ready, setReady] = useState(false) useEffect(() => { let isMounted = true const bootstrap = async () => { await bootstrapPlayers(db, demo, bootstrappedPlayerColors) const numTournments = await bootstrapTournament(db, demo, `${userId}:1`) if (!isMounted) { return } setScopedCounter(numTournments) setReady(true) } bootstrap() return () => { isMounted = false } }, []) if (!ready) { return null } const backend = window.matchMedia('(pointer: coarse)').matches ? TouchBackend : HTML5Backend return ( <DndProvider backend={backend}> <Integrity scopedTournmentCounter={scopedCounter} userColor={userColor} userId={userId} /> </DndProvider> ) } const Demo = () => ( <div className="grid grid-cols-1 sm:grid-cols-2 integrity-grid-cols gap-6" data-testid="offline-integrity-demo"> <div className="px-3 md:px-4" data-testid="user1"> <App dbName="user1" demoName="integrity"> <Wrapper userId={1} userColor="electric-green" bootstrappedPlayerColors={['green', 'yellow']} /> </App> </div> <div className="px-3 md:px-4" data-testid="user2"> <App dbName="user2" demoName="integrity"> <Wrapper userId={2} userColor="script-purple" bootstrappedPlayerColors={['red', 'purple']} /> </App> </div> </div> ) render(<Demo />)

/* prettier-ignore */


/* prettier-ignore */


const newTournament = (name, demo) => {
  const ts = `${Date.now()}`

  return {
    id: genUUID(),
    name: name,
    demo_id: demo.id,
    demo_name: demo.name,
    inserted_at: ts,
    updated_at: ts,
    electric_user_id: demo.electric_user_id,
  }
}

const Integrity = ({ scopedTournmentCounter, userId, userColor }) => {
  const { db } = useElectric()
  const { demo } = useDemoContext()
  const [scopedCounter, setScopedCounter] = useState(scopedTournmentCounter)

  const { results: tournaments } = useLiveQuery(
    db.tournaments.liveMany({
      where: {
        demo_name: demo.name,
        electric_user_id: demo.electric_user_id,
      },
      include: {
        players: true,
      },
      orderBy: {
        inserted_at: 'asc',
      },
      take: 24,
    }),
  )

  const { results: unEnrolledPlayers } = useLiveQuery(
    db.players.liveMany({
      where: {
        demo_name: demo.name,
        electric_user_id: demo.electric_user_id,
        tournament_id: null,
      },
      orderBy: {
        updated_at: 'asc',
      },
      take: 24,
    }),
  )

  const addTournament = async () => {
    const counter = scopedCounter + 1
    const name = `${userId}:${counter}`

    setScopedCounter(counter)

    await db.tournaments.create({
      data: newTournament(name, demo),
    })
  }

  const deleteTournament = async (tournament) => {
    await db.tournaments.delete({
      where: {
        id: tournament.id,
      },
    })
  }

  const enroll = async (player, tournament) => {
    const ts = `${Date.now()}`

    await db.players.update({
      data: {
        tournament_id: tournament.id,
        updated_at: ts,
      },
      where: {
        id: player.id,
      },
    })
  }

  const unenroll = async (player) => {
    const ts = `${Date.now()}`

    const tournament_id = player.tournament_id
    if (tournament_id === null) {
      return
    }

    await db.players.update({
      data: {
        tournament_id: null,
        updated_at: ts,
      },
      where: {
        id: player.id,
      },
    })
  }

  if (tournaments === undefined || unEnrolledPlayers === undefined) {
    return null
  }

  return (
    <div className="mb-4">
      <div
        className="flex flex-row items-center justify-between pb-3 mb-5"
        style={{ borderBottom: '1px solid var(--card-border)' }}
      >
        <label className={clsx('section-label text-small', userColor)}>
          User: {userId}
        </label>
        <ConnectivityControl />
      </div>
      <div className="flex flex-row my-4">
        <div className="basis-1/2">
          <PlayersWidget
            players={unEnrolledPlayers}
            dndDiscriminator={userId}
            onDrop={(player) => unenroll(player)}
          />
        </div>
        <div className="basis-1/2">
          <div className="relative block">
            {tournaments.map((tournament) => (
              <TournamentWidget
                key={tournament.id}
                tournament={tournament}
                dndDiscriminator={userId}
                onDrop={(player) => enroll(player, tournament)}
                deleteTournament={() => deleteTournament(tournament)}
              />
            ))}
            <div>
              <button
                className="button button--secondary button--outline button--sm me-2"
                onMouseDown={addTournament}
              >
                Add
              </button>
            </div>
          </div>
        </div>
      </div>
    </div>
  )
}

const Wrapper = ({ bootstrappedPlayerColors, userColor, userId }) => {
  const { db } = useElectric()
  const { demo } = useDemoContext()
  const [scopedCounter, setScopedCounter] = useState(undefined)
  const [ready, setReady] = useState(false)

  useEffect(() => {
    let isMounted = true

    const bootstrap = async () => {
      await bootstrapPlayers(db, demo, bootstrappedPlayerColors)
      const numTournments = await bootstrapTournament(db, demo, `${userId}:1`)

      if (!isMounted) {
        return
      }

      setScopedCounter(numTournments)
      setReady(true)
    }

    bootstrap()

    return () => {
      isMounted = false
    }
  }, [])

  if (!ready) {
    return null
  }

  const backend = window.matchMedia('(pointer: coarse)').matches
    ? TouchBackend
    : HTML5Backend

  return (
    <DndProvider backend={backend}>
      <Integrity
        scopedTournmentCounter={scopedCounter}
        userColor={userColor}
        userId={userId}
      />
    </DndProvider>
  )
}

const Demo = () => (
  <div className="grid grid-cols-1 sm:grid-cols-2 integrity-grid-cols gap-6" data-testid="offline-integrity-demo">
    <div className="px-3 md:px-4" data-testid="user1">
      <App dbName="user1" demoName="integrity">
        <Wrapper
          userId={1}
          userColor="electric-green"
          bootstrappedPlayerColors={['green', 'yellow']}
        />
      </App>
    </div>
    <div className="px-3 md:px-4" data-testid="user2">
      <App dbName="user2" demoName="integrity">
        <Wrapper
          userId={2}
          userColor="script-purple"
          bootstrappedPlayerColors={['red', 'purple']}
        />
      </App>
    </div>
  </div>
)

render(<Demo />)

Try it out for yourself. Can you cause an integrity violation?

Ultimately, where you used to need a server to provide consistency and integrity guarantees, now you can use CRDTs, strong eventual consistency and Rich-CRDT techniques. In the example above, we use a technique called compensations to ensure that the tournament is re-created if its deletion would lead to an integrity violation.

This is one of the techniques that allows ElectricSQL to support existing relational data models and provide drop-in local-first directly on Postgres.

---

So that was conflict-free offline. Let's now really unlock the power of the system by diving into active-active replication between Postgres and SQLite »