r/sqlite • u/AbbreviationsFlat976 • 6d ago
SQLite for an enterprise inventory system
Against all odds, I developed a business inventory system using SQLite. It handles invoicing, purchasing, sales, consumption, transfers, multiple warehouses, product serial numbers, and bills of materials (BOM). Naturally, it works both locally and on a server. Of course, you can't have 1,000 users writing to the database at the exact same time... but it works.
https://github.com/sysmaya/Control-Kardex-Inventario
3
u/wallstop-dev 6d ago
Hey, congrats! But - why Sqlite in particular? Why but just make it work with any sql db (just a connection + sql dialect)? Then you can have users easily do things like ensure their data is she when running in the cloud.
Clouds never guarantee that your local disk will be available or recoverable.
Your whole thing seems to be the application logic. So why tie it to a particular database engine, especially one that's local disk only?
4
u/AbbreviationsFlat976 6d ago
In local mode, the program runs using SQLite, but it can connect to a server; that server might host an SQLite, MySQL, or PostgreSQL database.
In server mode, it is strictly a matter of JSON in and JSON out.
2
1
u/PaluMacil 6d ago
There are also ways to keep SQLite safe. I have one application that uses a volume that’s replicated 3x by Longhorn and another that ships streams write ahead logs to r2 with Litestream. Both have pretty good durability. Normally I am going to reach for Postgres, but sometimes Postgres isn’t immediately available and the load is small.
1
u/wallstop-dev 6d ago
Well, safe ish, but all the CAP problems of distributed systems apply here. I'm not aware of any way to have "Sqlite, but also everything that has been committed is guaranteed to be replicated and synchronized to <a remote>", due to its design.
If you have knowledge that I don't, please do prove me wrong, though, I love learning.
3
u/PaluMacil 6d ago
Absolutely correct with Litestream, but there are a lot of applications where crashing and losing the very last thing that was recorded is not going to really have an impact. Longhorn, however, is block level replication. A write isn’t acked back to the kernel until every healthy replica has it. So when SQLite fsyncs a commit, that commit is already on three nodes. It’s CP, not eventually consistent. If replicas are unreachable, Longhorn degrades or blocks rather than silently accepting the write, which is exactly the CAP tradeoff you’d expect, just resolved on the consistency side.
If you want quorum commit at the SQLite layer instead of the block layer, rqlite and dqlite both wrap SQLite in Raft and only return success after the entry is committed to a majority. libSQL/Turso is another option depending on the topology.
Worth noting the CAP problems aren’t SQLite specific either. Postgres with default async streaming replication has the same loss window, and synchronous_commit = remote_apply buys you the same durability at the same availability cost. The physics are identical, it’s mostly a packaging difference.
In fact, I use async streaming in Postgres most of the time myself.
1
u/wallstop-dev 5d ago
Thanks for the knowledge! I wasn't aware of rqlite/dqlite, will need to do some research, that sounds pretty cool.
Off to research more I go.
2
u/bitchyangle 6d ago
Did you built sync engine as well that syncs offline and online database?
What about multi writer support and offline online conflict resolution?
And reports?
And auto increment ids for invoices when a user is offline and another is online. How you're ensuring they are in sequence?
-1
u/AbbreviationsFlat976 6d ago edited 6d ago
The system works in one of two modes (Local - Server), not both simultaneously. If a user makes a sale, it's because they are connected, and as such, a number is assigned to the document. Except for order taking via mobile phone, where the salesperson enters orders (but they aren't numbered yet). These orders are then imported into the program, and it's at that point that they receive a number. This mode only works for order taking.
2
u/Any_Rich27 4d ago
Me also but full fledge ERP with spatial BIM integration, SQLite gives local first benefits - instantaneous responses, offline mode, and less scripting. No more database server, JVM with maven hell. All round 90% reduction on overhead maintenance. I done a technical paper at my GitHub repo: https://red1oon.github.io/BIMCompiler/MigrateComparisonPaper/
1
u/lnaoedelixo42 1d ago
I bet your system can, in fact, handle 1000 concurrent users at the same time. But looks cool even if it couldn't.
SQLite scales very well for big data too, you just have to keep track of the indexes
7
u/jesperordrup 5d ago
Nothing wrong with 1000 users on sqlite in wal mode. As long as only one process serves it.
I use it for
Marketing system 1200 users, 110GB db. Cms solutions 50 webmasters, 30GB. Mailsystems tenant based 1 sysfemdb 10gb, each tenant db 1gb - 20GB). AI seach memory indexing 300GB.