Hacker News Reader

Comment by yread | Hacker News Reader

A bit offtopic but has anyone tried to replicate sqlite databases? Using rqlite https://github.com/rqlite/rqlite or something else?

View on HN

I 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.

I 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.

Since 2010, SQLite has had Write-Ahead-Logging. Perhaps your project was using an older version of SQLite? https://www.sqlite.org/wal.html

Expensify uses SQLite for their core database.

https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

Interesting discussion of bedrock

https://news.ycombinator.com/item?id=12739771

What 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 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.

Most 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/

Shameless plug since I am the author, but you could be interested in 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 ;)

Just a heads up, you should probably have someone proof read your copy. There is a typo in every paragraph.

Thanks I will proceed soon!

Almost 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!

Make your logged events invertible and get an undo/redo function for free. That's how I kill three to four birds with one stone.

invertible? What is that?

Say 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).

A common pattern to implement this 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

In 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.

Bedrock [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

Related: Is there a ruby client for rqlite? I can't find one.

Reply on news.ycombinator.com