Our great sponsors
-
-
Prisma
Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, MongoDB and CockroachDB
-
WorkOS
The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.
-
-
-
-
-
-
InfluxDB
Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.
-
-
-
Bedrock
Rock solid distributed database specializing in active/active automatic failover and WAN replication (by Expensify)
-
-
SaaSHub
SaaSHub - Software Alternatives and Reviews. SaaSHub helps you find the best software and product alternatives
https://dqlite.io/
I’m sure there’s more, those are just the ones I remember.
Note that the popular Node.js ORM Prisma does not support WAL.
https://github.com/prisma/prisma/issues/3303
> Is support for SQLite in the Elixir ecosystem, particularly Ecto, good enough for this?
Why yes it is. I maintain the `exqlite` and `ecto_sqlite3` libraries and it was just integrated in with `kino_db` which is used by `livebook`.
https://github.com/elixir-sqlite/exqlite
Litestream does a couple of things. It started as a way to continuously back sqlite files up to s3. Then Ben added read replicas – you can configure Litestream to replicate from a "primary" litestream server. It's still limited to a single writer, but there's no s3 in play. You get async replication to other VMs: https://github.com/fly-apps/litestream-base
We have a feature for redirecting HTTP requests that perform writes to a single VM. This makes Litestream + replicas workable for most fullstack apps: https://fly.io/blog/globally-distributed-postgres/
It's not a perfect setup, though. You have to take the writer down to do a deploy. The next big Litestream release should solve that, and is part of what's teased in the post.
It already has it [1]. Native litestream that RPC's to the primary sounds interesting though!
[1] https://github.com/phoenixframework/phoenix/pull/4268
Best option for SQlite with node is this.
https://github.com/JoshuaWise/better-sqlite3
He's all over the issues section, and seems very knowledgeable about how SQLite works.
I agree with this article! I even went so far as to write a Prisma-like SQL client generator that uses better-sqlite3 under the hood, so you get the nice API of Prisma and the synchronous performance of better-sqlite3. I’ve been using it for a few small projects, but I just released it at 1.0 yesterday.
https://github.com/CGamesPlay/rapid-cg
I've not done this but it's intriguing; potentially a best-of-all-worlds solution.
I think "proper" automatic replication is not possible given the mismatch between Postgres and SQLite - not everything in Postgres maps to a thing that is possible in Postgres.
That said, there are a variety of ways to get data out of Postgres, and a variety of ways to get things into SQLite.
You could periodically export CSVs or whatever from Postgres and periodically import them into SQLite.
Or you could do a more realtime sync by using a Postgres foreign data wrapper like this one: https://github.com/pgspider/sqlite_fdw which would let you write directly to SQLite. Combine that with database triggers on the Postgres side of things and you've got something pretty close to realtime replication.
Those sorts of solutions wouldn't be as robust as "real" replication, but could be very useful for a lot of scenarios. You could have Postgres importing gobs of data, and "replicating" it over to your reporting server which uses SQLite as a data source.
I've used it from Clojure, via HoneySQL, so no ORM, no danger of SQL injection. It was really wonderful!
https://github.com/seancorfield/honeysql
I used it to quickly iterate on the development of migration SQL scripts for a MySQL DB, which was running in production on RDS.
I might have switched to H2 DB later, because that was more compatible with MariaDB, but I could use the same Clojure code, representing the SQL queries, because HoneySQL can emit different syntaxes.