SQLite based app self hosted on VPS architecture & approach help needed

Does your app only read from the database or does it also do writes?

I expect only a couple of write scenarios.

1 - Generating users and auth. I want to reuse what comes standard with Phoenix Liveview

2 - At some point later, I will probably build and internal bookmarking functionality where the logged users would be able to save certain pages (based on url params) to their list of favourites.

I probably will have to somehow enable the updated and newly generated SQLite DB to respect existing user tables or maybe even use two SQLIte dbs in one app. One for the app itself, which will be mostly tables and charts and the other SQLite db for user management.

For your user data, I would caution against using SQLite if you expect thousands of users. SQLite has no write concurrency (at all), and its transactions per second are measured in tens. Which is fine as long as you don’t need many transactions, but just keep in mind it’s not going to scale very far (for writes). If you have a powerful VPS (as you say), consider just running Postgres on the VPS to handle the user data. Then if you ever need to scale in the future you can just get a bigger server.

As for your questions:

If you mean for your user data, check out Litestream and LiteFS (by the same guy, at flyio). They handle backing up/persisting the database to object storage or another server, in slightly different ways. I can vouch for Litestream in particular, very low maintenance (I’ve never had to touch it). Great for personal projects with SQLite.

Well the easiest way might be to run the Python pipeline on the same server, so you already have the file. But if you want or need to use a different machine, you could throw the file into S3 or similar, or rsync/sftp it onto the server. If you were feeling clever you could add an endpoint to your Phoenix server (behind some sort of auth of course) and use that to upload the file over https (I quite like that solution actually).

As far as switching over, I’m not actually sure the best approach there. There’s probably some way to close the existing Repo and re-open it (perhaps someone else can chime in?). You might also be able to engage in some shenanigans with dynamic repos.

Ecto migrations and schemas are separate. If the database is only written by your Python pipeline, as long as the correct schemas are in place in your Elixir app they will just work. If you change the database tables you have to change the Ecto schemas in your code and deploy the changes.

its transactions per second are measured in tens

Tens of thousands?

Mix.install([{:xqlite, github: "ruslandoga/xqlite"}])

db = XQLite.open("some-db.sqlite3", [:readwrite, :create])
XQLite.exec(db, "pragma journal_mode=wal")
XQLite.exec(db, "create table records(id integer primary key, age integer not null, name text)")

count = 20_000
records = Enum.map(1..count, fn i -> %{age: i, name: "name-#{i}"} end)

:ets.new(:stmt_cache, [:named_table])

sql = "insert into records(age, name) values(?, ?)"
:ets.insert(:stmt_cache, {sql, XQLite.prepare(db, sql)})

defmodule Bench do
  def insert(%{age: age, name: name}) do
    [{_, stmt}] = :ets.lookup(:stmt_cache, "insert into records(age, name) values(?, ?)")
    :ok = XQLite.bind_integer(stmt, 1, age)
    :ok = XQLite.bind_text(stmt, 2, name)
    XQLite.step(stmt)
  end
end

{ms, :ok} = :timer.tc fn -> Enum.each(records, &Bench.insert/1) end
IO.puts("Inserted #{count} records in #{ms / 1_000_000} seconds")

outputs

Inserted 20000 records in 1.033437 seconds

And since it seems like the SQLite databases would be immutable (created by DuckDB), a single readonly connection can be used for multiplexed statements and “unlimited” concurrency. A file watcher can be set up to switch the connection to new database files once they become available.

very nice and way more than I’ll ever need. My writing will only be users registering or changing password. Dozens of transactions per day maximum.

1- What are the multiplexed statements?
2 - Any example of this file watcher or something standard like LItestream/LiteFS should be enough?

@ruslandoga aren’t you the maintainer of a duckdb Elixir version ?GitHub - ruslandoga/duckdb_nifs: DuckDB bindings for Elixir · GitHub

Do you think duckdb could be used as a direct substitute for SQLite?

Might be worth knowing that recently the SQLite team shipped a new command line tool, sqlite3_rsync, which could solve the problem of updating the file in-place and efficiently.

I’d also advise using separate Ecto repositories and database files for your user data and this apparently read-only data dump, as it would make your life easier when it comes to database backups and what not.