Sqlite in a network

[Bill_Snaith]

I have a XOJO desktop App that uses a SQLLite database.
I want to store this database on a network drive, so other users may access it.

Question 1, can I store this database on a network drive and access it from a desktop residing App. [so when opening a new file, do I simply point to the network drive?]

Question 2, Can other users access that database from their desktop App?

Question 3, what happens if it is simultaneous access?
Thank you.

[Sascha_S]

Can sqlite support multiple users?

I’ve tried setting sharing a sqlite database over a LAN and got some odd results. Example the first user that opens access to the database gets full normal access. User 2 might access user a record created by user one and update it. This seems to work fine until user 1 access the updated record and doesn’t see any changes and user to will see those changes. I’m probably doing something wrong. If there’s any documentation someone could point me to or a quick example it would be greatly appreciate…

[kevin_g]

I suggest you don’t as you could end up with corrupted data. You really need a server based database such as MySQL or PostgreSQL.

[PaulS]

SQLite is single user based and is not suitable for a multi-user environment. You should use PostgreSQL or MySQL.
If you want to use SQLite in a multi-user environment you should take a look at cubeSQL from SQLabs.

[Bill_Snaith]

Thank you, that’s helpful! I agree about documentation. I find it really difficult to find documentation to tell me 'how to change from SQLLite to another database type, and moreso, to install a desktop App and link it to the networked database"

[Bill_Snaith]

Great thanks. But 'how to change from SQLLite to another database type, and moreso,how to install a desktop App and link it to the networked database", I can’t find any documentation to learn from.

[Bill_Snaith]

Thank you Paul.

[MarkusR]

the point is, a database service run usually at own pc 24/7.
this could be a cheap mini pc with windows os or raspberry pi.
setup database, backup and maintenance claims time.

if you not need concurrent access you could lock the file if a user have it open,
similar that microsoft do with excel files in network.

alternate each user could have his own sqlite database local and you just exchange data.

about sqlite files, always create and have backups if you have important data.

[Bill_Snaith]

Thank you, this is a reply that allows me to proceed with a SQLLITE DATABASE HELD ON A NETWORK. I then code in my app, that when opening the database I place a lock file on the network, so other users cannot open simultaneously. I then delete that lock file when I close my App. This is simple to do. I am keen to maintain and secure the data, it is importantly data, so I need to learn backup etc. thank you for your help.

[Frank_Bicknell]

@Bill_Snaith

I place a lock file on the network

How do you go about this?

[Lynn_Fredricks1 (Lynn Fredricks)]

Valentina Server is a 5-in-1 solution that includes Valentina SQLite Server. You can request a free copy from our website, which includes 10 simultaneous connections.

Server is available as a standalone product OR, you can get VDN which allows you to redeploy copies of Valentina Server with your solution. Server also includes database servers for ValentinaDB (our columnar database) and DuckDB (a popular analytics database). Server also includes Reports Server and Forms Server. That’s all five of the 5-in-1. It also supports native connectivity to Xojo, Java, C#, Objective-C / Swift and more.

VDN (the $599 one) is included in Omegabundle for Xojo 2025 ($399.99), our big summer deal with the best 3rd party vendors supporting Xojo.

[Emile_Schwarz]

@Frank_Bicknell

How do you go about this?

I think at a file (same name as the .sqlite but ending with .locked instead).

There are also isReadable / isWriteable / Locked properties of the FolderItem…

[Joseph_E]

In my 10 years of experience of using SQLite on a network drive while multiple users pound on it all day, I have not had one instance of corruption. I have SQLite databases with hundreds of thousands of records in them in a multi-user environment (10+ users).

This goes against what everyone is saying and counter to what the very old docs on the SQLite database indicate, however, that warning is specific to operating systems that “may” have broken file lock systems. Broken file locking on any modern OS especially Windows, is unheard of.

I even wrote a test suite to try and crash or corrupt the file via multiple simultaneous writes, extended writes and other processes to try and break it. That software ran for a week, 24 hours a day, hundreds of thousands of writes, and not one instance of corruption.

SQLite is quite simple, when an app writes to it, the system locks the file for write and releases the lock once the write is complete. If another user tries to write to the file at the same time, you will get an error back, indicating the file is locked. It’s up to YOUR application to check for this error and act accordingly.

You absolutely can successfully deploy an SQLite file onto a network drive and have multiple users access it. If you design your desktop application properly, you should have no issues. The reason you would want to use SQLite is for simplicity’s sake, it’s easy to install, and easy to administer.

The “can’t use SQLite on a network drive” is wearing thin and quite honestly based on very old data and OS limitations that simply do not exist anymore. MY experience, based on mountains of data, testing and years of actual multiple deployed apps in this environment is that it is quite doable with little risk.

PM me if you’d like for more info.

[Bill_Snaith]

This is amazing, what great news, and a very detailed reply which I really appreciate. Thank you. It accept your knowledge and experience which seems to say I’m ok to carry on as I was intending. I need to catch errors of file being locked while writes are in,progress etc, as you say.
Only one question, a basic one, when people say deploy the database … I believe I simply treat a SQLLite database as any file, open in any directory whether in a network drive or local … is this correct? In other words no setting up of files in the network, no addresses to set up, etc etc.???
Thank you again, this is a great help indeed.

[Richard_Duke]

or use CubeSQL as the database server. I do this for all my client. my client can move from server to the laptop version in one second.

[Joseph_E]

@Bill_Snaith

This is amazing, what great news, and a very detailed reply which I really appreciate. Thank you. It accept your knowledge and experience which seems to say I’m ok to carry on as I was intending. I need to catch errors of file being locked while writes are in,progress etc, as you say.
Only one question, a basic one, when people say deploy the database … I believe I simply treat a SQLLite database as any file, open in any directory whether in a network drive or local … is this correct? In other words no setting up of files in the network, no addresses to set up, etc etc.???
Thank you again, this is a great help indeed.

Yes, an SQLite database is simply a file and is placed on the network drive as such. There is no setup or deployment.

Here are some additional tips for success:

FILE OPS:

1. Ensure your file is located on a computer, preferably a server or network drive that is accessible by all users.
2. Have a provision in your software to select and save that location locally on the user’s PC - see the docs on FolderItem and open file dialog. This is saved in a local text file using binary stream (see docs). I use a simple text file with a path and once the app starts, read that path to locate the database.
3. Once I have the file location I connect to the DB at app startup. I close connection when app quits.

DB ACCESS:

1. Always check for DB errors after each DB operation. Especially writes. If you receive an error - such as file lock, decide what you want to do; try again, alert the user, etc. I try a few times. Always check for both the message text, and DB error code.
2. Write your code where file writes are quick. Avoid lengthy writes, writing hundreds of records at one time.

SQLite VERSION:

1. No proof of an issue here, but I have always ensured all users are using the same version of SQLite in the app. This is embedded in Xojo, and they will periodically update the version of SQLite in some releases. You can ensure you are consistent by making sure all users are using an app compiled in the SAME version of Xojo.

USER INTERFACE CONSIDERATIONS:

1. This is not specific to SQLite, but any DB application. Just be aware that User 1 loads a record from DB to edit. Let’s it sit open. User 2 opens the same record, makes some changes and saves it. Now, user 1 saves their record and you save all the fields, which will overwrite user 2’s edits.
2. Listboxes are not live, once loaded the data is static. If other users change the data in the DB you’ll need to provide some provision for refresh, if that is important to you. One typical use case; a listbox is shown, user selects an item and the form shows the data to edit. Make sure when you do the original population of the listbox you save the DB record ID in the listbox (I use rowtag). When a user selects the listbox entry make sure you LOAD the data by retrieving current DB record using the record id, not just displaying what was in the listbox cells.

HTH

[Greg_O]

Regardless of the answers given here, you should read the concerns right from the horses mouth…

SQLite Over a Network, Caveats and Considerations

TL;DR

The problems arise from the database engine and the database content being on two separate machines. With Postgres or MySQL, they’re both on the same machine and your app uses a protocol to talk to it. With SQLite, the engine is built into your app and the content is on the network server. This requires that the communication between the two systems needs to be in perfect sync and as is stated in the article, it’s hard to do and shortcuts are often taken for the sake of performance.

What I’m getting at is that other peoples experiences of success should not be considered gospel here. IMHO, the best advice anyone could give is “it’ll work until it doesn’t.”

[Frank_Bicknell]

I have been one of the lucky ones. We used SqlLite in a networked environment for several years. At the peak there were 4 users accessing the tables at the same time, plus occasional remote access. Never had an issue.
The app and the SqlLite file are on the same lan shared drive. We also have all fiber connections.

The horses mouth seems to contradict all that was said in this section

7.0 Transaction Control At The SQL Level.

[Eric_Williams]

To be fair to the horse, it really does know what it is talking about. As Gregg noted, it’s a case of what works for you is great but may not work in a slightly different configuration - or, worse, may fail in a subtle way.

See also: App.DoEvents

[Frank_Bicknell]

Who am I to say ‘the horse’ does not know what they are talking about? That is not what I meant. If you read section 7, and utilize those processes, you just about remove all of the risks.