Get the latest tech news

Shardines: SQLite3 Database-per-Tenant with ActiveRecord

There is a pattern I am very fond of - “one database per tenant” in web applications with multiple, isolated users. Recently, I needed to fix an application I had for a long time where this database-per-tenant multitenancy utterly broke down, because I was doing connection management wrong. Which begat the question: how do you even approach doing it right? And it turns out I was not alone in this. The most popular gem for multitenancy - Apartment - which I have even used in my failed startup back in the day - has the issue too. The culprit of does not handle multithreading very well is actually deeper. Way deeper. Doing runtime-defined multiple databases with Rails has only recently become less haphazard, and there are no tools either via gems or built-in that facilitate these flows. It has also accrued a ton of complexity, and also changes with every major Rails revision. TL;DR If you need to do database-per-tenant multitenancy with Rails or ActiveRecord right now - grab the middleware from this gist and move on. If you are curious about the genesis of this solution, strap in - we are going on a tour of a sizeable problem, and of an API of stature - the ActiveRecord connection management. Read on and join me on the ride! Many thanks to Kir Shatrov and Stephen Margheim for their help in this.

When the file gets read Whether (and when) templating is done in it, for example - to inject credentials That all internal datastructures - such as connection pools - get initialised ahead of time Preconfigured, large clusters where data is usually sharded - using things like shop_id- but not segregated. By implementing these optimizations, I’ve seen remarkable performance improvements: single MySQL server handling 2M+ ticket reservation transactions per minute while the average latency of SELECT … FOR UPDATE SKIP LOCKED query staying under 400μS. So one of the reasons why the modern multi-DB features in Rails do not support dynamic tenant management with automatically allocated databases - in large numbers - is because, at least on the surface, only SQLite3 currently makes this pattern viable.

Get the Android app