5 ms·
> your normal dB driving some web-based CRUD app That can totally be handled with SQLite.
by hasenj 9y ago
> your normal dB driving some web-based CRUD app
That can totally be handled with SQLite.
- sametmax 9y agoI have several crud apps running sql. With moderate write load and a good concurrency write error handling code it works very well. Good when the product size is not worth a postgres full blown setup.
- raphaelj 9y agoI backup this. I've been using SQLite for some low to moderate load CRUD apps, and this always worked like a charm. SQLite also make backuping, testing and moving apps so much easier.
- ngrilly 9y agoHow do you workaround the fact that SQLite only supports a single writer? For example, your app could be blocked when you run a long operation like creating an index.
- sametmax 9y agoFor moderate load: just catch the exception and try again. It will rarely happen, and when it does, you can easily recover from it since your site is not hammered. For bigger load, have a worker that does the writes with a queue.
- ngrilly 9y agoHello Sam and/or Max, I'm French too and I read your blog sometimes ;-) Is the following what you suggest: You create an index, and the index creation takes 30 seconds. Then instead of writing directly to SQLite (which won't work since the index creation blocks other writers) you suggest to store the write in a queue (for example another SQLite database for durability), and have a worker apply the write to the main database when the index creation is done?
- Daycrawler 9y agoUnless you want to scale. I'm all for the rule of least power, but as soon as the app is exposed to multiple users I would ask and be sure about the expected number of simultaneous users before going with SQlite instead of going with a Client/Server RDBMS. Still, the bound is pretty high if you keep your transactions short.
- geocar 9y agoIf you have the correct architecture, multiple users shouldn't matter at all-- I have used SQLite to store and query activity data with something like 10k concurrent "users" without difficulty on a single machine. I am beginning to suspect that MySQL, PostgreSQL, DB2, Oracle, BigTable, and others allow one to get so far with the wrong architecture, that maybe some even very experienced programmers believe that to go faster they have no choice but more threads.
- cousin_it 9y agoCan you describe the right architecture?
- ngrilly 9y agoHow do you workaround the fact that SQLite only supports a single writer? For example, your app could be blocked when you run a long operation like creating an index.
- hasenj 9y agoSQLite supports concurrent reads and writes since 2010 with the introduction of the "write ahead log" https://sqlite.org/wal.html https://sqlite.org/wal.html
- ngrilly 9y agoYes, but with a single writer at a time. From the link you shared: "However, since there is only one WAL file, there can only be one writer at a time".
- scott_karana 9y agoNot if you need to use multiple app servers.