pelopo
SQLite based app self hosted on VPS architecture & approach help needed
Hi, a newbie to web dev and Phoenix & Elixir here.
I need advice on how to conceptually understand the topic of database updates and deployment from dev to prod for a SQLite based Liveview Web App.
I’ve got a python based scheduled data pipeline that scrapes, extract and transforms data from Internet and in the end, it writes it all into a duckdb file based database. After that, it uses a built-in feature called sqlite_scanner that generates a SQLite db from the duckdb file. Depending on the day, the final database is updated once or maximum twice per day.
My dev environment uses this SQLite db as a backend for the app. The reason being is that I want to save costs as much as I can, especially because it is just an initial efforts and prototyping. Besides., I already got a relatively powerful VPS server and I want to reuse it. Also, I don’t expect my app to be used by more than 10,000 people anyway.
My questions are:
1 - how do I deploy to prod to my VPS server in case with a file based database like SQLite?
I have my eyes on piku, but I don’t know how to deal with having a file based database that is about 15Gb and therefore too big for github to handle. I would prefer not to use docker.
2 - How to handle the updates to the database? If my Python data pipeline generates a new SQLite file every day, how can I push it to prod , into the existing and running app without glitches? How do I swap one database file for the newer? I don’t mind the users to experience a 1 second glitch either.
3 - Let’s say my data pipeline got to create a new table in the existing database or a new field in the existing table, how do I deal with that on the Phoenix side, do I somehow run automated migrations or as long as my UI components and the logic are ready to receive new fields on new tables are fine, then I don’t need to do anything else ?
Thanks
Most Liked
rhcarvalho
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.
ruslandoga
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
rhcarvalho
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.
Popular in Questions
Other popular topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
We build reliable cloud platforms for business-critical systems.
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #advent-of-code
- #elixirconf-us
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #security
- #performance
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Real-time error tracking, performance insights, and observability for devs.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"