#10 – Matt Wonlaw: cr-sqlite, syncing strategies and incremental view maintenance

So I think it's a lot easier to develop when your queries are co located with your components. So every component is responsible for getting the data it needs. And this makes your app like super composable, right? Like you can add and remove components to your app or your component tree. And because they're responsible for getting their data, like, you don't have to do any additional wiring or prop drilling. Welcome to the localfirst.fm podcast.

I'm your host, Johannes Schickling, and I'm a web developer, a startup founder, and love the craft of software engineering. For the past few years, I've been on a journey to build a modern, high quality music app using web technologies. And in doing so, I've been falling down the rabbit hole of local-first software. This podcast is your invitation to join me in that journey.

In this episode, I'm speaking to Matt Wanlaw, a prolific local-first tool builder who's behind projects such as Vlcn, CR-SQLite, and Materialite. Most recently, Matt also joined Rocicorp to work on their new product. In this conversation, we go deep on his projects covering CRDTs, SQLite, and incremental view maintenance. Before getting started, also a big thank you to Rocicorp and Expo for supporting this podcast. And now my interview with Matt. Hey Matt, welcome to the show. How are you doing?

Good. Yeah. Thanks for having me. I'm super excited to have you on the show. You and I have been collaborating on a few projects now over the course of the last few years. Most notably, you've been working on CR-SQLite and also Materialite most recently. But for those of you who don't know Matt would you briefly mind introducing yourself? Yeah um, Matt Wonlaw, and I guess I've been involved in the local-first community for about two years now.

It originally started with me trying to resurrect an ancient project of mine and trying to add, like, multiplayer and sync between devices. And I saw, like, that's actually a pretty hard problem, and there weren't, like, many off the shelf solutions, so then I just, like, dove into this , and that's where all these projects like CR-SQLite, Materialite and other things came from. Yeah, and I think this is also how you and I have met quite a while ago.

And before that, you've been working at Facebook for many years. And even before that, I think you had your first engineering role working on some software that by definition had to be very local. There was no local-first yet. Would you mind telling us a little bit more about what those first apps were that you've been working on professionally?

Yeah, so like my first job out of college was at a defense contractor called Lockheed Martin, but essentially what we were doing was we were building software for submarines, so like U. S. Navy submarines, and yeah, obviously a submarine has no internet connection, everything has to be self contained, but even beyond that, so the software I built was for, Like these thick clients for the workstations that sonar operators used.

And, you know, there, there are servers on the sub, you know, servers that provide storage and some that provide the signal processing and whatnot. But like an extra requirement was, you know, a sub is in a hostile environment. Things can break. So the work, workstations need to be able to work even if all the servers were down. So, you know, they need to hold the last 24 hours of data. So the sonar operators can, you know.

If every other system's down, they can at least work with the last 24 hours of data that they had. So that, you know, I guess was my first brief introduction to local apps that eventually do need to do some sort of syncing once connections are re established and, you know, reconcile the changes that were made. That's fascinating. Have you learned some patterns back then that you still see out in the wild today?

Or do you think the approach that you've taken back then is entirely different compared to today? Uh, Back then the data was very well segmented. So there was like never really conflicts. But event sourcing was the main pattern. So just have an event log of everything that has occurred. And when connectivity is reestablished, do you know. Play that event log forward from, you know, the time you lost connection to the current time.

Yeah. I mean, event sourcing, I feel like is still a super active topic. I'm actually looking into it much more closely myself for the use case of building Overtone, and I'm also exploring to embrace it a bit more for like syncing and migration strategy for LiveStore. But I also think that there might be a renaissance for event sourcing. We had it for a while with Redux in a much more self contained space, but I think there's a, that's an interesting topic for research and production as well.

Curious to hear your thoughts on event sourcing and how it could fit into local-first. I mean, I really like it. , you have a source of truth, like of all the facts, all the things that ever happened. Whereas, you know, a regular app or state or database that doesn't do event sourcing. You're losing all the things that happen, right? And you just have this snapshot of what things are right at this instant. So yeah there's no way to rewind.

There's no way to, merge other people's changes if they did come in the past. , so I guess event sourcing, you know, probably fell out of favor in some ways because, you know, storage requirements, like are you really going to store all the event logs? And then also, you know, processing those event logs. You do eventually need to process them into some you know, snapshot of state so your app can work with it.

So I think, you know, people seeing that, oh, I have to process the events and turn it into like these tables anyway, like why even do the event logs? Why not just mutate in place?

Yeah, I think in case anyone of the listeners is interested in local-first and event sourcing, please get in touch. I think this is a very interesting topic to research a little bit After building those apps for like submarines which sounds super fascinating, you've at some point also decided to move on and join Facebook nowadays, Meta. I'm curious to hear more what you've been working on there and whether there was any intersection with your local-first topics now.

Yeah. So Meta, , I guess, like, it shaped the way I approached the local-first problems after I left, but the problems there were very , , not related to local-first at all. So I guess, yeah, what I worked on, it was like three different teams, but they all kind of shared the same theme. And a lot of what I was doing, we had a system called Download Your Information. So this is like any Facebook user can go click a button and get an archive of all their data.

So we'd have to like crawl your entire Facebook graph and find Everything you own. So we call it the ownership graph. And then another thing one of my team's built was the deletion framework. So this is like you delete your account. So go delete every post you've ever uploaded, every like you've ever made, every comment, every like ad you've ever run every message you've ever sent across all the products, Instagram, Messenger, Facebook. What's I guess not WhatsApp.

And then, yeah, so that, that was like traversing this deletion graph. And then a third product, like, variation on all these themes was like investigations and human review. So, like, obviously there's lots of abuse on Facebook. And we had to have a way for, like, every product that's ever created, like, Can we make their content reviewable by like a human review team at Meta?

So this is like, yeah, trying to fan out from the content and traverse the graph of things associated with it for review. So I guess like, all this like graph walking was very, goes very hand in hand with query languages and also schematization. So, I guess I mentioned like three different areas of like quote unquote compliance, right? Deletion, download your information, and content review.

Well, if you're a product team at Meta, you don't want to be slowed down with having to integrate into all these systems. You don't have to learn about them. You just want to like ship your product. So like our key focus was how can we make it so developers can just make their product and not have to understand these things. So we kind of, we built it into the schema language.

So as you're defining your schema for your product, you can say like for a given edge, like this is an ownership edge, or this is like a deletion edge. And then just like by declaratively specifying all this stuff all these other systems could just work. And. Yeah, eventually when I pivoted to local-first problem, I was like, wow, this like developer experience of everything being declarative and the schema layer down, taking care of it for you maybe we can do this for local-first too.

I couldn't agree more. Did you eventually land on something where you got those declarative benefits for some local-first problems?

Yeah, so, the project I ended up working on was called CR-SQLite, so I was, like, taking these ideas and adding them to SQLite. So, yeah, SQLite's already, you know, a relational database, and it's already pretty declarative in terms of you can define your schema for your table, and you can specify an index, and you can specify foreign key relations, and the database manages all that for you. But it didn't have any primitives for collaboration, right?

So, like What if I give you a copy of my database and I have a copy of my own database and we both go offline and we both make a bunch of changes? What if we want to like sync our changes together or merge our DBs? So what I started adding to SQLite was this concept of CRDTs. So you could say for a given table, what type of CRDT should we modeled as? Like if it's a grow only set, or if it's like an add or move set, like technical terms in CRDT literature.

And then for specific columns in the table, you could also say like, what sort of CRDTs should be used to merge those columns. So yeah, the idea was any developer who has an app backed by SQLite, they can just go in and make some schema changes. And then that app can become collaborative by allowing merging databases with other collaborators. Got it.

And I think the parallel there is that many things about SQL can be also seen as rather declarative and the schema modeling, et cetera, those is where I think what we gain with local-first software is the the collaborative nature of the apps working together. And I think this is where you.

Brought some of the nice experiences that you've seen at Meta, where there's such a strong engineering culture for having a great developer experience, bringing that to the new topic that you were interested in, local-first. Meta had a really interesting philosophy when I got there. I think like one of my first questions I asked was like, oh, how do we like make sure people use our stuff that our teams, our security infrastructure?

And they're building things to make sure Meta's code was more secure. That was my first team. And like, security at Lockheed was always Everybody hated it. It was like, nobody wants to do it. And there's always this like mandated thing. So I got to Facebook. I'm like, how are we, I'm on security for, how are we going to make sure people use our stuff? And it was interesting. Like the manager at the time was just like, yeah, like we can't do anything. Facebook's bottom's up. We can't do anything.

Make sure anybody uses our stuff. The only way we can do it is make the safe way, the default, and easiest way. Like, people are going to choose the secure way because we've made it the easiest. And like, there's no other way we can make them choose it. So yeah, there's a huge, always a huge focus on everything we did from DevX. So, that like, developers would just pick that solution. Because it was easiest, not necessarily because it was secure, just because it was the easiest thing around.

And then it was also secure. Which is like the nice added benefit.

So after having worked for many years at Facebook, you've later then started working on CR-SQLite, but you didn't arrive at CR-SQLite immediately. I think you were for a little bit also still working on bringing back an app you've been working on before called Strut. So I'd be curious to hear a little bit more about that and particularly given that you worked on it quite a bit before you joined Facebook and then you started working on it again after.

So tell me more about the app and how that led you to local-first. Yeah. So it was my first, I guess, web app I was building. So yeah, at Lockheed, I was doing all these like Java rich clients, and I wanted to get into web dev. So I started building this thing called Strut.io. It's a presentation editor. And I knew people's devices, you know, they have storage and compute, and the browser had, you know, some storage back then. And I didn't want to, like, run servers for people.

I just wanted them to be able to work and edit presentations if they're online or offline and not have to sign up for an account. Like you go to the site and you can immediately start doing stuff. So yeah, like I guess without knowing it, I was building a presentation editor that was, you know, local-first in some ways. And at that time, it was like 2011, 2012, there was, you know, I guess the prequel to the local-first movement, there's like remoteStorage.io and ownCloud.

And I started getting involved with those, but then, yeah, eventually I got the Facebook offer and I just put Strut on the shelf to be resurrected sometime in the future. And then, yeah, when I was leaving Meta or Facebook, yeah, I didn't know exactly what I was going to do. So I figured, oh, I can just resurrect Strut.io real quick and you know, it'll bring in some side income. In the early days before I abandoned it, it had like 10, 000 monthly actives.

So I was like, Oh maybe a decade later we can get back to that again. But yeah, as I resurrected it, kind of the landscape had changed a bit. Like people had a lot more devices and also multiplayer was an expectation where like, You know, Google Slides, right? You can have multiple people on the same deck. So I was, you know, looking into how I was going to solve that problem for Strut.

And, you know, rather than I guess I've been building frameworks for so long at Meta, like, rather than making this a one off thing in Strut to solve the problem. I was like, Oh, like, can I solve it at a lower level? And that's when I went down the rabbit hole of CRDTs investigating SQLite, adding stuff to SQLite and so on.

So the data architecture you had for the first version of Strut around, like, yeah, before Facebook, you mentioned like around 2012 to when you've left Facebook and what you've arrived at. Very different. So I'm curious, like how you went from like whether you took rather some smaller steps or some much bigger steps altogether to rethink the data architecture and how you went about that. Yeah, I mean, I guess beyond just collaboration, I wanted to support Yeah, arbitrarily large presentations.

So right, the original version of Strut, it would load everything off disk and into memory, and then when you're closing the tab, or periodically, it would save. A big dump of memory to disk and yeah, I think that works fine, probably for the vast majority of cases. But I wanted to be able to handle presentations with hundreds of slides and tons of binary content or like images and videos and all sorts of things.

So like at first I was like, okay, how do I like lazily load this stuff off of disk? And, you know, as you're starting to implement that in your own data model, you're like, Oh, well, this is kind of like a database, which, you know, knows how to page in and out things in and out for me. So I think, you know, that was also an indication that, okay, like, something more like a database would be good. And then I don't know how I found that SQLite was better.

I guess I saw James Long's Absurd-SQL and Actual Budget. That's when I first realized that, yeah, like, I mean, I'd always known Wasm was a thing but that like SQLite was being compiled to Wasm and could run in the browser and it was actually faster than IndexedDB when you were, I guess there's some caveats there, but SQLite would store to IndexedDB, but it would store pages. So it would store four kilobyte chunks. IndexedDB, when you're like.

Reading things in and out individually, like single objects by key value is like super slow. And the fact that like, okay, you're using SQLite and reading in chunks rather than individual objects, like it would be faster than IndexedDB. Like if you change your IndexedDB access to be paged, okay, it could be faster in SQLite. SQLite, you can still do point queries and be faster than a point query in IndexedDB.

So that was something else that turned me on, like, oh, maybe I should use SQLite in the browser rather than IndexedDB, so I don't have to implement all this crazy stuff to make IndexedDB go fast.

And then, you know, there's all the problem of, like, collaboration still and my justification for picking SQLite then was, you know, not only is SQLite running the browser now, if I ever want to take this app and make it native well, SQLite, Everything I build for collaboration in the SQLite, like it'll work on native, it'll work on desktop it'll work in the cloud if you want some like cloud peer or something rather than like a JavaScript only solution.

So you made the decision, SQLite is the way forward, SQLite on top of IndexDB or on top of like some persistence mechanism, but you now also need collaboration. You looked a little bit into CRDTs, but you still decided instead of going CRDTs first in terms of the developer experience.

You still want to give a developer yourself SQLite as the primary way to think about the developer experience, but you started to use CRDTs or you wanted to use CRDTs as a implementation detail to bring into SQLite. So how did you go about that? How did you solve that puzzle of making SQLite collaborative out of the box? Yeah, so SQLite has a bunch of mechanisms for extending it. You can make new virtual tables. yeah, a virtual table is essentially a table.

It presents itself as a table in SQLite, except that The implementation of that table is any arbitrary code you want to write, so C code, Rust code, whatever. You can make functions, new functions, like a mat, you know, like min or max or whatever function you want to define. You can make new virtual file systems, so it's pretty extensible from the plugin side of things.

And yeah, one of the teams I had worked with at Meta, they built this thing called osquery way back, I think, You know, some other company may have seen osquery now, but it basically lets you query all details about your OS through SQLite, and they did that through virtual tables. So yeah, for adding CRDTs to SQLite, it was kind of a combination of a bunch of special functions. That like when you create a table so, plus special functions and special virtual tables.

So you can like, create a table, and then you can call a function that converts that table to a CRR, or Conflict-Free Replicated Relation. So just a fancy name for a table that's a CRDT. Or you could use this like virtual table syntax where you're like, I want to create this table as a CRR and then you list all the columns and in the columns, so when you're creating a virtual table, like you can customize that syntax all you want in your extension.

So I could like have a slot for custom data types. So you can say this is a string. And then like another addition to the data type is like the CRDT type. So it's not only text, it's like a perryText, or it's like, not just an int, it's an int that's a counter. Or it's not just a date, it's a date that slash right wins. Yeah, and then and then I guess the last thing, right, like, it's great to have a database that's, you know, a CRDT but how do you merge efficiently?

. There had been some earlier work, actually by a guy named Iver and we actually worked together pretty early on, on CR-SQLite. He did his master's thesis on essentially adding CRDTs to SQLite but some of the, like, Downsides of like that original approach was there's, there was no way to incrementally merge databases. You had to literally send the entire copy of the SQLiteDB to somebody else, and it would like scan every row and merge them all.

So yeah, one of the key problems we had to solve was like how do we incrementally sync And that was done essentially like implementing a global version that gets tagged on every row and a virtual table that knows how to find all the rows past the given version and just send you those to sync. Was there any prior art to combining a relational database and CRDTs or another mechanism to synchronize?

Yeah, so there's Iver's work, and then, I guess, James Long's work on well, Absurd-SQL, but I guess Yeah, I don't know if he ever gave a name to the layer above it that did the CRDTs. But yeah, James work, if I recall correctly, was all in JavaScript. So it wasn't built into SQLite itself. You couldn't just run it anywhere SQLite could run. It had to be in a JavaScript environment. And he took a different approach. I think he used hybrid logical clocks and Merkle DAG.

So yeah, I guess we started getting some trade offs of like, Strut was the first use case. Which actually informed a lot of design decisions for CR-SQLite and why I didn't want to use something like James Long's approach or, I don't know other solutions that were available at the time. Yeah. I'm curious to hear more about your your judgment of the trade offs and what made it a good versus not so good fit for your use case.

Yeah. So I think James's approach yeah, I hope I'm remembering everything correctly. This was like a couple of years ago, but when I looked into it, every single message for all time was stored. So this is, you know, kind of like event sourcing, right? But like, one of my key requirements, like, I was using Strut to drive requirements for CR-SQLite. And I wanted the ability for people to be offline as long as they wanted. For it to be able to run on constrained, low memory devices.

And so those two in combination with the fact that it's like a presentation editor where you can like, you can drag and drop components, you can spin them, rotate them type, like I didn't want every single event for every keystroke and every drag and all these things to be logged for all time, right? Especially if you're offline indefinitely.

So if you're offline for like, I don't know, a couple days editing a really big presentation, you come back offline, I want it to be able to sync almost instantly. So the approach I chose was the set of CRDTs that allow you to collapse history. So, yeah, if you've been off The database size never grows more than a constant factor of the base data. So no matter how much you're editing, you're always going to be within a constant factor of the regular DB size.

I don't know, I think that constant factor was like three or four times, I don't recall the exact number. Whereas the other systems, if you're the longer offline, the more and more the data grew which was, you know, not a trade off I liked. Yeah, I guess the downside to my approach is there's a few less transactional guarantees and you can't, you know, rewind history. But if somebody really wanted rewinding history, I was like, Oh, they could do it in user space.

Like, that's not the problem I'm trying to solve. That makes perfect sense. And I think I really like, you focusing on the use case that you're most familiar with Strut and the requirements around that, Since we're dealing with like distributed systems and programming is hard enough, that you can't really focus on all use cases at the same time. And this way you design for the use case that you know of.

And I think this is where you have like a canvas app or something like that where you move things around constantly. If you work for multiple days, you don't really care exactly about the change you've made over like five seconds, three days ago. You just care about, okay, this is the state it is in right now and that other users converge to the same thing.

Whereas I think a different set of requirements might be for a chat app where you might not send around, like, which sort of keystrokes someone has taken to arrive at the final message. You send the final message, but then those final messages, you might actually care about, okay, this came at this point in time. One message does rarely override another one. So this is, I think, where that might be a better fit.

And that makes also sense that in James use case with Actual Budget that's all about, like, historic transactions. You want exactly those like records and time. There's probably not so many, and even if there are many, they're all kind of worth it. They need to be accurate. So I think that makes perfect sense, and I think there will be like many different solutions for many different use cases.

And I think there's even, you know, an approach how you could apply a bit of like that compaction, even to event sourcing. I think there's like a concept called subsumption, I think. And that allows you to also like flatten some of those historic events. So that's super interesting topic of research, I think. . Yeah, I know there for certain CRDT algorithms and certain event sourcing things, like.

Yeah, there are ways to compact history, but it did seem like, still a pretty evolving area of research. I think now it's a lot more understood especially in the, like, list based CRDT space. Sure, like, I don't know, I guess, Yjs kind of pioneered a lot of that work of, like, run length encoding, everything, and then stuff. Yeah, I guess what I want to quickly mention, so there's, when I was first getting started, there was a community called Braid.org which I got involved with early on.

And yeah, I don't know, that was an amazing community in terms of like learning and being open. So I just want to call them out, like they helped me a ton to understand all these CRDT algorithms and when history can be compacted, when it can't. What kind of weird circumstances you get into if you know you're emerging changes peer-to-peer versus, you know, client server and whatnot.

Yeah. There this entire community, I'm not yet a part of, but given that this year I'm also looking much more into implementing, syncing this. I feel like there's a lot I have to learn about and I would love to also have some folks from that community here on the podcast. Yeah they're trying to add. Syncing to the HTTP protocol, so essentially adding a new verb rather than get, put, and post to allow you to merge documents or resources together. That sounds fascinating.

How far along is that effort? They are part of IETF now. I don't remember if they're a research, there's like two separate types of groups. There's like research groups and maybe there's standards groups. I think those are the two. Yeah, I don't know if they're still in the research phase or if they're on the standards track yet.

Very interesting. I have to look more into that. So, besides choosing the right CRDTs to fit into SQLite through virtual tables I think this, like, just picking SQLite to run in the browser quite a few years ago, that's probably you've must have been running into a bunch of challenges and issues along the way. So I'm curious to hear first getting sort of like a broad overview of the different challenges you were running into and then going into them.

Yeah, I think I wasted months of my life, like fighting SQLite issues. Cause yeah, it was really early where the official SQLite WASM project had kind of like just started. And I guess some of the first problems I ran into was. I guess compiling an extension into the WASM build. So yeah, it's one thing to write a SQLite extension. It's another thing to get it to run correctly in WASM.

It wasn't so hard when the extension was originally written in C, but like, I don't know, like trying to code review contributions in C and make sure there's no like memory issues was a nightmare. So I eventually like bit the bullet, started learning Rust and re implemented the extension in Rust, which I think was, yeah, it became like a third or two thirds less code and just way faster to write after doing that. I think it was worth the time.

But that introduced this whole new complication of like getting the Rust compiler toolchain to compile an extension that can be linked to the SQLite compiler toolchain that then goes through Emscripten to build a Wasm bundle. And let me see what else was there. Transactions was an interesting thing. So the inner, the originally the interfaces to SQLite in the browser were async. And if you ever had Well, I guess there's two builds of SQLite. The official SQLite build was always synchronous.

There's this unofficial build, which I think is better, called wa-sqlite, which all the interfaces were asynchronous. But it had these interesting caveats, like if you await two calls to SQLite at once, it would deadlock. So you had to make sure like there's only one Call to SQLite ever at once. So, you know, working around that challenge. Transaction interleaving was another, so like if it's asynchronous and you start a transaction and you have all the statements in the transaction, okay.

You, you began the transaction and then you like do a read, you're waiting the read. Well, while you're waiting in the read, you delegate control of the event loop, so somebody else could come in and, you know, kick off a new task, right? A new statement to do a write or something. Well, now that's somebody else's transaction that's trying to run while your other transaction is running, so you get this, like, weird interleaving.

So, yeah trying to lock out transactions in the JavaScript layer that was another annoying thing to deal with. And then, yeah, this is not related to WASM, but just getting SQLite with the extension built for all the platforms I wanted to target. So, like, eventually Expo wants to include it in their Expo SDK, so figuring out how to do Android and iOS builds in addition to, you know, the WASM build. And Mac and Linux builds.

Those sound like quite the buffet of different challenges that some sounds familiar to me as well as I think some you've been pioneering a tad earlier. And by now, some of those are maybe a bit more. A well trodden path, but I think there's probably even more that we don't understand yet. One more, I know one more interesting one I think more interesting than the other ones is since SQLite in the browser, at least the one I'm using, is IndexedDB backed.

IndexedDB has a very high cost for opening a transaction which I was unaware of at the time. So yeah, there's a lot of work. And anytime you open a SQLite transaction, it would open an IndexedDB transaction. So there's a lot of work making sure that, like, for reads Yeah, normally you just issue reads, you wouldn't necessarily batch them into a transaction. Like normally, you know, you'd batch your writes into a transaction.

But yeah, to make SQLite in the browser go fast if you're doing a whole bunch of reads, like making sure a transaction was open automatically all the reads would happen and then the transaction would close once all the reads are done. So it's like way of batching that was like a 10x speed up in the browser for SQLite.

So once you've solved all of those performance challenges and also like correctness, syncing challenges, et cetera, then it's also needs to be competitive somehow with the typical developer experience you have in a React app or in a different framework you're using. How did you go about designing that? Yeah, so, yeah, I've developed a set of hooks, so, I don't know, it's, I guess like the React integration was probably the least amount of, I don't know, I'd say it's the least amount of work.

Maybe not once I like think about all the strict code problems and stuff but yeah, essentially the DevEx was like a use query hook. So I think it's a lot easier to develop when your queries are co located with your components. So every component is responsible for getting the data it needs. And this makes your app like super composable, right? Like you can add and remove components to your app or your component tree.

And because they're responsible for getting their data, like, you don't have to do any additional wiring or prop drilling. And yeah, they're not going to break other components because suddenly, Some data dependencies are missing. So I really like that, yeah. Co located queries, so every component you could use this hook called UseQuery. And in that UseQuery, you just literally write your SQL that fetches the data required by that component.

And that SQL could be as complex or as simple as you wanted. So, you know, like, select from issue where id equals issue id or something simple or, you know, something complex like select star from issue, order by modify, join labels join owner, and yeah, all these things. So you've managed to tame most challenges with SQLite in the browser, making sure it works in Wasm, making sure that your own extension is working, et cetera.

And SQLite can be super, super fast for most workloads, but once your cardinality grows, et cetera, your tables get really big then queries can also start Taking a little bit longer. And that is because SQLite always kind of, besides a little bit of caching, always starts with your results for your queries from scratch, kind of. And you know, I've been chatting about this for quite a while back then. This is how I know about all of this context. And that has led you to look into.

incrementally maintaining the query results and the views. So I'm very curious to hear more what you've learned back then and which path this led you down. Yeah. Yeah. So I guess, you know, I was talking earlier about each component fetches its own data, has this use query hook, or just, you know, specifies a SQL query it's running. So when you're developing like a rich client side application like this, ideally your database is reactive, right? So any, anytime some data changes.

It's like you, you work on Overtone, right? The music app. If somebody presses play, then, you know, all the components that care about play state need to immediately update. And yeah, as you're saying in SQLite, if you're doing this somebody changes some state, well, you know, SQLite doesn't have any, you know, facilities for reactivity. It has a few but they're not very good. Like they don't give you many fine grained notions about what changed or like what queries it impacts.

So what you're left doing is rerunning. Essentially from scratch the queries that could have been affected. So, like, the SQLite change notifications, they'll tell you the row ID that changed, but not the contents, and they'll tell you, like, the table. So it's usually not enough to figure out exactly which queries to invalidate. And yeah, so you rerun tons of queries throughout the app.

And those are all rerunning from scratch, so like, if you have, like, for your track list, you're showing a few hundred tracks, and that track list has to do a number of joins, right? Has to, for a track, has to join the album to get the album title, has to join the artist to get the artist names. Yeah, I don't know if there's any other joins. Maybe there could be joins about, like, like status, or favorite status, or something about the track, right?

And, yeah, rerunning that join to select, you know, 200 some items. And yeah, maybe they just want to sort, and sorting it doing that from scratch every time somebody mutates something severely limits I guess how fast you can interact with the app. And yeah, I'd worked, tried a bunch with like, okay, you know, maybe a purely in memory SQLite, and that gets you pretty fast. But yeah, somebody was saying, oh, like, The goal for a reactive database is it's memory fast.

And then when I started, like, I don't know, that idea, a lot just helped my brain and I started like actually benchmarking, you know, if I'm just observing a value in JavaScript, how fast is that? And if I'm like, Observing a query in SQLite and updating the row and re running the query. Like, what is that? And I have a observable notebook somewhere that like compares all this and like the difference was massive.

So like just seeing how far I was from memory fast, like started making me a bit sad on SQLite. And also like benchmarking the original strut and the new one, which use SQLite and these use query hooks.

Yeah, like the original one I could throttle my Chrome, you know, but in the dev tools you can like downgrade your CPU right to like 6x slower or something and I could go all the way down to the slowest possible and everything was buttery smooth, but in the new one, like if I downgraded it too much, like you could see some visible lag. SQLite is fundamentally built in this request response style, right?

This is the era of the LAMP stack where, you know, databases request response made sense, right? A user would go to the website it would do a query against the DB and it would render, right? There was no, like, rich interactivity. It was always a full page refresh every time. So request response made sense. Yeah, but for these rich apps, request response no longer makes sense. And trying to fit a request response sort of DB designed around that, it into this reactive scenario.

And I think maybe one day somebody can get there. But I think right now it didn't seem to make much sense to me and the juice didn't seem to be worth the squeeze. So I started, I guess my love affair with SQLite has slowly been coming to a close. And I started, you know, investigating other projects. Yeah maybe one, one day port it if there's time or. porting some of these projects back to SQLite and contributing incremental data flow, differential data flow to them or something.

But yeah, I guess I should describe what these projects are, right? , So I like the idea of queries, being able to like, Issue a complex declarative query against some set of relational data to get back the data you need for your view. So I wanted to like, how can I , incrementally maintain an arbitrary query against some data? So like that track list example you query the, Tracks and their artists, and their albums, and the play state, and all these things.

How can we, when somebody does it right, rather than re running the query to get the track list it knows exactly which queries. Should be invalidated by that write, and rather than re running them, knows exactly how to patch up the rows that are impacted by the write. So, you know, at first this problem seems absurdly complex and hard. And you're like, Oh, yeah, I want to like invalidate and patch up any arbitrary SQL query. But yeah, I started reading some of the papers.

There's like a paper called DBSP. It's a differential data flow paper. I think Materialize is based on that. And then, yeah, started implementing it.

And then I realized like, I don't know, this, once you've read it and started implementing it and you realize like, this is very similar to other stream processing stuff , like, and other query, you know, builders I've done,, right, where rather than, you know, you create a series of operations and rather than them asking a database for the data, they're like taking streaming data in.

So yeah, like some of the work I'd done at Meta was on real time abuse systems and that was all streaming systems. So I was like, oh, like this problem isn't that mysterious. Like I've done stuff like this before. It seems tractable. So I, you know.

went full on, decided like, I think we, I think I can implement this yeah, based on prior experience, based on having done query languages before so yeah, MaterialLite was born which is bringing differential data flow to JavaScript, so you can compose, filter, map, reduce, and a join operator to create these pretty rich queries and have them reactively updated anytime there's a write. Right.

And so for those of the listeners who have not yet built their own database and maybe are familiar with like SQL where in SQL, you have like select from where, and all of those and JavaScript or other programming languages who you might've like, Map, filter, et cetera. They might have different names, but they're sort of like conceptually very similar.

And this is where you're basically just now like trying to recreate the same semantics that we have from SQL, where you can say select star from this where so, and you can basically have like an array where it now say .Map .Filter, et cetera, and where you flip the trade offs from before you owned in SQLite, you might get this query just once and then it's done.

You no longer are interested in like subsequent changes and then like, smaller updates, but in JavaScript where our app stays warm, we click the button. Something's changed slightly. You want to change the trade offs quite significantly. And that has led you to Materialite.

Yeah, so like just to build off on like how SQL concepts map to like filter, map, and reduce, or like map represents select or function application, like you're selecting some columns, well that's mapping the original object to some other object with a subset of the fields. Filter is the same as where Reduce is what you use for any aggregate function, like group by, sum, count. Yeah, and I guess one other operator would be concat.

So if you want to do or, you just, you fork your stream that you filtered or mapped over, and then after you've filtered it, you concat the streams back together, and then you run a distinct. So you can do an or and then join, joins a special thing where essentially. You kind of maintain what you've seen from one stream and then as you see results from another stream, you link them together based on some key.

At first, it like, Seemed like a large barrier, but then I don't know, once you get into it and you realize, Oh, like I've done all this before and filter, map, reduce, like these have direct analogs in SQL, like it's not so bad. So we've so far like touched the most common usage of SQL. But SQL has, or SQLite, Postgres, et cetera, but like sticking with SQLite for a moment there's quite a bit more than like the common like select from where, et cetera.

There's like various kinds of aggregations, various kinds of ways to combine queries, sub queries, joins, various kinds of joins. Is the system that you've came up with MaterialLite, is that on feature parity on what SQLite is able to do? Or are there some parts where you say, okay, that's out of scope because there's years worth, maybe decades worth of optimization have gone into SQLite into making that fast and that's out of scope for Materialite.

Yeah. So there's no window functions and there's no recursive queries. And base Materialite is strictly for streaming. So, like, if you're processing a stream of writes or events, like Materialite is a great fit because it's gonna, you know, patch up your query results as those events come in. But say you have a table of a million items and you want to query it from scratch, right? Like you didn't see the events, you couldn't maintain any queries.

You're just like, I want to know what's in this table. So, so you're going to have to run that query from scratch, even in Materialite. But for Materialite, what that means running a query from scratch is like setting up the Dataflow pipeline and then feeding literally every row, all million rows into that pipeline. Which, yeah, since everything's implemented with, like, filter and map, where, like, it's opaque, like, when you do a filter, you provide a lambda. It's opaque.

That's what fields you're filtering on which makes this from scratch case hard because, like, Oh, if I don't know what fields you're filtering on, then I don't know if I can apply some sort of index that you might have against this data. So, yeah, I guess after working on Material 8 and getting it to work pretty well, like, okay, that was the next problem. And it's like, we need an actual query language because in a query language is declarative.

You express, like, This is the field name I'm filtering on. This is the operator being used and this is the value. So the engine knows exactly where the fields are ordering by the tables you're using the fields you're filtering on. And once you have that knowledge, you can also make that like first query case fast, where I guess you're doing the traditional database thing of like figuring out what indices to pick. I don't think we'll ever, Be as fast as SQLite for that case.

But I think, you know, For an app like, for a rich client like, Overtone or Strut, right? , Most of your queries are subscriptions, they're not this like from scratch Sort of thing. Like, You're gonna subscribe, you're gonna like, You're gonna like, Set up a query that subscribes to the slide list, set up a query that subscribes to the component list on a slide.

And then as writes happen, you just want to update those rather than, yeah, always, like, rather than having to have first class support for running a query from scratch. So, yeah, so that latter thing needs to be just fast enough, but it doesn't need to be, like, Yeah, the fastest thing in the world.

I totally agree that there's a very interesting, different kinds of trade off that should be explored and should be built that makes so much more sense for a live active application where everything stays up to date and then changes quite minimally as opposed to request response, which is how SQLite works today. That being said, you can still get quite a bit of mileage out of SQLite today. If you are a little bit more mindful about like how you work with it.

So some patterns that I found to Makes SQLite work for me with Overtone. Is that what makes SQLite the slowest in a browser context is shoveling data from within the SQLite database into JavaScript land and back and forth as this is where you need to cross the memory boundaries from memory staying within Wasm to how it's being like marshaled into JavaScript objects. And what I found as a pattern that helps quite a lot is just keeping that small.

So if you need like a, and this is where I think you can apply quite a few of the tricks that you've now mentioned and you embrace with Materialite, you can actually bring back and layer on top of SQLite. Probably still not quite as fast as Materialite.

But probably fast enough to make a use case like Overtone work within that paradigm as well, that when the list changes, instead of like, getting a new list of 5, 000 tracks for that playlist, every time those 5, 000 tracks, you just get a change in terms of like, Oh, this new track was added. And getting that SQLite, I think can also be done with a few tricks namely also like a temporary table where you can save the previous result of your query before and after the change.

And then you query that in a diffing way. And then you still need to sort of like work with that diff information in JavaScript. But this is how you can work around that performance challenge. But your system with Materialite. All of that is absorbed from you, which is very attractive. Yeah, and I think right, so Materialite, right now, it can be backed by an in memory collection, or it's just some little interface that's called a source that you implement.

So I think, Yeah, SQLite could be that source, and then that would solve the like, first query problem. So I just compile the Materialite queries to SQL queries. and then the incremental updates are maintained by Materialite going forward. Yeah, obviously you have to like, listen to the transaction failures and somehow roll those back. But yeah, I think it's doable to like, Marry those two together and have kind of the best of both worlds. And I know there's like a project called GRDB.

I think it's like a iOS SQLite wrapper which adds a bunch of reactivity. it's not like completely general. It doesn't support like completely arbitrary SQL queries, at least in an efficient way. But I think it gets you close enough with the queries it does support. One more thing about like, moving from the request response style to like, queries being subscriptions. It gives you these really cool opportunities to optimize queries at a global level.

So like, if all your queries are subscriptions, you can see what subscriptions are open. And then you can start seeing which queries are either identical and deduplicate or which queries share operations. So like, maybe you have a whole bunch of queries that all do the same join. While in the streaming system. You can optimize that so that join happens once and then the results of the join are fanned out to all the queries that use it.

So yeah, you can do some really neat tricks at a global level once you model your queries or subscriptions. Yeah, that's super interesting. And a few of those things I'm exploring for LiveStore myself where they're basically the problem of reactivity. And efficiency, trying to keep the performance as good as possible, but also squeezing that into a frame budget since LiveStore and Overton all run within the main thread, which I'm not sure that's probably also the way how MaterialLite works.

There is Interesting to also see it through a lens of like a different paradigm shift evolution in the JavaScript front end world which is about signals. And signals is also about like, I think a more lighter weight reactivity system that is all about being composable. And and I think those worlds are now finding, like, a way together within live store with the prior work with Jeffrey and Nicholas on Riffle. This was like some early implementations based on a paper called Adapt On.

And from my understanding all of like the research that has gone into adapt on. And sort of like in parallel, what's been evolving as signals. Those are actually the same thing.

And I think now we can do some really interesting work on combining the power that like more substantial state management primitives, Like a database gives us or something like material light gives us and bring that together with something like a signal system that also kind of out of the box address the thing you've just mentioned, which is reusing parts of the computational graph. The only thing signals is missing. I'm surprised nobody's done this yet.

Is Incremental computation against collections, right? So you think of like, I have an array, and I map the array, then I filter the array, then I reduce the array. That's like creating a new copy of the array each time. And if you like, add one element to the array, you like, rerun all those against the full array again. So it's kind of like the query from scratch case, right?

Whereas Materialite, right, You add an element to the array, well, it's only going to run the filter on that one element and the map on that one element rather than running against the full array. So there's some interesting benchmarks maybe I can share with you after of like, yeah, incrementally maintaining a map filter reduce pipeline against an array of a million items. Yeah, so it'd be really cool if like the signals implementers would even implement this sort of stuff eventually.

So even though your love affair with SQLite has for now come to an end. I think the problems and goals you're chasing after are still very similar to what I think is feasible with SQLite, just maybe with like a different performance footprint. I think with SQLite you also get the benefit. of just that being like a very familiar and trusted thing that's like been proven and battle tested for literally decades now.

And even though you said like you were running into some performance issues SQLite is still so ridiculously fast that it's very feasible to do write those applications in the, in a browser context, et cetera. And I think even if you now on a modern CPU straddle your your CPU, it's still manageable. But I think there's multiple paths to arrive at the same goal.

And so as my understanding is that the project Materialite opened a couple of really interesting follow up conversations for you which is now opening a new chapter for your professional life. So do you want to tell us a bit more about that? Yeah, I guess, yeah, Materialite is my least known project, least complete project. But yeah, it did pick up some interest by Rocicorp specifically Aaron, who I met at the local-first conference in St. Louis.

And yeah, we're talking about, you know, the model of subscript, those queries as subscriptions and like having a reactive app and I think he's the one that said like, oh, things need to be memory fast. And yeah, so, so I started working on MaterialLite and we did some explorations of like, if you powered one of their demo apps with MaterialLite, like they have a linear clone that's a demo app and they want to see like, Oh, how many issues can we scale this to?

If we like use this differential data flow approach. Yeah. And they really liked kind of the initial results. And yeah, we worked together, I guess. quite a bit over the last four slash six months. There was like a two month break for me on paternity leave. And yeah, so I'll be starting a job with Rocicorp here pretty soon as a partner. Yeah, I'm super excited about that. That's amazing. I mean, we had Aaron on the show.

I think that the second show for localfirst.fm, and there were so many deep insights there by Aaron, and I'm a big fan of the products that's already been built there, Replicache, Reflect, and sounds like your work with Materialite is also going to be incorporated into making the products even better. So I'm curious to hear more of your thoughts on you know, what do you feel like where's local-first going with the products you're working on with Rosicorp?

Local-first provides a superior DevX because you're no longer worried about APIs of, like, how do I get data from the server? Like, a sync engine, or your database is solving, you have a sync engine solving that for you, right, where you're just coding against local data rather than setting up all these weird REST APIs that you have to call to get the data. And yeah, I think that's the key value of local-first and the main thing that Rocicorp is pushing forward.

It's like making that sync engine experience as best, as good as it can possibly be.

So you're now joining Rocicorp where you're building Replicache and Reflect, but the local-first space has grown quite significantly to a point that can be almost a bit disorienting. For newcomers who are trying to first figure out what does it mean to build the app local-first and then also choose the right tool for the job. Can you provide a little bit of guidance which how someone should think about use cases and picking the right tool?

Yeah. So I guess maybe when you're somebody, when the first question somebody asks, should ask themselves is, "Do they want servers involved at all?" Is the first question. So, like, if you want all sync to go through a central authoritative server, and you want that server to be able to override decisions made by the client apply data integrity rules revert changes made by a client Replicash is a very good choice for that.

If you want something, Where, you know, you don't want to preclude the ability for peer, for nodes to sync peer to peer, or maybe the data they're syncing is end to end encrypted. I think something like CR-SQLite actually shines there. CR-SQLite lets you merge in any topology you want, whether it be peer to peer or hub and spoke.

So we've seen a couple of companies that are, have like end to end encryption, where they use CR-SQLite to sync, or they have literally a pub sub channel, where all peers just publish their messages, and any peer who receives the messages integrates it. In those use cases and then I guess if you're really looking for, like, if your data model is like document based, I guess going back to syncing, so not just CR-SQLite for peer to peer, but also Yjs and AutoMerge is capable of peer to peer sync.

They're also, of course, if you can sync peer to peer, you can sync through central server too.

If you're looking for, you know, you just want to support a collaborative text field collaborative text doc and you don't need, you know, ways to query over your documents, like you're fine just storing this doc in a blob in your Whatever your storage is you know, Yjs is a very good choice because it's primarily targeting it at text editing, collaborative text but they don't have, you know, facilities like built in for persistence or querying and these sorts of things.

So I haven't kept super up to date with AutoMerge, but from what I've seen if you want like, the ability to fork and look at different portions of history. So like, say people are editing a document somebody wants to fork their document off, make a bunch of changes and merge the fork back in I mean, I see AutoMerge talks about this concept a lot. So I think if it's not there already, AutoMerge would be your best bet for some. for that sort of behavior. Yeah, then there's ElectricSQL.

So that is like a, you know, Postgres centric solution. So if your database is Postgres and you're okay with, like, merging based on CRDT rules You know, that's a good choice. So yeah, so I say like your back end is Postgres and you're okay like using CRDT rules. Like yeah, what does that mean? So a really interesting thing about Replicache is it can work with any back end, pretty much any back end. So Postgres, MySQL, all sorts of things.

And the way this works is that it uses sort of a rebase model where , the developer, defines their mutations. And those mutations get run in a specific order on the server and clients when they receive updates from the server, any mutations they have outstanding. They apply the server updates and any mutations that are outstanding, they rebase on top.

And this is like super flexible in that you can write any, like, if you want to change merge and conflict behavior, well, you just write your mutations however you like to get the behavior you want. Whereas something like CR-SQLite or electricsql to get the merge behavior you want, you have to pick specific CRDT types. And it, it seems a little bit less flexible since you can't write like a custom mutation.

But ElectricSQL does seem to have a pretty good end to end story of like, you set up your Postgres DB, that schema gets replicated down into a local SQLite DB, and as you make schema changes upstream, they flow downstream. So in terms of like, Having a good story between two specific databases it seems pretty good. And then I guess the last thing is PowerSync. So PowerSync, uh, works on a different model that's not rebase or CRDTs.

So with PowerSync, a client will accumulate changes, and if a client has local changes, it won't take any changes from the server. So in the rebase model, if a client has changes, it'll take changes from the server and then replay its local changes on top. Uh, but in the PowerSync model, the client has local changes, it won't take any server changes until those local changes have been incorporated by the server, and then it can take server changes.

Um, So yeah, to me, I've never built an app with PowerSync, but this seems like it'd be a lot higher latency, uh, for the syncing, because if you have any outstanding local changes, you simply cannot take a change from the server. Whereas rebase model, if you have outstanding changes, it's fine. You get the server changes, you just replay your local changes on top of the server changes.

That's a really great overview, and there's many other tools that more more tools than we have time to exhaustively can cover here. But I think that's a really great orientation, particularly with Asking yourself the first question, like where should the authority originate from? Do you want to have a server? That's, I guess, more traditional how web apps work today.

And that can simplify things a lot because in that server you can enforce if there's like a merge conflict or if there's like maybe a user has set some data in a way That might be not compliant with the app that you want to build or for other simplification reasons, et cetera. So I think picking that, where does the authority live? Is there an authority? And , which role should a server play? I think that's a really important one.

And then also the two kinds of like ways to go about syncing or at least two ways going more about certain spacing, everything on CRDTs. Or going with a event rebase model, our Replicash is working. I'm curious how someone who has used neither and might have not really a lot of intuition for what are the implications of choosing one or the other. Can you provide a little bit of an intuition which kind of app use cases are a good fit for one or the other? Yeah. So with CRDTs, you don't.

Have code on the right path, like the CRDT algorithm is going to determine, I guess I should say you don't have code on the merge path. The CRDT algorithm is going to determine how things merge. And you're just picking a set of CRDT algorithms where something like Replicash, where you write custom mutators. You can write a set of mutators that run on the client, and you can write mutators with the same name, same args, but do something totally different on the server.

Which, one it's, since you're writing code in the mutation, it's giving you control over, kind of, how things merge and sync. And two, since The code doesn't have to be identical on every node because the server is the authoritative answer. You could do something in the server mutation like check permissions or, you know, set last modified times or something from the server's time rather than the client's time.

Yeah, but I guess the one downside to that is a CRDT, like, you can sync messages in any order, like, yeah, if you have message A, B, and C, if you sync them B, C, A, or C, B, A, like, you always get the same state, which means that you don't have to have a single server being the authority.

You can have every peer be on equal footing and essentially have no server whereas like this, you know, Replicache model, you do have to have, Someone that is that authority that's going to provide the total ordering of messages.

And to me, it sounds like at least the latter, the rebasing model with that authority living in a server, for example, that is a bit easier to get into less of a radical shift of like thinking, changing the way, how you think about data modeling, et cetera, and it's probably the easier one to get into and might be a better catch all solution for many app use cases.

Like if you look at yeah, I think it's the easier, it's like, it's more familiar and if you have an existing app that you're trying to add multiplayer to Like, yeah this can work on your existing data model without much changes or with little to no changes. Whereas, oh, you want to switch to CRDTs, like you might have to make a new data model specifically for this collaboration thing and migrate your stuff over.

Right. And I mean, the advice I would give on top of that is that. There are now like so many interesting and easy to use options out there, whether it's Replicache, whether it's AutoMerge or others just maybe try building the same mini version of your app in different technology stacks, see what works best for you, and you'll develop that intuition just by yourself. And I think that's a really fun thing to do.

Cool. Matt, thank you so much for sharing about like all those different projects you've been involved with. I'm really excited to see what you'll be building at Rosicorp. Is there anything else you want to share with the audience now? Yeah, just stay tuned. We're building the next. Kind of iteration of Replicache and Reflect.

And , it was exciting enough to make me like drop everything I was doing and join the team and yeah, go back to, I yeah, work, working for somebody, I guess, rather than just, you know, following whatever research interests, wherever they took me from day to day. So yeah, it's, I think what they're building is super compelling and You'll hear more, you know, over the summer and coming months. That's incredible. I'm really excited to hear more about what you all have been working on.

And yeah, thank you so much for coming on the show. Yeah. Thanks for having me. Thank you for listening to the localfirst.fm podcast. If you've enjoyed this episode and haven't done so already, please subscribe and leave a review wherever you're listening. Please also tell your friends about it.

If you think they could be interested in local-first, if you have feedback, questions or ideas for the podcast, please get in touch via hello at localfirst.fm or use the feedback form on our website, special thanks to Expo and Crab Nebula for supporting this podcast. See you next time.