Active-active replication

In the previous steps on realtime multi-user and conflict-free offline, we've seen data sync between users and their devices. What we haven't yet seen is how it syncs via and with a Postgres database in the cloud.

SQLite <> Postgres

Below, we've again embedded a simple local-first demo app with a slider and some items you can add and clear. This time, below it, we've also displayed the access credentials to connect to a cloud Postgres database.

Live Editor

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 Replication = ({ itemColor, slider }) => { 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> <label className={clsx('section-label text-small', itemColor)}> SQLite </label> <div className={clsx('my-8', itemColor)}> <SliderInput min={0} max={100} step={1} value={sliderValue} onChange={setSliderValue} onChangeComplete={syncSlider} /> </div> </div> <ItemsWidget add={add} clear={clear} items={liveItems} itemColor={itemColor} disableWhenInProgress={false} /> </div> ) } const Wrapper = ({ itemColor }) => { 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 <Replication itemColor={itemColor} slider={slider} /> } const Demo = () => ( <App dbName="user1" demoName="active-active" bootstrapItems={4}> <Wrapper itemColor="electric-green" /> </App> ) render(<Demo />)

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 Replication = ({ itemColor, slider }) => {
  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>
        <label className={clsx('section-label text-small', itemColor)}>
          SQLite
        </label>
        <div className={clsx('my-8', itemColor)}>
          <SliderInput
            min={0}
            max={100}
            step={1}
            value={sliderValue}
            onChange={setSliderValue}
            onChangeComplete={syncSlider}
          />
        </div>
      </div>
      <ItemsWidget
        add={add}
        clear={clear}
        items={liveItems}
        itemColor={itemColor}
        disableWhenInProgress={false}
      />
    </div>
  )
}

const Wrapper = ({ itemColor }) => {
  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 <Replication itemColor={itemColor} slider={slider} />
}

const Demo = () => (
  <App dbName="user1" demoName="active-active" bootstrapItems={4}>
    <Wrapper itemColor="electric-green" />
  </App>
)

render(<Demo />)

Run it a few times and watch the content sync in. It's fun!

More to the point, it turns your existing management interfaces (anything that writes to Postgres, be it Rails or Retool) into a content distribution tool for your local-first apps.

Schema evolution

You can also use Postgres to update your local database schema. Write your DDL migrations using your preferred web framework. Apply them to Postgres. ElectricSQL picks up on the changes and propagates them through the replication stream.

---

So we've seen active-active replication and schema propagation. Now let's see how you control these replication flows using dynamic sync controls »