Episode 33 - Stephen Margheim

Embark on a journey through the landscape of SQLite and Ruby on Rails as I chat with the exceptional Stephen Margheim, who enlightens us on the robustness and agility of SQLite for modern web development. Stephen brings to the table his extensive open-source contributions and projects that integrate SQLite with Rails, offering insights into Ruby gems like Lightstream for backups and an SQLite package manager for extensions. Dispel any doubts about SQLite's scalability with us, as Stephen dismantles misconceptions and highlights how this database can stand tall among the giants.

As we navigate the conversation, you'll uncover the elegance of simplicity in database management. I reflect on my own experiences, contrasting the complexity of orchestrating large-scale applications against the serenity of smaller, SQLite-backed projects. Stephen and I advocate for the beauty of monolithic repositories, debunk the notion that overengineering is a must, and sing praises for starting lean and nimble—because sometimes, less is genuinely more. This episode is a chorus for pragmatism, where we harmonize the hymn that simplicity and development velocity can and should be the leading voices early on.

Finally, witness the burgeoning Ruby ecosystem's embrace of SQLite through the spotlight on emerging gems like Lightstack and Extralight, which are fine-tuning the Rails experience for both seasoned developers and newcomers. This episode isn't merely a discussion; it's a testament to the growing vitality of Ruby's relationship with SQLite and the shared vision of streamlined, powerful web development. Join us to learn how these tools can transform your Rails journey, as we champion a future where sophistication and accessibility go hand in hand.

Judoscale
Autoscaling that actually works. Take control of your cloud hosting.

Honeybadger
Honeybadger is an application health monitoring tool built by developers for developers.

Disclaimer: This post contains affiliate links. If you make a purchase, I may receive a commission at no extra cost to you.

Transcript

Speaker 1 0:01

Hello everyone and welcome to another episode of Code and the Coding Coders who Code it. I am your host, drew Bragg, and I'm joined today by Stephen Markham. Stephen, for those of the listeners who don't know you, please introduce yourself.

Speaker 2 0:14

Yeah, thank you. My name is Stephen. I go by FractaledMind on Twitter and I am an engineering manager at Testio here in Berlin, germany, but I spend a lot of my free time working in the open source ecosystem, particularly with SQLite and Rails.

Speaker 1 0:35

It's going to work. I'm going to ask Stephen three questions. I'm going to ask him what he's working on, what kind of blockers he has. If he doesn't have a recent blocker, we can talk about a blocker he had and how he went about solving it. And then we're also going to go into my favorite question what's something cool, new or interesting that you've recently learned, discovered, worked on? I have a feeling I know what the topic of that is going to be, but we'll see when we get there.

Speaker 2 1:07

Stephen, what are you working on? Yeah, right now I'm bouncing around between six different open source repositories trying to bring this wild vision I have to life that Rails can be the best web framework in the world for building SQLite applications. So I've got a couple of open pull requests in the Rails repository right now. I have a couple of open pull requests on the SQLite 3 Ruby repository and then I have been building a few gems myself, specifically the Lightstream Ruby gem, which provides a Ruby adapter for the Lightstream utility, which provides a really excellent solution to the problem of if I have a SQLite database, how do I do backups, because I don't want one machine to go down and I lose all of my data. So it makes it easy to install that utility bundle. Add Lightstream, the executable gets there. Then it adds a Rails engine as well to make it easy to start that process up and make sure that you are backing your database up, and got a couple of issues and pull requests from community members open on that repository.

Speaker 2 2:19

Similarly, there is a utility it's called the unofficial SQLite package manager. There is a utility it's called the unofficial SQLite package manager. So there are a lot of extensions for SQLite, which is really useful when you have really specific functionality that the engine itself doesn't provide. You actually have a fair chance SQLite's 30 years old. It's had extension support for decades of finding an extension that does provide that for you.

Speaker 2 2:46

And the SQL package manager provides, like a homebrew style CLI interface for installing those and I wrapped that up in Ruby so you can also just bundle, add SQL package and get that into your project and then finally, like big one the big one I say that, but just the one that's closest to my heart but has the ugliest name is the ActiveRecord Enhanced SQLite 3 Adapter, which is a gem I put together to ensure that any Rails application after 7.0 can take advantage of the new features that are coming into Rails to make working with SQLite databases as resilient and performant as possible, without, a having to necessarily upgrade your application to a newer version or, b having to wait until a newer version is released which includes these changes and looking forward to talk about some of the newer features there. But yeah, I find myself in my professionally undiagnosed ADHD brain bouncing between these six repositories every day.

Speaker 1 3:52

That's a lot of repositories to keep context on. I have trouble with way less than that. It's interesting that you talk about using Ruby on Rails to be the best way to build SQLite applications, because, even though, unless it's changed, I believe SQLite is still the default for a new Rails application. I feel like the first thing they do in every tutorial, in every bootcamp, everywhere is oh, we're going to use Postgres. Occasionally, you'll get someone who's like, no, we're going to use MySQL, but everyone's like, no, you use Postgres. No one uses SQLite. I'm curious to see what you think is like. Why do you think people do that versus going? Sqlite is actually pretty awesome, especially for toy applications and even like it can scale. But why do you think there's this notion of we always default to Postgres. We don't use SQLite?

Speaker 2 4:41

Yeah, I think that it's grounded in a reality. That was much more true a decade ago and inertia has just carried it forward. So there are a few details that are, I think, important for people to understand. The first one is that SQLite the project, is famous for its maniacal focus on testing. It is one of the most well-tested pieces of software in the entire world. Less well-known is the fact that they are equally maniacal about backwards compatibility. Okay, and since SQLite 3.0.0 came out, which is at this point almost two decades ago, every new feature that they add is off by default, right, Because if they change the default, it's not backwards compatible to 3.0.0. And so what has happened over the last 20 years is that the library, the engine, has gotten much more powerful and had a lot of features added that make it genuinely viable for web applications, but zero of those features are enabled by default. So if you just run, install SQLite, attach it to your Rails application and Rails has done no configuration, you've done no configuration and you put that on the internet, you will experience some pain points, and 20 years ago that was true and I think people just sort of took it and well, okay, don't use that database, We'll use something else.

Speaker 2 6:14

I think the other big thing and DHH has talked about that in his last Railsworld keynote the fact that solid state storage has become completely commonplace and NVMe drives are commonplace. It is easy for us to not take the time as web developers to reassess our priors and say, oh, is it so awful to have a database that is a file on disk? Do I have to have a full to have a database that is a file on disk? Do I have to have a full server process, Postgres installation to have quote unquote, scale and performance? And then the final factor is it is just a fact that SQLite only allows for linear writes.

Speaker 2 7:01

So if you have your web application, linear rights, so if you have your web application and basically every web application these days has support for handling concurrent requests, it's easy enough, I guess, to imagine that if your database doesn't support concurrent rights, then quote unquote, it can't scale. And if it can't scale it must be a bad idea. And if it's a bad idea you should never start with it. What I think more and more people are starting to understand and appreciate is that when you remove entirely network latency and your queries go from being measured in milliseconds to microseconds, you just immediately mathematically have the ability to pack a hundred thousand queries in the same amount of time. So even if you have to linearly stack them up, you have all of this room that is available to you.

Speaker 2 7:56

And people, I think, have also just only recently started to explore and appreciate the tools that SQLite makes available for allowing for really saturating that span of time and allowing for concurrent requests to line up all of their operations and get them executed as quickly as possible. And in the real world, you really have to be experiencing a large amount of traffic before you would feel like, oh, it's just actually physically impossible for me to use SQLite for this. But this sense well, oh, they say it does linear writes that can't scale, so I shouldn't do that.

Speaker 1 8:44

I think you take those three things together and that's my sense of the core of the conversation around why people have thought, oh, this isn't production grade software Right, cause everyone thinks they're going to release day one at Shopify scale, so naturally you need to be able to handle billions of rights per second and all of that. I can understand that. I think that's been one of the arguments against Ruby on Rails for a while has been like, oh, it's not fast enough and it's plenty fast, it's more than fast enough. That's not your problem. Get to that scale that anything with the Ruby runtime becomes a problem, and that's a good problem to have. But get out the door first.

Speaker 1 9:26

So you said you've been working pretty hard on making it. Ruby on Rails is the best way to build SQLite applications, so you had to make this extra gem, the enhanced adapter, and I take it that you had to make a enhanced adapter because the default built-in one isn't good enough or there was something missing that the core team didn't want to support. Why did you have to make a separate gem to get the kind of SQLite support you were looking for in Rails?

Speaker 2 9:52

Yeah, well, to this point the core team has been very understanding and supportive of the different problems that exist in the Rails SQLite Active Record Adapter. The real stumbling block has been the fact that Rails has been increasing but still a slow release cadence right. So you get a pull request merge. It might not be in a released version of Rails for three months, six months, months, six months, and even when it is, how many more months? Until 30%, 50% of the Rails applications that exist in the world are on that version.

Speaker 2 10:33

So the vision behind the enhanced adapter is sort of twofold.

Speaker 2 10:39

On the one hand, it is a sandbox to experiment with features that I believe are going to make a meaningful improvement to Rails applications and to get that out into the ecosystem, into the community, get feedback on them, really make them robust and resilient and then say, okay, now I'm really confident that we can't upstream this into Rails.

Speaker 2 11:05

And then, on the backside of that, the gem ensures that, even as something has been upstreamed into Rails, you don't have to be on the bleeding edge to get that feature.

Speaker 2 11:16

You can just bundle, add, bring the gem in and you're going to be there. Even right now, if you're on Rails main, there are pull requests that are still open, still going through review, that make meaningful improvements that aren't in main yet, but to this point the Rails core team has been very receptive to all of the enhancements and adaptations to the SQLite 3 adapter, enhancements and adaptations to the SQLite 3 adapter. So the goal, like at a high level, is to get all of the essential stuff nailed down into Rails 8, make Rails 8 just objectively the best web framework for running SQLite applications. But then the gym will backport all that functionality all the way back to Rails 7.0 and continue to be the sandbox playground for figuring out what kinds of features might possibly make sense to put into Rails core to really ensure that Rails applications are as resilient, robust and performant as possible when running SQLite in production it robust and performant as possible when running SQLite in production.

Speaker 1 12:28

I think that's a very interesting and cool way of doing it, where you have a separate gem to kind of verify the ideas and make sure everyone has a even footing, because, you're right, it's a relatively slow cadence and you don't want people to be like, oh well, once we're on the next version of Rails, maybe we can use this feature. It's like cool, we have a gem that allows for that, and then someday maybe we won use this feature. It's like cool, we have a gem that allows for that, and then someday maybe we won't need the gem. Rails will just take care of all of it for us. But that's the beauty of having a mature and robust ecosystem of libraries is we don't have to wait. What makes SQLite a really good choice for applications in general or a certain type of application? When you think of a SQLite application, is there a this type of application is perfect for SQLite, or it's like SQLite's great, you can use it for anything? Is it pretty general or specific, or somewhere in between it's pretty general.

Simplicity vs Complexity in Database Management

Speaker 2 13:20

My sort of spicy take version of this is that you can have an application that you know on day one right, like I'm going for the moonshot, and you should just get a PlanetScale subscription and that's the kind of application you're building. You want to reach PlanetScale. You want to have 1 billion users. Don't run your own Postgres server. Don't mess around with Heroku Postgres. You know where you're headed. Get there with Heroku Postgres. You know where you're headed, get there. Or you are the 80, 90, 95% of Rails applications which are business, valuable CRUD web applications and it's mathematically impossible for you to ever have a billion users. Probably, if you saturate your entire market, you would be lucky to have a million total users in your database, let alone 100,000 daily active users or something right. And SQLite makes a really excellent starting place. I see SQLite as a really powerful companion to the concept of Rails. As the one-person framework, it allows you to have the 12-factor application from the old Heroku days, just naturally and easily.

Speaker 2 14:40

Your repository is your application which is going on your server. You can have a one-to-one-to-one mapping. You want to configure your database? Boom, itto-one-to-one mapping. You want to configure your database? Boom, it's all in your database, yaml. You want to run background jobs, spin up another database file. You want caching, spin up another database file. So everything is co-located. Everything is in the repository. Everything is easy to deploy. It's naturally monolithic repository. Everything is easy to deploy. It's naturally monolithic. The monolith can go onto a single server. You vertically scale that server as your demand increases. This simplicity is remarkable If you've experienced both, like for me at my day job.

Speaker 2 15:23

Our primary 15-year-old web application is massive and 15 years old and we are running it on Google Cloud Platform with Kubernetes and Terraform and Docker Right, and there are real technical benefits to that. But the complexity I feel it every time I have to interact with it, interact with it, and then I am maintaining some of our other quote unquote smaller applications which are SQLite applications deployed on a Hetzner server through Hatchbox. I mean, I'm breezing through, adding features and fixing bugs and I think it's so easy for us as developers to get sort of mesmerized by this idea that for us to do our jobs well, we have to make the right technical decision. And the right technical decision must mean the decision that is correct today and will be correct tomorrow and would be correct a year from now and would be correct five years from now, and there's a sense that it has to be perfectly elastic, and so if I have a database, I need to be able to scale to zero and scale to infinity. That's clearly the technically perfect solution and I want to do my job well, so I should do that.

Speaker 2 16:44

The massive amounts of complexity that come along with that kind of elasticity and flexibility, and especially with something like Rails, where I'm certain that it is rare but it is at least possible to change your database over the course of the lifecycle of your application, and taking advantage of the velocity that can come from a radical quote unquote I don't think it's that radical, but some people would think of it as radical a radical appreciation for simplicity and really trying to leverage that power of Rails as a one-person framework, even as you're building a business and building a team and pushing that as far as you can go, and then if you reach those boundaries, then, as you said, that's a wonderful problem to have and you have the capital to address it.

Speaker 1 17:33

I hope so why?

Speaker 2 17:34

spend that capital on day one Right and be like, well, I should build my application right and so I'll get my load balancer and I'll make sure I have my three application instances and I'll have my Postgres database, but I need to have a read replica. I should probably have two read replicas and I'll make sure I have Redis, but I should have two different Redises with different eviction policies for my cache and for my background jobs, and each Redis obviously also needs to have a read replica. And I'll put a bounce in front of that. And I guess technically I just now decided I need 10 servers.

Speaker 1 18:00

Well, what an interesting complex topology For my hundred users. Yeah, yeah.

SQLite in Rails Simplified

Speaker 2 18:04

It's like come on, you don't need that, you objectively don't need that, yep. So, embracing simplicity at the start of things, embracing velocity at the start of things, sqlite and Rails together, I think, make a really powerful combination for a high leverage solution that allows you to focus on your business value.

Speaker 1 19:08

And so in Rails, as that kind of keeping everything in a somewhat more monolithic way or keeping your external dependencies low, like we've got this new, we've got solid cache and we've got solid queue, keeping it in the database instead of needing your Redis instance for these things, how does SQLite play with those kinds of things Play?

Speaker 2 19:29

with those kinds of things. Yeah, it takes a little bit of mindset shift but it plays incredibly well, right? So 37signals, they've got this massive MySQL mesh. They've got a whole bunch of MySQL servers, but they talk to it as if it's one and have all of the infrastructure complexities to support that. And in that setup, right, everything is running on that same logical single database, that mesh In SQLite. There's no point to try to have a single logical database. Just split out the files right now in production in one of our applications and it has been going really well, and I literally just added a section to my database YAML New database.

Speaker 2 20:18

Rails has full support for multiple databases. It's so easy. It's a migration path, q. Call this one Q migrations, and the name of this database will be Q. And I've just got this file, qsqlite3, that lives right alongside my productionsqlite3. And SolidQ, solidcache have built-in support for Rails' multiple databases, so you just configure the connects to configuration, say, yeah, it talks to my Q database and it works incredibly well, talks to my Q database and it works incredibly well.

Speaker 2 20:56

And actually that mindset shift is what unlocked for me the real potential for this, and I actually just recently released a new gem which isn't like squarely in the Rails and SQLite ecosystem, but directly came out of this exact realization. It's called solid errors Again, very creative naming. I'm a poet of sorts, but it is a built in very, very simple error monitoring service and the way that I use it in my application is that exact same way. I created an errors database and I connect the gem to that database. So I have my production database, my queue database, my errors database, my cache database. When Rails ships their generic, their solid cable gem, I'll have my cable database. Right, I'm just going to have 10 of these SQLite databases hanging out on the file system Again.

Speaker 2 21:48

Rails makes this so easy. It already has done all of this work for multiple database support.

Speaker 1 21:53

Not really, I imagine thinking about this use case at all, and yet it fully supports it and makes it very, very straightforward and easy stumble upon that not too long ago and dropped it in our dev links chat at work, just because I do think it is a really cool idea. It's not a replacement for honey badger, it's not a replacement for app signal or what have you especially when you need those more robust features and to be able to do more, but for a very simple application or application that's getting right off the ground, to be able to do that in your app, it's like why didn't we have this from the get-go? It's one of those. Oh, this makes so much sense now that it exists and I appreciate the simplicity of it because of that.

Speaker 2 22:40

Or just to say that was exactly my thinking was that it's in no way meant to be I don't even conceive of it to be, I don't want it to ever be a competitor to a full third-party service. But instead, how can we make it such that the default Rails new standard experience is like oh yeah, I'm going to do error monitoring on day one and to bring as many of these features just a little sneak peek behind the curtain here. I'm working on another gem right now solid metrics same basic idea.

Speaker 1 23:17

Right, it's a very simple idea.

Speaker 2 23:18

It's easy to iterate on it. I'm not very creative, but we have our gifts.

Speaker 1 23:22

Nailing things is hard.

Speaker 2 23:24

Yeah, it is hard, that's why I punt and just borrow other people's names.

Speaker 2 23:28

But yeah, just to make it such that Rails applications from day one can be quite unique to Rails, this unique balance of remarkably simple, so easy to work with, to evolve and maintain over time, but also remarkably powerful, and this solid concept when paired with SQLite and Rails' natural multiple database support, I think makes a really, really powerful combination and I have been taking great advantage of it in my applications at work and I hope to bring as much of that into the Rails ecosystem as possible, because when you can have an application on day one that is still simple, straightforward, follows the Rails way, truly monolithic all of these positives we were talking about but also has monitoring, has metrics, has insights we're taking advantage of all these things that Rails gives us, but making it available to developers and getting everyone thinking about oh yeah, this kind of functionality is really valuable for an application.

Speaker 2 24:34

And then, as that application grows and evolves and you find users and you find revenue, you're like okay, now it's time to graduate to a honey badger, now it's time to graduate to an app signal. But you don't go from well, I've never considered this at all or have any functionality in my application to well. I guess, now that I'm'm making some money, I should know what errors happen. Yeah, it's kind of ridiculous right?

Speaker 1 24:56

yeah, it's interesting too because there is this I don't even know necessarily that it's a trend. It's almost like we're going backwards or returning to our web roots in a way where, like the whole concept of mastodon right is like you run your own server and it can talk to other servers and everything, but like people run their own instance. Campfire is a sort of example of that. You take this thing you potentially didn't build, but you can expand it because you have the source code. But with open source applications it's fairly common. Here is the source code for this thing. Devto is another good example. With their form, you can make your own instance of this and you just deploy it here.

Speaker 1 25:36

But for someone who's doing it on a very small scale, it's like they don't want to pay for a Honeybadger or an AppSignal, they don't want to pay for a Redis instance or anything like that, the fact that you can just take a repository and be like, hey, everything's in here, You're just going to dump it on this server and if you have an error you'll be able to at least see it. And if you get to the scale where you have enough users that you're making money off of your instance, and you need the more benefits of something like a Honey Badger cool, you can go and just swap out for that a lot easier. But everything is so contained and I feel like that is so like an old school way of thinking and it just makes me question why did we ever get away from that? That actually sounds awesome. Why do we keep on doing this? Hey, let's make it stupid complex to get started with all of these things that you don't need for the first year, two, three years, depending on how fast you ramp up. You definitely don't need it for your first hundred users. You might not need it for your first thousand.

Improving SQLite Adoption in Rails

Speaker 1 26:35

It's like you're killing so many great ideas before they get started by just being like artificial complexity. Go job security, because you know exactly the cloud architects need more jobs or something. It's a really cool feeling where it's a really cool thing to see and I think something like a solid errors is like. It seems like such a small piece. It's such as oh yeah, we can just track our own errors in our own application, but at the same time, it just adds into this big puzzle of we can actually host apps really easily. We can build apps really easily. We can build distributed systems really easily, or at least that's my take on it. I don't know.

Speaker 2 28:07

Strong agree. Strong agree.

Speaker 1 28:11

Might be a spicy take, but I guess the next question I have leads us into the next question of blockers. You're doing a lot of work. You've got six different repositories that you're working on. You've got your enhanced gem. You're working on upstreaming that into Rails, and we all appreciate you for that. You're working on upstreaming that into Rails, and we all appreciate you for that. But what kind of blockers do you see? On getting SQLite to be that very mainstream, people aren't afraid to use it or aren't wary of it. They don't immediately default to changing their database to Postgres or they don't feel the need to do that. What do you think needs to happen to unblock that?

Speaker 2 28:47

Yeah, I think that there are two main areas and they have been areas of focus recently for me. So one is backups and the other is concurrency. So the Lightstream gem bundling up the Lightstream utility, which is a wonderful, wonderful piece of software by Ben Johnson at Flyio. It streams replication logs of your database to S3 compatible buckets so it can be S3, it can be digital ocean spaces, whatever Google Cloud calls their thing, and what that gives you is it gives you effectively point in time backups of your SQLite database. And another really nice thing about that utility is it also has natural built-in support for multiple databases.

Speaker 2 29:34

So you are running an application, like I am. You've got four different SQLite databases for these different use cases. You want to ensure that they're all backed up. Very straightforward, and the gem comes with a generator. It gets everything set up for you and intraspects your database YAML file and make sure that all the databases you have listed there, all of your SQLite databases listed there, are going to get backed up. So I think that problem is very nearly solved, so long as people know that it exists. If you're running an app on Rails with SQLite, make sure you run, bundle, add Lightstream. It's going to make your life much, much better. And I say this as someone who has deleted my production SQLite database before right, don't be me.

Speaker 1 30:21

We've all got to drop the production database.

Speaker 2 30:23

You got to do it once. You got to do it once.

Speaker 1 30:25

Only once.

Speaker 2 30:26

Yeah, and then get bundle, add light stream and make this problem go away. The second problem is around concurrency, and this is where I have a number of open pull requests right now. Right, so I really want to make this story completely solved problem by Rails 8. But right now you do need the enhanced adapter. Let me try to give a condensed version of the problem. There are two problems that exist right now. One exists at the Rails layer, one exists at the Ruby layer, so at the Rails layer.

Speaker 2 30:58

Sqlite uniquely has this concept of transaction modes. So because it does linear writes, it has a write lock on the database and the default way, the original way so that hasn't been changed that transactions work is that the operation that begins the transaction just begins the transaction and it says for all I know, this is going to be a read-only transaction, I don't need the write lock, so I won't do anything. If there is an operation inside of that transaction that is a write operation, when that operation is executed then SQLite will say oh, I guess I need the write lock. Let me go and attempt to acquire it If another connection has the write lock currently, because you're inside of a transaction and SQLite takes its isolation guarantees extremely seriously. It says well, I can't safely just retry this write operation and wait for the write lock to come, so I'm just going to error. It'll just immediately throw an error and that will bubble up to your application. So it is important if you are running a transaction in SQLite and you know that it's going to have a write operation, you should tell SQLite to use what it calls an immediate transaction, which means that it will attempt to acquire the write lock in the begin transaction operation and not inside. And because it's at the start of the transaction, if it needs to wait because another connection has it, it can use the retry mechanism that SQLite has and your operations can line up on their own. So there's an open pool request right now into Rails.

Speaker 2 32:38

I've had discussion with the Rails core team to figure out what is the best way to bring this feature into Rails right, because Rails is going to wrap every single write operation that you make in active record in a transaction, so we know that that transaction has a right operation. How expansive or how selective do we make that change? That's one prong. The other prong, which is at the Ruby layer, is that the retry mechanism that SQLite has, it's going to call a callback and by default, the way SQLite works is it has this series of sleeps that it'll do. It'll sleep one millisecond, two, five, 10, so forth and so on up to a fixed timeout amount, and rails defaults you to five seconds, right?

Speaker 2 33:17

Well, sqlite is a C program and the Ruby gem provides the Ruby to C bindings. But the way that that is implemented when that Ruby operation is sent out to say, hey, do a write, and then SQLite attempts to acquire the write lock oh, another connection has the write lock, let me do the retry callback that all happens inside of C land and it's not communicated back to Ruby land. So the Ruby connection holds the GVL, it holds the global vm lock, and so sqlite is using a mechanism to enable concurrency to say hey, I'm going to wait on io, other threads, please do work. I'm just going to be chilling. But ruby doesn't know about that. And so ruby is like I am working, I am working, I am working and all the other threads have to wait. So it nullifies the benefit of this concurrency feature that SQLite provides.

Speaker 2 34:17

So what we have done in the Ruby gem is implement a custom retry mechanism in Ruby which will release the global VM lock and allow other threads to do work while SQLite is waiting for that connection to be able to acquire the right lock.

Exploring Ruby Ecosystem and SQLite Integration

Speaker 2 34:40

You're going to see exceptions in your logs in production if you just run SQLite right now, and we can make those fixes and make them defaults. There is then a third feature that I'm also working on to allow the Ruby gem, when a long running query is running, to say hey, I'm waiting on IO, let another thread have this, so you've acquired the write lock. Or maybe you're just doing a read and it's taking two milliseconds. Don't just sit and wait and do nothing for two milliseconds. Let another thread do work, saturate your CPU as much as possible, and that's just going to be a feature addition. But those are three pieces of work to address the concurrency problem, and I think when you pair a really solid story for backups and replication with a really solid story for concurrency, I think you give solid foundation pun unintended for SQLite on Rails applications.

Speaker 1 35:35

Right, right, no, that's challenging blockers for sure. Concurrency is never easy Fascinating bit of problems and problem solving. That's going into that. Excited to see it come into Rails, though, and be more of proper support and be there kind of in that out-of-box experience where Rails knew you're not also using the database flag anymore because SQLite's so very powerful, but we've got it out of the box as powerful as it can be. So it feels so silly to ask you this last question, because you've talked about all these cool, new and interesting things that you're working on or that you're discovering or that you're utilizing. Like we've already talked about Lightstream, we're talking about the adapters and the things that you're doing. So it feels so silly to ask but it's my favorite question, so I have to ask it Like, what is something cool, new or interesting that you've recently just learned or discovered or built, or what have you that we haven't already?

Speaker 2 36:32

talked about. It's my favorite question too. Long-time listener, first-time caller. Well, thank you.

Speaker 2 36:38

There is a growing ecosystem in the world of Ruby around SQLite and there are two projects in particular that I think are very exciting, that I want to call out. So the first is a project called LightStack by Old Mo at Old Mo on GitHub and on Twitter, Muhammad Ali, and honestly this was the project that gave me that light switch moment. This is possible. I want to explore this. It is a gem which provides SQLite-backed adapters for all of the IO-bound components of Rails. So you want your action cable backend to be SQLite? Lightstack has you. You want your active record database to be SQLite? Got you. You want your active support cache to be SQLite? Got you. You want your job backend to be SQLite? Got you. You want your active support cache to be SQLite? Got you. You want your job backend to be SQLite.

Speaker 2 37:35

All of the Rails components that require some IO backend. It has out-of-the-box SQLite adapters for all of them. So you just do bundle, add light stack, bin Rails, generate light stack, install. You have a full featured Rails application and that's it. No external dependencies. It's going to spin up these four different database files. You can map that to four different Lightstream backups, you're off to the races. So it is a really powerful, simple and fast way to get a new application up and running right. You don't need anything.

Speaker 2 38:10

And it was very inspiring to me and, honestly, a lot of the work that I have done has basically been can we make the default Rails experience this good? This is the vision, this is how applications should start and, specifically, I think that if we can make the out-of-the-box Rails experience that powerful but that simple, I think that it's an important part of revitalizing our newcomer pipeline. I was almost going to say pipe stream. I've got all these SQLite words in my head, but that project very cool, Worth checking out. Lightstack by Old Mo Very cool, worth checking out Lightstack by Old Mo.

Speaker 2 38:56

And then the second one is an alternative SQLite 3 driver for Ruby.

Speaker 2 38:58

So there's the SQLite 3 Ruby gem that is under the SparkleMotion org on GitHub, which is the default and is wonderful and great, and I've really enjoyed working with Mike and Aaron who are the primary maintainers over there.

Speaker 2 39:13

But Sharon Rosner, digital Fabric and Polyphony and many of these gems in the Ruby ecosystem has built an alternative driver at Ruby called Extralight and it is a gem.

Speaker 2 39:33

That starts from the concept of we must ensure that the Ruby interface to SQLite supports concurrency, so it has much fuller story around concurrent support for working with a SQLite database through Ruby, and when you put it under concurrent load you're going to see noticeable speed improvements. And I have been working with a couple of people, including Sharon, on a Discord server to put together an active record adapter for ExtraLite so that you could swap out the SQLite driver in your Rails application and hopefully we can get that done in the next couple of weeks. But if you really want high performance interactions with SQLite, that is a really excellent driver and Toronto's done really great work with that gem. And I'm sure that there are many others that I am not thinking of, but in the world of Ruby and SQLite I would be remiss to not mention those two projects, awesome, awesome additions to everything that we've talked about and everything that we've gone over already.

Speaker 1 40:46

I've heard of both of these things, actually, potentially from either you tweeting about them or someone else retweeting or in a conversation somewhere. But yeah, it's such a fascinating little bit of software development when we start talking about databases and you've got this mental model of oh yeah, you're Postgres or you're MySQL instance and it's like, well, no, actually there's a third option and a really good option, and it's funny I said this before. I think I had Joel Draper on. He was talking about some wild ideas he had for how you could abuse SQLite and I remember way back in the day, like high school, way too long ago, when I first discovered programming, that was sort of my assumption of what a database was was just a file on disk where, yeah, you just had some operation that would write some data to this file and then, when you needed the data, it would just go and retrieve it. I'm like that makes sense to me, like what do you mean? It's this whole thing, it's a whole other thing. And like obviously there's so much more to it than just putting some words in a file and calling it a day.

Speaker 1 41:50

But SQLite makes so much sense to that model of thinking of yeah, why wouldn't our data just be a file on disk that we can access? It makes sense to me. So I'm enjoying seeing it kind of become a topic again. And I say again because you mentioned, sqlite has been around for a good long while. It is not a new hype project or not a new JavaScript framework. It is not a new hype project or not a new JavaScript framework. It is tried and tested and it's gone through its paces. So I'm enjoying seeing it being talked about again and talked about with such passion.

Speaker 2 42:22

Yeah, it's been quite enjoyable for me too, as someone who I think shares a very similar sort of vision for web development and an appreciation of like, the power and the beauty of simplicity and of high leverage tools to see more people, more of the conversation in web development space, not be exclusively focused on like here's this thing that's one day old and right, and this one percent of use cases is absolutely amazing. And let's not think about the other 99%. But people like really come back and be like you know, actually, maybe it isn't crazy to take the world's most widely deployed database, the best tested piece of software in existence that has been used by every company under the sun for 30 years yeah, probably that's good enough for my crud application and be like yes, it doesn't actually have to be as complicated as so many YouTubers or blog posts might make you want to think. And for people who are considering or starting or hoping to break into web development, to like show them a vision of you can do meaningful, economically valuable work with a small number of high leverage tools that you can genuinely understand. Is it easy? It isn't.

Speaker 2 43:43

I've stumbled just over the last six months I have started to learn little bits of C. I have now C code in the SQLite 3 Ruby gem. That's crazy. I'm not a C programmer, I don't know C, but I learned enough to write some. And I've read some of SQLite's internals and it's like do I know C? No, but can I understand what that code is doing? Yes, and this stuff is approachable, it is understandable and presenting this vision to people, that it's not all this crazy extreme complexity, it doesn't have to be. Sometimes you need it right, sometimes you do need it. There is a scale. You don't have to need it and you really can get started and do meaningful work. Build real projects not toy projects, not side projects like real businesses, real applications, and you don't have to take on all of this additional complexity.

Speaker 1 44:34

Yeah, I can wholeheartedly agree with that. I think there's a lot of artificial gatekeeping. Artificial complexity leads to a degree of gatekeeping in technology where it's like, oh, you can't be a full stack developer because you don't know how these 9,000 systems that you're probably not going to touch anyway work. And I think that getting back down to the bare bones and getting it simple enough where a single repository can hold everything you need is such a huge step forward in getting it super approachable to everybody, Because yet, once you're in, the scale is infinite. If you want to work on those insanely top 1% complex problems, they exist and you can go and do that. That's not what 90% of web development is, so we need to stop being like, oh, you don't know that top 1% Terraform, Kubernetes, Docker, chaos, then you're not a real developer. No, get out of here. And this is a huge step in the right direction. So I'm here, for it is what I'm trying to say. This was awesome.

Speaker 1 45:39

Thanks so much for coming on reaching out on Twitter. You reached out at the perfect time. I was taking a social media break, which I highly recommend everyone do at some point, because it was actually wonderful to just decompress a little bit. I got married at the end of December and decided that I would take a break from all social medias. The only thing that I did wrong was I didn't tell anybody I was taking this break from social media. So people were messaging me going, hey, what do you think about this? And it was like a month later. I'm like, oh hey, by the way, I wasn't ignoring you, I just was not logging into social medias. I appreciate you reaching out so that we can get you on talk about all this cool stuff. I appreciate you being flexible with your time being over in Germany and also appreciate everything you're doing in the Rails ecosystem.

Speaker 2 46:21

Ditto and congrats on the marriage. I am actually going to Jamaica at the end of this month to get married myself.

Speaker 1 46:28

Awesome Congratulations. A fun place to get married yeah, probably self. So awesome Congratulations.

Speaker 2 46:35

A fun place to get married yeah, probably a slightly nicer weather than Germany at the end of February, early March. Yes, very low bar, but yes, much nicer.

Speaker 1 46:41

Awesome. Well, good luck with that. And then, after you settle down with all that and we get some sequel light fanciness into rails eight, we'll have you back on to talk about all the new goodies that people can play with. Sounds good, awesome. Thanks so much, and we'll talk to you all next time.