Why you should probably be using SQLite

• StorX

  5 14 10.0 PHP

  PHP library for flat-file data storage

I'm a huge fan of SQLite! My org's apps use it heavily, often via this simple key-value interface built on sqlite: https://github.com/aaviator42/StorX

Handles tens of thousands of requests a day very smoothly! :)

• pocketbase

  177 33,169 9.7 Go

  Open Source realtime backend in 1 file

  

I’ve been using Pocketbase[0] on my projects and I can’t recommend it enough.

It is built on a SQLite db and has real-time pub/sub capabilities. Its JS SDK is incredibly easy to use and setup for CRUD as well. For side projects and some medium tasks, I’d say SQLite/Pocketbase has been super easy to work with.

[0] https://pocketbase.io

• InfluxDB

  www.influxdata.com sponsored

  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.

  

• litestream

  165 9,964 7.5 Go

  Streaming replication for SQLite.

One possible strategy is to have one directory/file per customer which is one SQLite file. But then as the user logs in, you have to look up first what database they should be connected to.

OR somehow derive it from the user ID/username. Keeping all the customer databases in a single directory/disk and then constantly "lite streaming" to S3.

Because each user is isolated, they'll be writing to their own database. But migrations would be a pain. They will have to be rolled out to each database separately.

One upside is, you can give users the ability to take their data with them, any time. It is just a single file.

[0]. https://litestream.io/

• sqlite-utils

  35 1,510 8.1 Python

  Python CLI utility and library for manipulating SQLite databases

Sounds like your problem is with SQLAlchemy, not with SQLite.

My https://sqlite-utils.datasette.io library might be a better fit for you. It's a much thinner abstraction than SQLAlchemy.

• databases-intuition

  3 44 7.1 Go

  Building an intuition for latency and throughput of basic operations across SQL databases.

Without too much difficulty you can get into the 100,000s inserts per second range, and even near 1M inserts per second.

https://github.com/eatonphil/databases-intuition#go-mattngo-...

• marmot

  33 1,628 8.6 Go

  A distributed SQLite replicator built on top of NATS

• libsqlfs

  9 571 0.0 C

  a library that implements a POSIX style filesystem on top of an SQLite database

  

- Use clone file to duplicate the cached data directory to give to individual tests.

One thing I'd like to pursue is to store the Postgres data dir in SQLite [1]. Then, I can reset the "file system" using SQL after each test instead of copying the entire datadir.

[1]: https://github.com/guardianproject/libsqlfs

• WorkOS

  workos.com sponsored

  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.

  

• embedded-postgres

  5 321 5.7 Java

  Java embedded PostgreSQL component for testing (by zonkyio)

  

Little use if you’re not on the JVM but I’ve had great success with Embedded Postgres:

https://github.com/zonkyio/embedded-postgres

Each test just copies a template database so it’s ultra fast and avoids the need for complicated reset logic.

Suggest a related project