Alex Koutmos played with something similar previously, maybe this is useful? Alex Koutmos on X: "Using Ecto dynamic repositories, a DynamicSupervisor, and a Registry, I was able to create a SQLite powered Phoenix app where every user has their own SQLite db and interacts with their own repo instance. I have now achieved the sharded webscale. AMA #MyElixirStatus https://t.co/3qJkQokooi" / X
trarbr
akoutmos
Author of Build a Weather Station with Elixir and Nerves
A little late to the party here, but the proof of concept repo is open source and available for perusing 
8
homanchou
Could you say a little more about the overall design and thought process in terms of scalability and failover?
And my understanding is that sqlite can only have one connection, which might be fine if you guard access using a single genserver.
When using multiple SQLite databases, which are usually on same file-system as the application accessing it, how do then move to supporting a multi-node configuration?
Then does every node mount a shared drive or file system to access the sqlite files or is there a primary node that opens connections to databases and every other node connects to that node? Or is there a global registry that is synced with all connected nodes, and each node has access to the same mounted file system and every node can checkout and connect to databases but the registry will only allow one node to connect to a database at a time?
0
warmwaffles
This is an old constraint. As long as you open your database with journal_mode: :wal you should be fine with multiple processes writing to the same database. Where you WILL run into potential issues is if you are writing to a database file that is on an NFS mount. Then your file system locks won’t be perfect. I haven’t had any issues yet but I suspect if I have network issues that NFS mount is going to suffer.
With how ecto_sqlite3 and exqlite are currently built, it uses DBConnection to pool the sqlite file handles. We have had discussions about moving to a single genserver for writing and reads going to another genserver(s). But right now each node has its own pool of handles open for the database.
I have a personal project I’ve been toying with where I have thousands of sqlite databases. The data is partitioned that way so that I can do a really dumb map reduce. I have it setup to use dynamic repos which causes the connection pool to go down to 1 and execute on that. All of the databases are local to the instance and replicated out to a file server for backups. I avoided opening the database over NFS for the fear of network gremlins.
There are a lot of ways to slice this, and just make sure you have a plan in place to deal with the faults that will happen.
1
dimitarvp
SQLite has 3 modes of opening a “connection”:
- A handle usable only by a single OS thread;
- A handle usable by multiple OS threads;
- Multiple handles all pointing to the same SQLite database file.
Like @warmwaffles says, you can also open the DB in wal or wal2 mode in combination with any of options 2 and 3 and then your SQLite is effectively like a local Postgres. It would still struggle if there was a big amount of writes per second – I reckon 2000 or more – but most apps needing SQLite don’t do that so… 
1
andreh
Hi, this is really nice, thanks! I’ve tried to create 100 users using this seeds.exs:
Enum.map(1..100, fn k ->
IO.puts("Creating user #{k}")
{:ok, user} =
SqliteScale.Accounts.register_user(%{
email: "user#{k}@dot.com",
password: "passpasspasspass"
})
end)
Unfortunately, I get this error message:
Creating user 91
[debug] QUERY OK source="users" db=0.0ms idle=367.7ms
SELECT 1 FROM "users" AS u0 WHERE (u0."email" = ?) LIMIT 1 ["user91@dot.com"]
[debug] QUERY OK db=0.3ms idle=519.8ms
INSERT INTO "users" ("email","hashed_password","inserted_at","updated_at","id") VALUES (?,?,?,?,?) ["user91@dot.com", "$2b$12$Cpfw1ZaIVOiyoipYKMZlGe0AzDOryNUfg/R0uuNHr0eu7g2W/nOvG", ~N[2023-12-22 17:49:46], ~N[2023-12-22 17:49:46], "61079def-1734-4f8a-91d2-21a3edec047a"]
[error] Exqlite.Connection (#PID<0.1316.0>) failed to connect: ** (Exqlite.Error) database_open_failed
[error] Exqlite.Connection (#PID<0.1314.0>) failed to connect: ** (Exqlite.Error) unable to open database file
[error] Could not create schema migrations table. This error usually happens due to the following:
* The database does not exist
* The "schema_migrations" table, which Ecto uses for managing
migrations, was defined by another library
* There is a deadlock while migrating (such as using concurrent
indexes with a migration_lock)
[...]
** (Exqlite.Error) unable to open database file
CREATE TABLE IF NOT EXISTS "schema_migrations" ("version" INTEGER PRIMARY KEY, "inserted_at" TEXT)
(ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1047: Ecto.Adapters.SQL.raise_sql_call_error/1
(elixir 1.15.7) lib/enum.ex:1693: Enum."-map/2-lists^map/1-1-"/2
(ecto_sql 3.10.2) lib/ecto/adapters/sql.ex:1154: Ecto.Adapters.SQL.execute_ddl/4
(ecto_sql 3.10.2) lib/ecto/migrator.ex:756: Ecto.Migrator.verbose_schema_migration/3
(ecto_sql 3.10.2) lib/ecto/migrator.ex:564: Ecto.Migrator.lock_for_migrations/4
(ecto_sql 3.10.2) lib/ecto/migrator.ex:433: Ecto.Migrator.run/4
(sqlite_scale 0.1.0) lib/sqlite_scale/user_repo.ex:16: SqliteScale.UserRepo.with_dynamic_repo/2
(sqlite_scale 0.1.0) lib/sqlite_scale/dynamic_repo_supervisor/repo_supervisor.ex:72: SqliteScale.DynamicRepoSupervisor.RepoSupervisor.run_migrations/2
(sqlite_scale 0.1.0) lib/sqlite_scale/dynamic_repo_supervisor/repo_supervisor.ex:53: SqliteScale.DynamicRepoSupervisor.RepoSupervisor.add_repo_to_supervisor/1
(sqlite_scale 0.1.0) lib/sqlite_scale/accounts.ex:88: SqliteScale.Accounts.register_user/1
priv/repo/seeds.exs:16: anonymous fn/1 in :elixir_compiler_1.__FILE__/1
(elixir 1.15.7) lib/enum.ex:4356: Enum.map_range/4
(elixir 1.15.7) lib/enum.ex:4356: Enum.map_range/4
(elixir 1.15.7) lib/enum.ex:4356: Enum.map/2
priv/repo/seeds.exs:12: (file)
I can’t believe 90 files could exhaust the number of file descriptors that the BEAM can handle! Any idea? I’d like to have ~1000 databases, I hope it’s possible!
0