Separate Sqlite Database per Customer?

Phoenix Forum Questions / Help
,
1

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.

Is it even possible? Is anyone doing this?

2 Likes
2

I’ve done it in Rust and Golang but not in Elixir yet. Very common practice in certain businesses.

3

In general Sqlite does not provide strong security mechanisms. It might be inappropriate to handle sensitive data.

On the other hand, I do see the benefits if you need to delete a particular’s customer data easily.

4

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).

5

Ecto also allows you dynamic repositories.

5 Likes
6

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.

7

Ecto also allows you dynamic repositories .

This looks promising. Thanks for the tip !!

8

:dets might be an option since it uses one file per key-value store and has simple commands to open and close the files.

9

:dets is simple to use for dynamic k/v stores…

Yes! Also CubDB.

10

@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()
3 Likes
11

Looks alright for a start. I’d have 80% of the code be tests though. :grin: But that’s for later stage.

1 Like
12

Alex Koutmos played with something similar previously, maybe this is useful? https://twitter.com/akoutmos/status/1495830005267542027

2 Likes