Local-First Software With ElectricSQL

Hi. My name is James Arthur. I am the CEO and one of the co-founders of Electric SQL. And today I'm going to talk about local first software and how you can build it using Electric React.

So just to jump in, so this is a rather simplified diagram of, I guess, old-fashioned software development. So it's showing a cloud first kind of architecture. The user comes along, clicks the button, there's some kind of request gets sent to the server over the network. The server then responds to the result. This could be like a whole page request. It could be Ajax. And then you display the results to the user.

And if you contrast that with LocalFirst software, with LocalFirst, you move the database or store into the local application, the user clicks the button, it goes straight to the local database, and it's instantly displayed to the user, and then data syncs over the network in the background. So that's kind of what we're talking about as a pattern. And just as a quick orientation in terms of code. So this is an example react component that's set up to work in a LocalFirst way. So you have this add function is a callback handler, that's bound to a click event. You call this create method, and it writes directly to a local embedded database, in this case an SQLite database. You can then query that local database directly in the app code, and typically you have like a live or reactive pattern where you're then binding the query results to a react state variable. So that's sort of an orientation to what we're talking about.

So why is local first a good pattern or a good idea? If you come back to this simple diagram again, with this cloud-first pattern you basically have the network on the interaction path, so your availability and your latency is slightly just at the whim of the user's network connection. It's very important to engineer high reliability, like you need the server online to serve the requests, so you sort of push these like high number of nines, and it's very expensive to do that kind of reliability engineering. If stuff takes a while over the network, the user's sat there waiting on a loading spinner, they're waiting for stuff to load.

As a developer, because you're writing code that moves data back and forth across the network, you have to write a load of stuff to handle network errors and failure scenarios, and also because the data tends to be stored in the cloud, it's sort of ripe for what you have today around models where companies offer products to get user data to then exploit the data and analyze and sell it. Those are a bunch of the challenges with this traditional cloud-first approach.

With local-first, what you get is... Rather, obviously, because you move the local database into the application, you don't have the network on the interaction path, you get zero latency. Applications basically default to working so they work offline even when the network is down, they work even when the back-end is down. You have this first copy of the data inside the local application, so users actually own their data. Because you have this kind of zero latency, as soon as a user does something, it's displayed instantly. You have this kind of like quality of kind of instant reactivity and sort of instant feel to the applications. Because the data syncs in the background over active-active replication, you get this natural real-time multi-user sync. It also in many cases simplifies operating the software because you move the way in which you're moving data back and forward across the network away from a whole set of different APIs and microservices to just a standardized replication protocol. And it means that also the back end just doesn't need to be engineered to such high levels of reliability, because the applications can function even if the back end is down. So that's a sort of introduction to kind of generally kind of why local first, and I think if you look at a lot of kind of modern applications that have been disrupting their sector or kind of building out the sort of best possible user experience, they are now being built on this pattern, right.

So why do you need to kind of platform to build on this pattern? And the answer is that, like if you look at those apps like Figma and linear, like they famously have to spend millions of dollars engineering in order to be able to build applications to that kind of grade or quality of experience. And it's because of this fundamental complexity that you get once you combine the way in which you achieve the sort of instant modern feel with this built in multi-user collaboration. So with this kind of a local first pattern, you get this kind of instant reactivity by doing this local right to a database and then syncing in the background.

If you compare it to using old fashioned software, like something like JIRA, JIRA just feels like a dinosaur just linear just kind of feels like modern software kind of should. And those are kind of maybe sort of prosumer kind of tools that people kind of working in design or kind of project management would be using every day. You also have have some of the best consumer tools that are built on this pattern.

So the new Facebook Messenger is built local first, then you go Google Workspace apps that are building collaboration into the workspace tooling. So basically, a lot of the kind of best products are already building on this pattern. And also you see, you see a bunch of platforms kind of evolving where, if you look at the state transfer layer of the application, you've had state transfer protocols like rest and graph QL. And then there's been a kind of evolution to more real time kind of sync layers that are kind of doing the same job of moving data across the network to stuff like super based live blocks, etc. And now what's evolving is a new generation of tools that does this kind of state transfer piece with kind of real time multi user sync, but also they have conflict free offline support. And that's the sort of new batch of local first startups where there's some really, really cool projects approaching it from sort of slightly different perspectives, which Electric is one and our particular kind of angle in that I'll talk about just in a sec.

But what I wanted to discover first is like, so why do you need to kind of platform to build on this pattern? And the answer is that, like if you look at those apps like Figma and linear, like they famously have to spend millions of dollars engineering in order to be able to build applications to that kind of grade or quality of experience. And it's because of this fundamental complexity that you get once you combine the way in which you achieve the sort of instant modern feel with this built in multi-user collaboration. So with this kind of a local first pattern, you get this kind of instant reactivity by doing this local right to a database and then syncing in the background. But because you also have multi-user collaboration, you get multiple users potentially editing the same data at the same time, without going too far into the details. But you probably recognize that once you combine this kind of local right with concurrency, it just opens up this world of distributed systems complexity. You're basically operating on the AP side of the cap theorem. And there's a whole bunch of stuff that you then need to take care of around conflicts and concurrency, which really should be solved in a kind of systemic way at the kind of platform level.

And so, that's basically our kind of background as a team. So, Elektric is a project. It came out of probably the last sort of 20, 30 years of research into the distributed systems that can operate on the AP side of the CAP theorem. We have two of the three inventors of CRDTs, for instance, which is a key primitive for these conflict-free systems on the team. We have Anet Benyusser, who led the development of AntidoteDB, which is the most advanced geo-distributed database for working from the AP side of the CAP theorem. And Walter and Kevin are also just two of the leading researchers that have worked with Anet, Mark and Nuno over the years. So, we sort of come from the background of engineering these kind of distributed database systems. And then we've used that kind of research to build out this like sync layer for building modern apps. And as I mentioned before, just in terms of sort of where we sit out of these kind of local first sync layers, one of the things that we've looked at around the real-time systems is it's really useful if you can just basically take them and drop them onto an existing application. Whereas in order to make the kind of local first or conflict-free stuff work, a lot of the new systems have had to sort of build out their own data models and their own kind of world in a way. And so you get a kind of silo between your existing applications that would be built on, say, something like Postgres or standard database technology compared with... And then the kind of new sort of real-time or kind of live systems where you end up with a kind of silo because they've had to kind of work with their own data models and their own limitations to make things work. So our focus with Electric is basically to build this kind of local first sync layer, but do so in a way that you can just drop it directly on to existing applications and specifically applications that are built with Postgres and React. So in a way specifically, it's like a drop-in sync layer that gives you this kind of magic local first data on top of an existing Postgres data model that integrates with your existing React components.

And so what I'll do is I'll just hop out of the presentation here and just move over to basically kind of show you the platform. So here it is, this is electricsql.com, there's a whole bunch of stuff on here that you can kind of dig into. What I'll do is I'll just jump first through, we have this introduction guide that kind of gives you a... I just sort of walked through kind of some of the affordances of Local First that I was talking about.

So first here, for example, these are all, by the way, just live demos. You can kind of see the code that's actually running underneath them. But this is just contrasting this sort of instant feel of Local First versus Cloud First. So here we were just timing the kind of reactivity loop to basically handle the event right into the database, pick up on the reactivity and re-render the React components when adding items into a database. And you're comparing that with like a Cloud First system. But basically, the Local One just feels instant, right? Everything just happens immediately. With the Cloud One, it can feel instant if you have a decent enough internet connection. But if you've got users that are on a slow connection or mobile, maybe, or just if you're having some temporary connectivity issues, how responsive your application feels is just in the lap of the gods. It depends on what that connectivity is like.

And then, as it says here, as well as being reactive, it's basically naturally real time. So everything syncs for us through the database. So if we add an item over here, what we have in this demo is we have actually two different local databases embedded separately in the page. So there's nothing kind of sharing the data directly between them in the page. Everything's going basically through our sync service into Postgres, out of Postgres and back into the other database. And as you can see, basically everything just stays in sync between the two applications. And this is an example of basically things embedded in the web browser using a WASM SQLite build. You can open it up in your mobile browser, as well. And we also have drivers for native mobile environments, specifically Exspur and React Native at the moment.

And then so one of the great things, as I was saying, is this combination of real-time with conflict-free offline. And so this is basically the same demo that we were looking at before, where we have real-time sync between the two databases. If, for instance, I say that user one goes offline for a while, you can still interact with the application. Everything just works. So user two clears their items. Once you reconnect, basically everything ends up in consistently in the same state. So the model is strong eventual consistency. Technically, it's actually transactional causal plus consistency based on CRDTs.

It's formally proven to be the strongest consistency model you can have for an AP system like this. But the result is basically for the user, it's just resilient to going in and out of network connectivity and the applications just keep functioning.

Now, with that, I mentioned some of this distributed systems, complexity you get with a kind of sync layer like this. This is a demo that's just showing you, for instance, how, as well as maintaining consistency of the data, so you always end up in the same state, we also preserve relational invariance.

So there's a classic example where this is where you have players who can enroll in tournaments, so you can basically enroll a tournament, and as you see, it syncs between the two devices. Now, what happens, for instance, if we disconnect user one, enroll a player in this second tournament here, whilst concurrently this user deletes the tournament, you basically have to have a way of preserving referential integrity. And for this demo, we use technical compensations, which means if that happens, the tournament gets recreated. So there's basically a whole bunch of clever stuff built into the sync layer, which is designed to maintain the sort of standard database level guarantees that you would have coding against a system, like say Prisma on the back end, or just coding against any database system, but we're basically allowing that to work even when you're doing these local writes, even in an offline mode.

And then just maybe just show one last thing. So it's active-active replication, right? So we've been showing basically interacting with the system through these widgets embedded into a web page. Basically, I'll open up a terminal here. And Windows v. Windows. So what we've got is basically the same kind of mini demo here, just embedded into the page. Again, this is all just live code.

You can see if I grab here, what we've got is some user-scoped PSQL credentials. So I can just connect directly to the Postgres database that's underpinning the system. Let's have a look here. There's some of these tables that were just underpinning the demos, players and tournaments, et cetera. And then if you go here, for instance, here's a command that I can enter into Postgres, which will just update the slider to a random position. And as you see, if I hit it, I can just basically keep moving the slider around.

So there's a few really interesting things here. One is that you can have your UE state in the database. So you have unified state management between your data and your UE state, which is actually quite a profound change in terms of simplifying the way you do application development. And also, meaning that you don't get kind of boundaries between those kind of different models between client state and sort of persisted data. Also, so it's bi-directional. So for instance, we just saw basically updating a record in the database and how it's reflected here. Equally, if I run this command to basically watch the slider value, you see that as I move the slide around in the browser, the value is updating in the database. So basically, you can see the bi-directional sync.

So basically, you can see the bi-directional sync. What you also have is with this is that you can then use the database as like a content publishing system. So this is a simple demo, right, where we just have these items. But if I run this to basically just insert an item, you can see basically how the content streams kind of out of the central database into the local apps.

But this is one of the really nice patterns where you basically, if you have Postgres, and you can imagine, for instance, you have Postgres running underneath, like, a headless CMS or kind of any system that can publish into the database. And as soon as the data is written into the database, this sync system just takes care of propagating it out into all the local applications whenever those applications connect. So it's actually a really nice kind of centralized content publishing pattern for kind of a whole range of different types of applications, where, again, that data that you're kind of syncing out like that is immediately available for this instant reactive kind of multi-user collaboration on top of the data because it's two-way sync.

You're not just like publishing kind of static data. So there's a whole bunch of other stuff we could kind of walk through. I thought maybe it would be interesting to just jump into some code and kind of do it ourselves. So we have Quickstart here. This has like a starter template. So if I just grab that, if I go into a sensible folder, then I just run this command. So let's say, call it demo. This is a bit like a kind of standard React starter. It just pulls down some dependencies. It'll take kind of maybe a minute, just depending on my network connection, and then sets us up a template application.

We'll then sort of walk through the steps of starting some backing services. So we run, for the demo purposes, we run a Postgres, and then we have this electric sync service component. It's like a stateless web service that sits in front of Postgres, and it's designed for basically self hosts. So all of this is open source. Everything's like packaged for you to run yourself easily using Docker. There's no complex durability requirements. All the kind of durability is provided by Postgres, and we run on top of just standard Postgres. So you can host Postgres wherever you kind of host it at the moment. You just need logical replication enabled. And currently, there's some things that are out of permissions, but we're working to kind of reduce those.

So here we are. We've created the folder and we've installed the dependencies, so I can go into the demo. If we fire up the code here, then, for example, you can see this is just like a standard React application.

And this is, for instance, where we like, this is the sort of root component where we do some stuff to configure the electric database connection. The way it works is you have SQLite embedded and in your environment, using whichever kind of library would be sensible for that, we then have an adapter pattern. In this case, we're importing a WASQLite adapter, instantiating the database connection, and then we electrify it so you have like a local SQLite connection. You have your database schema, which I'll show you in a sec, and you just have the config of that like auth and kind of URL to connect to. And then we then put that into a provider, which means that the components can get a handle on this electrified database object and you use it a bit like the code we were showing before.

So coming back here, just walking through these steps. So the first thing that we can do is basically just run the back-end services, which we've got wrapped up into a Docker Compose file. You can run your own Postgres and you can connect electric to it, this is just basically we've kind of packaged stuff to try and make it easier to run. Usually that command just runs immediately when it was happening. Okay, just rebooted my Docker there and what I'm going to do is now run the backing services, so the Postgres and the electric using Docker Compose. So, that's your own backend up, that just runs the services.

Here we are, now what I can do is basically apply the database migration. So, what you do with electric is you define your database schema in Postgres and then the schema propagates out to the local devices. So, just here, for example, if you look in db.migrations, yes, here we have a very simple SQL file that just defines an items table and then we electrify it. So, that table syncs through the replication machinery. So, I can just basically apply that migration to Postgres here. And then, for example, if we go into, we can connect to the local Postgres, sorry, and command and then we can see that there's an items table that's been created. That item's table has a few triggers that we add. Triggers in a shadow table that we add to do some of the clever conflict-free merging stuff. And then what you do is you generate a type safe database client from the database schema. So, if I now run client.generate, this basically reads the electrified part of the schema and generates out you a type safe client library. So, you can see that here, for instance, if we go in here, and it uses the Prisma and Zodd machinery, and you basically get a whole bunch of functions. So, here we just have like an items table and there's various functions that you can call on it, and when you use those in your ID, they're all type safe. And it also bundles the initial migrations into the application so that when you build your local application, it knows the database schema that it's working with. And then it accepts ongoing migrations over the replication stream. And so, with that in place, I can now start the application and you basically get a very simple application that's just using electric SQL. And you can, if we just hop into the code, it's very similar to the example I showed you up front. So, this was the instantiation code we went through before. And then here in the components, we get a handle on the database. You do this direct right to the local database when you're adding items.

You have this results listing that's just a live query bound to this, a kind of live version, a rerunnable version of a local database query to get all the items. And then we also have what you can see here in this user effect hook is that we have what we call the shape-based sync machinery. So, shapes are a primitive to control what data syncs out of the post, goes onto the local devices. In this case, we're syncing all of the items because it's a simple demo.

In other cases, for example, if we just hop back to the website and just jump into the shapes docs, you can see that you basically are able to define a kind of filtered subset of the data. So, in this case, it's like load a project and all of the content that comes in underneath it. So, you get the shape primitive to control what data syncs off the local device and then you get these live queries to kind of bind the data to your components.

Maybe just to jump into one more thing here. So, we've shown this very basic example. A slightly more realistic application here is we have a linear clone built with electric SQL and you can just kind of use it. And basically, as you see, all of the interaction is just kind of instant, right? There's no delays, there's no loading spinners, everything just works immediately. And you can kind of come along and create a new issue, and it all kind of goes in. So, you get a sense of this kind of quality of the user experience and everything here is live. So, if we pull up another browser window and you make these changes, you'll see them all just kind of sink like we were seeing with the previous demos.

I hope that gives you a bit of a taste of the system and the development model. You can come, all the documentation here is on our website, ElectricSQL. Maybe just to finish up, jump back into the presentation. So, kind of recapping on Local First Software in general, it's kind of better for everyone involved. Like, for the user, you get this very high quality user experience, you get apps that just work, that are resilient to the network or the backend being down. You get this lovely combination of like, instant feeling, built-in collaboration, and offline support. You also get data ownership and other kind of wider benefits. Also, for the developer, what we've been seeing from companies like Muse and like Linear, who've been building these applications, is they've been reporting back that actually the developer experience is much nicer as well. A, there's a whole load of less code you have to write because you're not doing this kind of imperative state transfer layer. And also, depending on how you do it, but you can avoid rollbacks and tentativity. So, you're just basically writing with this local database without having to kind of engage with the network programming at all. And also, there's nice stuff around having the local database available to us like debug, replicate state, etc. You get this like unified state management. And for the operator as well, it tends to be much, much simpler. A, you reduce a lot of the load on server systems because you have this kind of optimal caching within the local device. And also, you replace our APIs and microservices with this much simpler, sort of standardized replication protocol.

And if you want to learn more about LocalFIRST development, there's a whole load of stuff online. There's a great community, the LocalFIRST Web Community. They have a very active Discord and a website. You can see here with a whole bunch of resources. There's a manifesto by Ink and Switch who kind of coined the term and kind of introduced the kind of benefits of the model, which is really worth a read. And we have this kind of intro guide, for example, that I was showing you before on our website.

And just recapping on Electric SQL, so we're like a LocalFIRST sync layer. It works on top of PostgreSQL. It's designed to be compatible with your existing data models. It gives you a type safe client. It gives you the shape-based sync model. You have live queries, local writes. It does all the kind of reactivity for you. And it's wrapped up with these React hooks and React framework integrations. So it's ready to go to integrate into either a new or an existing React application.

And for more information about Electric, this is our website, electricsql.com. We have a Discord community, which is very welcome to join. If you have any questions or wanted to discuss anything or need a hand building applications. And you can see our GitHub, everything's open source and developed at ElectricSQL. Slash Electric. Thanks very much. The QR code here goes to electricsql.com.