5 ms·
A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite or something
by yread 8y ago
A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite
https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
or something else?
- newusertoday 8y agoI am interested in this topic as well, although sqlite explicitly says that it is not meant for client-server configuration. I still want to see if it is feasible and someone is using it in production.
- move-on-by 8y agoI got thrown into a legacy web project that used sqlite as the database. It was a small internal-only app, I guess the original developer(s) figured it was so small that sqlite would be plenty and it would reduce the environment complexity. Unsurprisingly they were wrong. It was small, but sqlite couldn't handle multiple users. I believe this was before sqlite had WAL support, so reading would lock the DB. The 'solution' was to split the sqlite database into many smaller DBs that would allow users to use the site at the same time as long as they were in different areas. This greatly added to the complexity of the application. Some reports would need to access multiple databases to get what it needed, so it would still lock out people. Complexity was much higher then having all the data in a single postgresql/mysql database. All the users hated the system and often ran into DB lock issues. > sqlite explicitly says that it is not meant for client-server configuration They are right and their advice should be heeded.
- zip1234 8y agoSince 2010, SQLite has had Write-Ahead-Logging. Perhaps your project was using an older version of SQLite? https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- misframer 8y agoExpensify uses SQLite for their core database. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...
- yread 8y agoInteresting discussion of bedrock https://news.ycombinator.com/item?id=12739771 https://news.ycombinator.com/item?id=12739771
- zip1234 8y agoWhat they mean by that comment is that it doesn't have users in the sense that mysql, SQL Server, etc have. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html The documentations says that it works well for websites. For client server, you just need to make an API that handles the interactions with the database.
- siscia 8y agoMost applications don't need the level of concurrency that Pg or MySQL provide and they will be just fine with something simple to operate, which is why I wrote https://redisql.com/ https://redisql.com/
- mamcx 8y agoAlmost all my main apps have a sync history. Before was using batch processing and a version field: customer: name, ...., version = 1 update customer: name, ...., version = 2 and sync manually. Now I use a log/event sourcing like setup, where every write to the BD is also stored in a log table and sync from there. Much better!
- blattimwind 8y agoMake your logged events invertible and get an undo/redo function for free. That's how I kill three to four birds with one stone.
- mamcx 8y agoinvertible? What is that?
- blattimwind 8y agoSay you record an event like "name of customer X has been changed to FooCo". Instead, record "name of customer X has been changed to FooCo from Foo & Sons Co". If you want to undo it, just swap from/to (=invert it).
- zbentley 8y agoA common pattern to implement this is an audit table: https://dba.stackexchange.com/questions/15186/what-is-an-audit-table https://dba.stackexchange.com/questions/15186/what-is-an-aud... ...which is itself a special case of an audit trail or log: https://en.wikipedia.org/wiki/Audit_trail https://en.wikipedia.org/wiki/Audit_trail
- blattimwind 8y agoIn my experience the usefulness of audit tables diminishes as the table becomes more complex (more columns; composite and document columns, like arrays, json, xml), because it becomes difficult to find what exactly changed. Another problem is related rows; audit tables aren't very good to capture the context of changes. You can work around this with more complex triggers, but it quickly becomes a lot easier to do this from the application and not in the database, capturing semantics and not just data changes. The issue with any application-based approach is of course that it can't provide total access mediation.
- siscia 8y agoShameless plug since I am the author, but you could be interested in https://redisql.com/ https://redisql.com/ Basically an Redis module that embed SQLite, I offer replication on the PRO version, if you want to try it out you can download the trial version for free. If the trial version is not enough, send me an email and we could work something out ;)
- onli 8y agoRelated: Is there a ruby client for rqlite? I can't find one.
- atombender 8y agoBedrock [1] by Expensify is supposed to be solid. Master/slave async replication (i.e. all slaves are read only), pluggable, provides full SQLite access via the MySQL protocol. [1] http://bedrockdb.com http://bedrockdb.com