The Duke of SQLite: Litestream with Ben Johnson
January 17th, 2024
34 mins 14 secs
About this Episode
Ben Johnson (@benbjohnson) is the creator of Litestream and LiteFS, two open-source disaster recovery solution for SQLite. Litestream is designed to provide continuous backups for SQLite databases by streaming incremental changes, allowing for easy data recovery in the event of a server crash. LiteFS, on the other hand, is built on LiteStream but uses transactional control to focus on replication and high availability. Join us as Ben discusses the challenges and trade-offs of open source contributions and the future of databases.
Contributor is looking for a community manager! If you want to know more, shoot us an email at eric@scalevp.com.
Subscribe to Contributor on Substack for email notifications!
In this episode we discuss:
The history of how Ben got involved in SQLite development out of “spite”
How Litestream “works on a fluke”
Different use cases for Litestream vs LiteFS
Why fully open contributions isn’t always Ben’s style
The greater server-side SQLite landscape
Links:
People mentioned:
Philip O’Toole (@general_order24)
Other episodes:
Ben Johnson:
I got in the habit of just releasing stuff. I talk about it, put it out there. I like the open source side where it is just a community of people trying weird stuff, seeing what sticks.
Eric Anderson:
This is Contributor, a podcast telling the stories behind the best open source projects and the communities that make them. I'm Eric Anderson. Welcome Ben Johnson to the show. We are excited to continue our SQLite frenzy with, I don't know, the King of SQLite, but certainly one of the royal family here. We did just record rqlite and then we did Turso a month ago or more.
Ben Johnson:
It'll just be a SQLite podcast going forward.
Eric Anderson:
I don't know whether to call this a Litestream episode or a LiteFS episode, but it is a SQLite episode. Usually I have you start by telling us what the projects are, an elevator pitch, so to speak, to ground the conversation. You could start with one or the other or however you want to do that.
Ben Johnson:
Sure. Litestream probably makes the most sense. That's the first one I started on. So Litestream, the idea of it is it's meant to be disaster recovery essentially for SQLite. So usually when you run SQLite, it just runs in a single box and you could do backups every hour, but really what you want is you don't want to lose almost any data.
So what Litestream does is it takes advantage of the fact that S3 is super cheap to upload to, but expensive to download from, and it just has a streaming replication every second to S3 of incremental changes. Then if your server crashes or just blows up, then you can actually just download up to a second or two from your disaster essentially and recover all your data. That's the elevator pitch for Litestream.
Eric Anderson:
There's a million ways we could take that intro, but just to noodle on one thing, SQLite, I think for a lot of people they imagine it in the browser or on a local device. Is that the typical place where people are backing up their SQLite from, or is this meant for a server side SQLite?
Ben Johnson:
Yeah, I really want to make server side SQLite work. So my background, the last maybe decade or so was just in databases. I run another one, a database called BoltDB. It's a pretty popular Go keyvalue store. I really tried to make an application stack essentially on Bolt to see if I could make a key value store work for a main storage. It worked in a lot of ways, but you really start to miss schemas and indexes and those nice little things you get from SQL databases.
So the next step is looking at what is out there for a SQL based database, and really SQLite is the main one out there, just runs on everything and is rock solid. So I transitioned over to that. Actually for the Bolt stack, I tried to make it Bolt, Go and I think it was React I think had just come out at the time, or it was recent. So I was going to make the Burger Stack, which I thought was a little play on words, BGR. Anyway, it never took off, but the marketing was on point.
Eric Anderson:
Yeah, I don't know what makes a stack acronym take off because there's been a lot of good ones of late that haven't quite found escape velocity. So that makes sense for why you want something that's not a key value store, so you want SQLite. But maybe now do the opposite comparison. Why do service side SQLite and not Postgres or MySQL?
Ben Johnson:
Yeah, by all means. I use Postgres and MySQL, and I used to be an Oracle DBA way back in the day, and I have nothing against the client server model. I think it works in a lot of instances and there's pros to it and cons, but once you start developing against a local SQLite database, it is just wicked fast to do. You don't have the concept of N+1 query so much. You might have one SQL query that'll pull a list of orders and then for each order you need to do a separate query for each of your items, and you start to explode the number of queries and just the latency between the server and the client just blows up.
You have to keep doing all these round trips. And when you have SQLite, everything is just right next to it in process and it's just orders of magnitude faster for those for optimizing that kind of thing. And I feel like we're getting to that place where we have these servers that are just super beefy and cheap and have fast storage and the speed of light is like your limiting factor when you're going between your application server and your database. So that's the ideal fit.
Eric Anderson:
Super. And I'd like to get into your background and the background of the project just because we had talked about these prior episodes, maybe it's worth flashing that apparently you've worked with Philip not only in SQLite open source land, but you're in the same company together and you've already alluded the fact that you've got a database background. So take us through the small world that we live in.
Ben Johnson:
Sure, no problem.
Eric Anderson:
And why are you working on this?
Ben Johnson:
Sure. Yeah, it's weird. I actually started as an Oracle DBA maybe 20 years ago, and then I've done Pearl, I've done JavaScript applications. I've done the gamut of everything. And about 10 years ago I was working at this company that did behavioral analysis, so basically had to log data and all kinds of interesting stuff from big companies which took and tried to ingest all their logs. And they had this really terribly slow way of doing it, where they'd ingest it into a SQL server database and then they'd run queries and it took a week to process data and what was that map reduced thing that came out?
Hadoop had recently come out and I was like, "Hey, you guys should try Hadoop." And I was the data visualization guy at the time, I didn't even do databases. I was like, "I think this would be way faster." And they're like, "No, we're not going to do that." So after I left that company just had this little tickle in my head where just out of spite, I wanted to see if I could make it faster, what they were doing. So then you just go down this rabbit hole and when you start making things faster, there's no end to where you go.
So I started with, I was doing some Redis stuff and then I was like, "Ah, it's not fast enough." So I'd go a little further down and I'd start writing... You just slowly start writing your own database, you don't even need to. So I actually ended up writing this proof of concept of a behavioral analytics database, even had its own query language and parser and all kinds of stuff, but would actually write to disc and then query stuff super fast.
Eric Anderson:
It's interesting, the life decisions we make. That this one little curiosity set the path for the career in a sense.
Ben Johnson:
It's like spike driven development. So yeah, I started going down that road and it was really just a side project at first. And the actual funny thing about that was that I was doing a talk at a local database distributed systems meetup, and I gave this example of, "Hey, if you're, say Shopify," and they were a new company at the time, I was like, "Hey, you could analyze how people flow through and where they drop off." It was funnel analysis early on back then, and you could see where people would drop off at different stages and what they would do instead.
And it was an interesting way to visualize all this stuff. And two weeks later I get an email from somebody at Shopify. They're like, "Hey, I saw this video of yours." And I ended up going out there and I ended up working with them for about a year and a half until they IPO'ed. And then yeah, I switched over to working with Influx Data for a bit. That's where I met Philip O'Toole who does the SQL distributed SQLite implementation.
And yeah, we actually don't even discuss the SQLite stuff much until recently. We're just on both our own divergent paths and just met in the middle. But yeah, he's a great guy. I like all the stuff he's doing. Definitely a different approach, but I like that. Everybody has their own take on how to do this really specific weird problem of distributed SQLite. I think it's healthy for the ecosystem.
Eric Anderson:
There's got to be a part of you that gets a little bit of buzz out of like, "Oh, other people think this is interesting and want to solve it." But...
Ben Johnson:
For sure. I think the worst thing is being the only project in the space. Does anyone care about this problem besides me? So it's great to see competition. And honestly, my end goal would be to make software development easier for people in whatever way that looks. And if someone else comes by and they have some solution that's 10 times better than Litestream or LiteFS, more power to them, I'll switch over and use it, that'd be great. Somebody else solve this problem, where we don't have to spin up a database server and all these other things, we can just deploy out code and not worry about it.
Eric Anderson:
So helpful background. You're clearly curious about something, it led to a distributed data processing career and particularly maybe something about SQLite databases, but I don't know that we've gotten to Litestream quite yet. Where are you the day of the first commit thing?
Ben Johnson:
Oh, sure. So Litestream, there was more than I knew. SQLite seemed like the way to go. If you wanted a fast embedded database, I hate all the complexity going on with all the stacks I get to set up of Postgres and then maybe need some caching using Redis or whatever in your stack, MIM cache. And just all the different components and having to set them up, and one breaks and just debugging all that's such a nightmare that for the majority of small to medium-sized projects, I feel like they can get by on SQLite running on a half decent sized machine somewhere out there. So I wanted to figure out that problem.
It seemed really interesting to me and I realized that SQLite was the thing I wanted figure out. That seemed to be the main tool, but actually figuring out how to hook into that was the big problem. They don't really give you a great API and I wanted to make it something where you don't have to custom compile anything. You just plop this thing in and it just works. There's minimal configuration. I didn't want the application to actually know about the underlying ops side of it where it could be running against a regular SQLite database or it could be running against this thing that's also uploading to S3.
You should know that from the application side. So it was really more just a bunch of iterations. And even for a while I was looking at rewriting SQLite and Go just more to understand how SQLite works. I tend to rewrite projects to understand them. And so I was doing that for a bit and just one day reading through some of the docs. Litestream, it works on a fluke almost. So the way that SQLite works is that it does this thing called write ahead log or there's a mode that it has where it'll essentially write all your changes to a separate file called the write ahead log, and then it just depends onto that file over and over again.
Each new page it writes, it depends onto there, and then eventually it gets too big and it needs to do a thing called checkpointing, which is where it takes all those changes and then copies them back over to the main database and it restarts that log. But the thing is with SQLite, it can't actually do that checkpointing process until it doesn't have any transactions going on. So Litestream essentially hooks on and does a read only transaction, so it's like a long living read only transaction.
So we can look at the data behind the scenes as it's getting written in, and there's some checks and things to prevent it from missing data in there. So it doesn't actually use an approved SQLite API, but it goes through SQLite channels and all that stuff, the way you're supposed to use SQLite.
Eric Anderson:
You found a programming interface, just not the one they intended you to use.
Ben Johnson:
It's not official necessarily, but just how their logs work and all that. It's just stumbled upon it one day and I tried it out and it worked. It was great.
Eric Anderson:
So SQLite's famous for being a single file. My database is just a file. And so this write ahead log is a second file that's maybe hidden somewhere that's temporary.
Ben Johnson:
Yeah, essentially. And it's a little bit of a misnomer. There's actually four different files you can have SQLite database, but yeah, essentially you can think of it as a single file.
Eric Anderson:
So you create Litestream. Did you have an ambition? You were excited about making this thing real.
Ben Johnson:
I didn't really think anybody was going to take it seriously.
Eric Anderson:
So how do you go about launching an open source project? Was this your first and what does launching SQLite look like to you?
Ben Johnson:
No, it's not my first. I had some decent success with the BoltDB.
Eric Anderson:
That's right, I'm sorry.
Ben Johnson:
Yeah, that one got pretty popular and then I eventually archived that one and SED who was Core OS at the time before they got acquired by Linux, or no by Red Hat, I think they eventually took it over. But yeah, I think I've gone up and down through a couple different projects. I feel like I've started a ton of projects and a couple of them worked. So you can find it's just a graveyard of repos on my GitHub and a couple that actually worked out. So I think it's, I got in the habit of just releasing stuff. I talk about it, put it out there, and I've always just had an interest in trying things out.
I like the open source side where it's just a community of people trying weird stuff, seeing what sticks. There's a thing called symbolic execution in computer science where it's used for testing or you can use it for a lot of things. You can actually write out a program and then you can actually generate test cases for it based on... You basically make these math equations for each branch as it goes through your program and you feed it into what's called an SMT solver. And then they can spit out inputs that would solve all the different branches and they were super weird nerdy projects.
And I spent probably six months doing a port of one of those, it's a C program called [inaudible 00:13:19]. I ported over to Go and used Go's SSA format. I spent tons of time on it and then it just went nowhere. No one cared. Probably the most advanced project I've worked on, and it just fell with a thud. So you really never know honestly. I think the more accessible the project is, I think that helps a lot. People love SQLite, so in hindsight, I think that was a natural thing. People want to see things that they already love get additional support.
Eric Anderson:
And was Litestream ever to be a startup? Eventually you made your way into Fly, I think in relation maybe to your Litestream work.
Ben Johnson:
Sure. Actually Fly actually purchased Litestream as a project, which is unique. And then I came on as well with that obviously. And it wasn't ever meant to be a startup or anything. I think I had some thoughts of I wanted to create a service where people could make it easier for people just to be able to continuously stream back up or use SQLite's in some way. But I didn't envision it as the next billion-dollar startup thing. I've tried doing startups and things in the past and they fail miserably. They never get anywhere off the ground. So I've realized that's not my forte.
And so yeah, there's no big ambitions, but I think with Fly, they'll want to make things where people can run their application easily across multiple regions and whatnot, and just make things really fast for end users. And that was what I liked about SQLite and where I was going after Litestream, I was trying to make some replication on Litestream itself, and that worked, but it eventually got forked off into a separate project. We needed to rework a lot of things to make it work, and that's what ended up being LiteFS essentially.
Eric Anderson:
So Fly could have forked Litestream presumably, but then that would just confuse the world, like why and what. So by buying the project, they get your endorsement and you get to keep working on it. And there's one community and everybody's happy
Ben Johnson:
Yeah, and they do a great job. Honestly, one of the reasons I came on is because they do support a lot of open source creators and they're not even very flashy about it honestly, but there's a lot of projects out there that they're the highest donors too. So I like that side of it. But yeah, I don't think they had an interest in taking it over or trying to be some big name around it. I think they just saw it as helping to enable their users to run stuff better. So I think that was more the ethos. And honestly, unless you have experience and doing database C stuff, it's not much fun to be on this really low level piece.
What we wanted to do with the LiteFS was, so Litestream hooked in through the regular SQLite transactional hooks-ish sort of. And then LiteFS takes a different approach where we really wanted to make it feel like you're writing just a local Litestream, but you wanted to be able to have those rights automatically replicated instantly to your other nodes and have that all work seamlessly. And you have a lot of things around ensuring consistency. You don't want a separate process to die and then come back and then it gets inconsistent from where the data's at.
So we actually built it as a user land file system, which is a weird approach, but it can essentially intercept rights and basically check them, see where a transaction begins and ends, and it can package those rights into a file that we then shipped to the different replicas in real time. So it's much more strict in terms of consistency of the actual file contents, whatnot. Litestream did what it could with the API it had, but we really just wanted a little more control around it. So that's where LiteFS ended up going.
Eric Anderson:
Should LiteFS be then thought of as a next generation?
Ben Johnson:
I think it's really more like a separate thing. Yeah, Litestream is really great if you just have a single node and you want disaster recovery. That's really the aim of that. And then the two most requested things and Litestream were people wanted to do replicas. So essentially they had a primary and then writes immediately go out to other nodes and then they could query off of those as well.
And then the other one was having failover. So if the primary dies it fails over automatically to another node, and that was the goal with LiteFS. So it has a few more moving parts than Litestream, Litestream is pretty dead simple. And then LiteFS, it has some hooks around being able to switch what the primary is and then hook into the other replicas. So it's really more of a replication and high availability tool than Litestream is.
Eric Anderson:
And I shouldn't go here, too much of a noob, but I'm going to. The way you described this other API, that LiteFS is using, felt reminiscent of people talking about using the Postgres wire protocol. This isn't a wire protocol that you're?
Ben Johnson:
No, it's not a wire protocol. Although I just make a project that actually you can interface with SQLite databases over the Postgres wire protocol.
Eric Anderson:
Oh gosh.
Ben Johnson:
It sort of works. That's pretty hacky. Don't use that.
Eric Anderson:
What's the state of LiteFS today? It's a database you can go run on Fly?
Ben Johnson:
We run it internally in production and essentially you set it up and it's great if you need... A lot of times your biggest overhead in terms of latency is just geographic. So if you have people that are around the world, especially a latency from US, I don't want to say here to Europe, but the US to Europe is usually about a hundred milliseconds is the ballpark. And from the US to Asia around a quarter of a second, 250 milliseconds.
So if you can actually place data over there that's right next to your application, you can just get so much faster response times from your web apps. So that was the impetus of what we wanted to do and we're trying to make it as dead simple as possible. So there's not a lot of thinking about, you don't need to be a distributed systems expert to run this thing. That tends to be a problem in the distributed systems world. It's like everyone needs a PhD to run anything.
Eric Anderson:
So I mentioned to you, we had Turso on a couple months back, they were describing libSQL mostly from the open source angle. And I should ask you about SQL and the SQLite world later, but they also do some edge SQLite things. Could you help us understand the differences or similarities between the two approaches?
Ben Johnson:
Sure, no problem. This is no knock towards Turso. I think there's trade-offs for both. And honestly, sometimes I feel like people in the SQLite community should make some drama. I don't know, but we're all friends. Everyone like, "Oh, that's cool," and people borrow from each other and it's fun. But yeah, Turso, the way they've done it is they're still essentially a client server tool where you're connecting up to another server. It's usually at an edge location. It's not in process, like LiteFS would be. They do have an option where you can actually embed replicas, like read only replicas locally with your application.
That's a newer feature I believe. But yeah, they've taken a different approach in how they hook into SQLite. They actually just forked off the project and they made a couple changes around the write ahead log and how they can hook into it in some different application hooks. So really it's not a pure SQLite approach. And that's fine. It depends on what's important. I like to reduce the amount of friction people go through. If you already have SQLite running, you don't have to install different libraries or do any tweaks around that. You just plug in this thing and it should hopefully work.
But again, it's a different approach. I think there's definitely benefits. It's a managed service. We can't really do a managed service with LiteFS because they're really running in the same process more or less as the application itself. So a managed service doesn't make a huge amount of sense, whereas with them, they can connect up, they do the management of the actual servers themselves. So there's some ease of use and ease of maintenance that they get out of that.
Eric Anderson:
Yeah, I think you're close to clarifying. You answered questions that I didn't realize I had with that. So LiteFS is not a managed service because you end up just being SQLite files on local disk.
Ben Johnson:
It's essentially like a file system that lives on the same node as your application or on the same server.
Eric Anderson:
So I have an app server and instead of having my database over the network elsewhere, there's just files on my app server.
Ben Johnson:
Files on your app server. And then it has a little staging area where you can hold almost like diffs of your... Every time you do a transaction, you're getting this transactional file and that can get shipped out to the other replicas, but sometimes other replicas can lag behind. They can get disconnected. So when they come back up, you want to have some most recent set of those transactions. So there's that staging area for those as well, if that makes sense. Maybe that's getting too far in the weeds for database replication.
Eric Anderson:
Yeah, that's fine. In the edge world, there's some of these serverless app servers where they just get spun up. I'm speaking the Fly language a little bit, but poorly. Quickly process your requests and then get spun down. I believe that's how it works to some degree. So in there, my database then is just active for a moment. The files are there on that same little execution node?
Ben Johnson:
Yeah, files are there. Right now LiteFS works where it needs to have at least the primary nodes, those running all the time. They don't do well for auto stopping, which you can have for replicas elsewhere where they can come up and they can pull in the latest data and stay up for a little bit and then shut down when they're not used.
And we started doing some work on what's called a virtual file system, which is a concept inside SQLite where they actually abstract out the file system because they run on Windows and they run on Unix, and there's a layer in there. We've actually built a version that works with our... We have a managed service for just disaster recovery stuff with LiteFS called LiteFS cloud. It'll actually work with that and it'll pull down pages and it's transactionally aware within there. So you can run it on something like [inaudible 00:22:49], but it's still alpha beta.
Eric Anderson:
So I paused this on the SQLite open source libSQL, but maybe just SQLite is apparently a unique project in terms of outside contributions.
Ben Johnson:
They don't allow it.
Eric Anderson:
What are your thoughts on that as someone who probably considered... There was probably a point where you're like, "If I could just make a contribution, this would be a lot easier."
Ben Johnson:
I can see where they're coming from. I disallowed contributions. I've done that on a lot of projects personally. I think my contribution policy got on Hacker News one time. And I was expecting people just to dump on it, but everyone was like, "Oh, I can understand that."
Eric Anderson:
Makes sense.
Ben Johnson:
It's just draining after a while sometimes. Especially with the projects I do, I really like having an idea of what the end goal is. With Bolt, I get a lot of shit for, I essentially called it done at one point where I wasn't going to make any more feature changes. It was this is it and we'll do bug fixes and whatnot. But I really wanted a simple project where you don't have to worry about constantly learning what's being added or different things.
And those features coming in might bring in bugs for other things. I wanted a staple and I had an idea of what the end goal was. So when you have outside contributions, people always want to add more stuff. No one's coming in trying to add more test cases or something. It's always some really big feature that like, "Oh, I think this web server belongs in your key value store," or something like... It's maybe not that crazy, but trying to constrain the vision while accepting outside contributions is tough.
Eric Anderson:
You had this contribution policy that was basically like, "Don't try," and it was on Hacker news.
Ben Johnson:
Yeah, it was like F off. And I think people understand for the most part. Some people get a little grumpy about it, but by and large, I think people understand and appreciate that.
Eric Anderson:
So libSQL, is that... You answered this earlier, that since you've been able to make Litestream work with SQLite, then it's nice to be able to not have to switch libraries.
Ben Johnson:
There are certainly things I'd like to probably tweak in SQLite and I put that up there to see if they'd be open to changing those things. But I think by and large, I can think it works well. I like the actual, been dictator for life model of open source. It tends to be like one person or two people are the main contributors to most open source projects and just they have a sense of where the thing is going or where it should go and what are the constraints, other projects in their head.
And trying to open that up to just everyone. I think there's pros and cons for sure. I just think it's a lot to put on people, especially for projects where you're not necessarily making money off of it or it's not your full-time job. It's just like this fun thing you like to work on, and then you just keep having people come in and trying to change it around. So it's more power to them if they're opening it up by all means. It's more of a mental health thing I think, for me.
Eric Anderson:
So what's the future for databases? I think a lot of people are building apps that are more or less the same and they reach for the database they're familiar with. Might they benefit from just starting with LiteFS? Is that the right use case for IFS? Just most new crud projects.
Ben Johnson:
Honestly, I would even step back a little bit. I think you can just start with SQLite honestly, you don't have to use my stuff at first. I think if you start growing and you realize, "Oh, I want to have continuous backups..." On our Litestream page, we actually have alternatives like, "Hey, here's how you can just set up hourly chron backups. You don't even have to use Litestream."
Eric Anderson:
Before you go on further. So I should just have SQLite server side sitting on my app server as we talked about. And then once I'm like, "Oh, I feel a little uncomfortable, I should probably have backups." Then I can Litestream it to get backups and then... Sorry, pick up where you were.
Ben Johnson:
Oh yeah, Litestream it as you say. I haven't used it as a verb, but that's good. But you can continuously back up your data up to S3 and it has some nice protections around it. And then if you continue to grow or maybe you have users in a different side of the world and you're like, "Hey, this is slow over here, it would be great if I could just have a replica and replicate my data."
You can use LiteFS. I think at each stage I really wanted to make it, you don't have to jump in and commit to this new project from day one. It's really more like an evolution of like, you have SQLite, "Oh, here, you can tack this thing on when you're ready for it," and then you can tack this thing on and when you're ready for it. And just slowly move towards that end goal.
Eric Anderson:
Another enthusiasm for SQLite is around Local First, and I want a SQLite instance on my device, and then maybe it can talk to a SQLite in the cloud. And now I've got the benefits of Local First and a service side database. Is that a Ben Johnson use case?
Ben Johnson:
No, it's not. So there's two separate worlds. So LiteFS and Litestream both do what are called physical replication. It basically copies the exact pages, like the bytes in the pages across the network to somewhere else, and they can recreate your original databases through change sets. And if you have the Local First stuff. So CR SQLite is one that's pretty popular where it's actually CRDTs over SQLite, CRDT is conflict... Or no, I always-
Eric Anderson:
Resolution.
Ben Johnson:
Yeah, data type something. Anyway-
Eric Anderson:
I think so.
Ben Johnson:
It's the worst marketing name in history, but it's a way to have people changing the data in two different locations and have it sync and you can figure out how they merge together. So that's really a separate world of SQLite. I think SQLite is probably the most popular as far as I know. I know there's another one called [inaudible 00:28:22].
I don't know if they're still doing it. But yeah, so there's been different approaches to that. And I think Local First makes sense for certain projects, but I think there's a lot of mental... You really need to understand how conflicts work and how all that stuff works and whether it's actually beneficial for your use case. I think there's a lot of overhead to it.
Eric Anderson:
Yeah, it feels like for most use cases, most applications, it's a nice to have feature and the implementation is quite a lift.
Ben Johnson:
Yes. I would agree with that.
Eric Anderson:
And most people are like, "You know what? Maybe I don't need to have that actually." But for some apps that's a critical thing and they make it work. I don't know.
Ben Johnson:
And again, more power to them. Eventually consistent stuff generally, most of the time it's just hard to do. Early's hard to do well. You get a lot of edge cases and weird bugs. That's my warning to people. Anything eventually consistent.
Eric Anderson:
I think we've covered most of what I wanted to cover. Ben, anything that you feel like could be interesting that we haven't covered?
Ben Johnson:
I think there's a lot still around SQLite usability on the service side, which I think can be improved. I love to see people work on that. I started a little hack of... I saw somebody else make a hack of this, and I made my own hack of this, of connecting to SQLite over SSH, so there's no server running on the server node.
But when you connect over SSH, you can run a program over there on the other side and then communicate over standard and standard out with it. So it's essentially connecting out to your SQLite program on your server and then doing queries against that. But again, there's no official API to doing that within SQLite, like the SQLite's CLI. So I'd love to see more around that.
Eric Anderson:
You're saying there's an admin interface, like I can communicate with my database through the app server, but if I want to configure it or do some other admin type things.
Ben Johnson:
Or if you just want to do some backend queries, I think that'd be great to see. But yeah, I think there's mainly usability honestly is, I think the biggest hurdle. I use a lot of CLI tools, so I don't mind SSH being into another node, but some people dislike that, so I think that can be a hurdle for sure. So if there's more gooey, more usable interfaces for more people, I think that would help a lot.
Eric Anderson:
What is the community like around server site SQLite? Do you find that there's a bunch of people who are kind of, "I've moved on from my SQL and Postgres. I just do, just as we described, I start with my server light SQLite and then I start doing backups, and then I do this conflict resolution," or whatever it is, and they've moved on. They don't need Postgres or MySQL anymore?
Ben Johnson:
I don't think they're mutually exclusive by any means. And honestly, when you look at companies or businesses, you have your application like the main one, and then you have 20 or 30 of these side ancillary applications of things. This thing runs and does whatever, has this little UI on the side. It's not part of your main application that has to be up and running all the time. And those can tolerate, if you have to restart the server, you don't mind people not connecting to it for a second or two. So I think there's a lot of different use cases out there.
And I think SQLite can be great in a lot of different ones, but I think as far as starting out, I think a lot of people feel more comfortable using SQLite. And honestly, for a long time it was just like the toy database that people made fun of you for if you used SQLite. But honestly, it does so much in there. You can do JSON processing, you can do full text search, you do all kinds of stuff that's just built in. So I would say it does 90% of what you typically need Postgres for, and then that extra 10%, a lot of times you can just get from your local application language.
Eric Anderson:
And you're pointing out that in a world of microservices for example, you might have a bunch of small servers who do little jobs and they need a little persistent storage, and so you slap some SQLite on them and you're good to go.
Ben Johnson:
Pretty much. As long as people feel like they can use it and if it works for the use case, I think that's great.
Eric Anderson:
Is there a place where SQLiter's gather? When you say it would be great if somebody did this SSH thing, who talks about that and where do they talk about it?
Ben Johnson:
I would say Twitter used to be a place. It's fluctuated recently I feel like with the change over to X and all that. I think whenever there's a SQLite post on Hacker News, people gather there and people love SQLite, honestly. And then there is a SQLite Reddit, it's not super active. There's a SQLite forum that the SQLite folks run themselves, but I wouldn't say it's a lot of service side SQLite people. So it's a mix. I think Twitter was the main place for a long time.
Eric Anderson:
And Ben, what's the future hold for you? More SQLite or what's scratching? What's the current curiosity that could lead to the next decade of interest for you?
Ben Johnson:
Oh man. I don't don't know about the next decade, but I think just making application development simpler I think is kind what I like to focus on. And there's a lot of work that goes into just small changes a lot of times. So I think it's just getting through that and making it...
At the end of the day, I would love it if LiteFS was really just a little checkbox you click and suddenly your data is replicated and there's nothing else you have to know about it. And making those defaults of configuration or whatever, things just more natural and easy to use goes a long way. So yeah, I think just making it an easier tool to use. Really.
Eric Anderson:
Super. Well, Ben, I'm excited that you got curious about this years ago and then all you've given to the community and appreciate your time today.
Ben Johnson:
Cool. Yeah, thanks for having me on, Eric. Appreciate it.
Eric Anderson:
You can subscribe to the podcast and check out our community Slack and Newsletter at contributor.fyi. If you like the show, please leave a rating and review on Apple Podcasts, Spotify, or wherever you get your podcasts. Until next time, I'm Eric Anderson and this has been Contributor.