multiple client connections to db

[timhatch]

Apologies for submitting this as an issue - I didn't see a means to otherwise ask a general question.

I have been using a sqlite database to collect and sort results, but allowing multiple connections (so that the workload of entering data can be shared between a small group of people - the actual use case is collecting competition results where we need to enter 100s of results quite quickly). I was thinking of re-writing the system we've been using as a node application (long story).

Having used dblite in some other node work that i've done recently, I wondered how how dblite might cope with this case. Specifically, supposing the node application opened a single database connection, but INSERT/UPDATE queries were received from multiple clients, would dblite queue the relevant queries or would I likely run into blocking issues at the sqlite end of things?

I'm not talking about a large number of clients, usually less than half a dozen perhaps, and whilst there might be other database solutions that could be used sqlite has worked well enough in the past that I don't particularly feel the need to change.

Thanks for any advice and apologies again if I've missed a more appropriate means of enquiry...

[WebReflection]

2 or thousands makes no difference, you can open an SQLite file once per session, otherwise it's locked. This is not a dblite limit, rather how sqlite works.

What you should try to do is simply keep the opened db reference available through the main logic and use that to query any sort of data.

All queries will be queued as it happens anyway, and everything shuold work without problems.

Does this make any sense?

[timhatch]

Yes, it makes perfect sense. My thinking was to open a single connection to the database when the app launches and then allow multiple clients to access the app. I just wondered what would happen if a second query were submitted before the first had been completed. 

I’ll write a test application and see how what happens.

Thanks for the reply.

Tim

[WebReflection]

well, it preserves atomic results so if user A scheduled in queue 100 queries, user B has to wait 'till these are completed. This is I believe how it works, again, SQLite anyway, maybe faster if natively queried but it still grant atomicity per each query after the other.

The way you are testing, would be the way I'd go anyway, I hope that works for you.