Posts with tag sqlite

sqlite.directory updates

After the initial launch of sqlite.directory, I have been working on a few updates to the site. Here are some of the updates:

• Show count of apps (59ba725)
• Show favicon of app (d89dfbe)
• Randomize the order of app entries (d703bf9)
• Validate that even the optional Repository URL is a valid URL (15ad911
• Make the application more responsive (07a5a3d, ea01025, e370f9d, daed3ab, 5568fdd, 93c5c43, and 3b8cf5e)
• Update the PWA manifest to improve colors, description, and quick actions (31422f6)

As of writing this (December 11, 2024 at 1:30 PM CET) there are 17 applications listed on the site. I am excited to see the site grow and to see more applications listed on the site. If you have a project that uses SQLite, please consider adding it to the site. You can do so by visiting sqlite.directory and clicking the “Add entry” button. I am looking forward to seeing your project listed on the site!

Loading extensions

Rails continues to expand its lead as the single best platform for building web applications backed by SQLite! You can now load extensions directly from the database.yml file. This now means you have quick and easy access to the full range of SQLite extensions. This is a major step forward from the previous way, which required writing a custom initializer or using the enhanced adapter.

Introducing sqlite.directory

sqlite.directory is a directory of web applications that use the SQLite database engine in some meaningful capacity in production. I have just launched the initial release today. Check it out and list your SQLite-backed app today: https://sqlite.directory

Supercharge the One Person Framework with SQLite: Rails World 2024

Problems with a backoff busy handler

In the process of getting the non-blocking busy_timeout from the sqlite3 gem used in Rails, I needed to explore the differences between a busy handler implementation that uses a backoff and one that simply polls on a consistent interval. What I found was that a busy handler that backs off kills “tail latency” (how long it takes for the operation in the worst cases). This is because the SQLite “retry queue” behaves very differently from a more traditional queue that we tend to work with and think about, like a background job queue.

The how and why of optimal performance

Isolated connection pools

If you want to squeeze as much performance out of your SQLite on Rails application, at some point you will need to confront the problem of writes saturating your connection pool. Let’s dig into this problem and how to solve it with some clever usage of Rails’ multi-database support.

Non-blocking timeout

Last month I released version 0.4.0 of the activerecord-enhancedsqlite3-adapter gem, which added support for a non-blocking busy_timeout to the SQLite adapter in Rails. As with all of my lower-level SQLite work, the goal is always to push these solutions into the foundations of the ecosystem so that everyone can benefit from them. In this case, I’m happy that the busy_timeout feature has made its way into the sqlite3 gem, which is the most popular SQLite gem for Ruby.

SQLite Quick Tip: Multiple Databases

When building a full-featured Rails application, you will want not just a database to store your model data; you will want a job queue backend, a cache backend, maybe even a pubsub backend. When building a Rails app leveraging the power and simplicity of SQLite, you will likely wonder how best to architect these various services? 37signals has been releasing new gems to use solid-state storage via relational databases. They default to using one database to back all of these services, but they use MySQL. How should we handle this with SQLite?

Installing extensions

In a previous post, we discussed how to load SQLite extensions distributed as Ruby gems into a Ruby on Rails application. Unfortunately, there aren’t many SQLite extensions that are distributed as Ruby gems. So, in this post, we dig into how to install a wider range of SQLite extensions into our apps.