I’m building a web app and would like to hear the thoughts / experiences of others as to the architecture they have employed, particularly around the choice of db and using a load balancer.
Currently I’m using sqlite as the db during development but expect to run multi-instances (say 10 or more) of the web app, load balanced by HA Proxy. I’m interested to know if anyone has done something similar using sqlite as the db and if so, what management they have used (ie cubesql, valentina or maybe even a Xojo web service using Luna for example) ?
Thanks for any and all experiences
Steve
P.S. For context, I know that something like Postgres is a no brainer on the web end BUT down the track I’d like to build out something (either/and mobile / desktop) with offline functionality which would use sqlite - hence my questioning relating to it and being largely portable for my needs between the overall solution elements.
SQLite is a FILE, it can only used by a SINGLE instance. Why try to hack something that is not intended to be used like that, reinventing the wheel when there are lots of RDBMS available?
Xojo cant even make Web/Mobile/Desktop apps with the same source code, so, why bother to limit one of the platforms if you are not even reusing the code?
Not sure if this helps, I have a Xojo web App using SQLite. It’s been up for about three years and is solid as a rock. It has about 50 sessions open all day every day and they’re all hitting the same DB file - it does about 2000 transactions a day. In addition to this there’s often users on the website https://www.coffeestamp.co.nz checking their account etc also so this can sometimes add dozens of (temp) open sessions hitting the same DB file too. I did crash the servers the other day when about 6500 people checked a promotion on the site though via and email out. It’s in the hospo sector so gets very bus over mid day - often a transaction every few seconds. To my knowledge not one bit of data’s ever been lost or dropped. Not sure if this is how things are meant to be setup ? Seemed like a good idea at the time.
For example, being a single thread solution, you cant use loadbalancing directly. In MySQL or Postgres you can use it from lots of concurrent instances or apps at the same time
Hi Ivan, I’m not familiar with load balancing but am aware it’s something I probably need to look into. At the moment though many instances of my App are using the SQLite file concurrently and it seems to work OK. What are the benefits of going to a RDBMS ? Thanks.
@Ivan Tellez the fact that the codebase will not be exactly the same doesn’t bother me too much. Regarding sqlite - I know what it is etc and I have an idea some are using it with a server in a load balanced architecture - I am seeking their experience(s).
@Barney Hyde thanks for the input on your setup although I suspect that others would warn against multi-instance directly hooking into the one sqlite file unless theres some locking mechanism involved ?
Many instances of your app, or a SINGLE instance with multiple users?
Since xojo is not multithread, the apps are very resource limited, to avoid the problems you describe with your promo email, you need multiple copies of your app runing at the same time, and an app that balance the trtaffic to those instances. SQLite can’t work in this scenario.
I have multiple Xojo apps on the one web server accessing the one SQLite database open for read/write at once, but you MUST open the SQLite database as multi-user. You cannot access this SQLite file over a network.
Well the traditional view (and my understanding) is that we shouldn’t have multiple instances (aka processes) accessing the same sqlite db file in a write mode…
It’s nonsense that you shouldn’t. It depends on your needs!
It’s possible to use wal (multiuser or WriteAheadLogging in xojo) which allows for multi-process and/or multi-thread usage. It may be better to use something like mysql or postgresql but that uses alot more memory on average.
Just be sure to use transactions if you use sqlite using WAL.
CubeSQL works VERY well with Xojo and gives you a ton of options for sqlite based applications. I would recommend download the free version (I believe it gives you up to 2 concurrent connections) to see if it fits your needs. For example, you can import your sqlite database to it, create a database connection string to CubeSQL and off you go.
I would do this first before considering other database options.
Getting back to your original question - the good news is that the database handling in Xojo is pretty consistent no matter the back-end, so it would not be too much work using MySQL/MariaDB from a standard Linux LAMP stack to get very stable multiuser access for your Web app and then use SQLite for the desktop where you’re dealing with single user access.
No, as I don’t have the need for load balancing. It is a Web App and a couple of Console apps accessing the one SQLite database. I use MBS for multi-user SQLite access, but I assume Xojo SQLite would work just as well.
