[jbverschoor]

- Most transactions are read-only

- "Large" applications can usually be sharded by account. This means 1 file per account, and can easily be put on the most optimal geolocation of the account

- If you defer locking until commit, allowing multiple writers ( https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi... ). This is good enough for most applications anayway.

- Sqlite simple, fast enough for almost anything, supports a good set of features and datatypes, is very easy to embed.

[tidenly]

Why would I bake all of those assumptions and limitations into my system though just on the hope it won't ever become a problem

[onetom]

because you can move faster and explore you problem domain cheaper and validate your solution earlier. then, if you "struck gold" and happened to arrive to some product market fit, then even if you would need to rewrite big chunks of your solution, to swap out your persistence layer, you have a solid specification to follow, which is your sqlite-based implementation!

such a rewrite is a lot more predictable endeavor, then building the initial solution, that it's a great problem to have :)

meanwhile, your UI don't have to change and a lot of your other glue code or business-logic code don't have to change either, IF you haven't hardcoded direct calls to SQLite everywhere in your program :)

eg. I used HoneySQL with great success! My queries are safely assembled from Clojure data structures and I had a single function, which I used to format them to the desired SQL dialect H2DB/SQLite/MySQL/Postgres, execute them and parse the results back into Clojure data structures and even take care of lazily paginating through long result sets, without burdening the consumer of the data with such details.

https://github.com/seancorfield/honeysql

[danuker]

Depending on your expected lifecycle of the app, you should use an adapter between the DB and the rest of your app.

Have an interface made up of clear methods which you do all your business through. That way, if you want to change the DB, you only need to rewrite the adapter.

I believe SQLite is a great, low-insallation-needs portable DB you can start your project with. If it turns out your startup gets millions of users, you can afford to switch.

[ignoramous]

> Depending on your expected lifecycle of the app, you should use an adapter between the DB and the rest of your app.

A sane design, but realise that limitations tend to leak through abstractions.

[_fizz_buzz_]

This questions goes both ways: Why put effort into something that might never even be a problem?

[jbverschoor]

Simpler backups. Simpler restores. Easier multi tenancy. Easier to have data+app near customer. No firewalls. No passwords. No keys.

Why do you assume you'll run into problems? The moment you're running into problems, you better have a good plan with any RDBMS.

[whiddershins]

How hard is it to migrate if/when.