I’m writing an app that manages sensitive data. It might be nice to segregate each customer’s data into a separate Sqlite database.
I’ve done different flavors of multi-tenancy in Postgres, and have configured apps that use a pre-defined group of databases. But never have seen anyone attach, create & destroy Sqlite instances dynamically.
I’m pretty sure everything you need can be achieved in Elixir/Phoenix, yet, I must say, I have no experience with it.
Having said that, if instead of calling Repo in your project, you do something akin to get_repo(user, team), where you lazy create that database if it doesn’t exist (and check permissions to access), that should sandboxed enough.
If you think about it, it’s much more secure than other approaches, where a web framework defaults to a db connection. Here, every, single, time, you need to explicitly tell elixir what database to use, by calling a configured Repo (you can configure more than one).
At scale this becomes less appealing. There is a cost to open/close each db. At scale you won’t be able to keep all of them open all the time. With any kind of concurrent usage of a single db, your app will need to coordinate who the “writer” is. If your app moves to multiple nodes, the work will need to be routed to the node that hosts the sqllite db. If your app is transaction heavy, you will end up doing alot more i/o, the syncing of the single postgres WAL is significantly more efficient than an array of sqlite dbs.
@dimitarvp thanks again for the reference to dynamic repos. Using this info, I wrote an prototype library called DynDb to manage Dynamic Sqlite databases. Interested to get feedback re: API design and potential gotchas. Have a look!
#!/usr/bin/env elixir
Mix.install([
{:dyn_db, github: "andyl/dyn_db"}
])
# Write a Migration
defmodule Migration0 do
use Ecto.Migration
def change do
create table("chats") do
add(:message, :string)
timestamps(type: :utc_datetime_usec)
end
end
end
# Write a Schema
defmodule Chat do
use Ecto.Schema
schema "chats" do
field(:message, :string)
timestamps(type: :utc_datetime_usec)
end
end
# Interact with the DynDb
defmodule Main do
def main do
# The database file will be auto-created
{:ok, db1} = DynDb.start_link(database: "./data1.db")
# Migrate
DynDb.migrate(db1, Migration0)
# Query
DynDb.insert!(db1, %Chat{message: "HELLO at #{Time.utc_now()} UTC"})
DynDb.all(db1, Chat) |> IO.inspect()
end
end
Main.main()