Should I manage my data in-memory or use SQLite?
-
Hi, all!
I'm currently working on an open-source note-taking app, Vibrato Notes, (source code) and feel like I am at a bit of a crossroads with a software design choice.
When I was creating the main functionality of the app, I starting with a bunch of dummy data (Notes, notebooks, and tags) loaded from JSON files. When you open the app, it will take the data in the JSON, create 'Note', 'Notebook', and 'Tag' instances (classes), and add them to a 'database' object that will manage the objects, send signals, etc.
This has worked fine so far and hasn't caused any noticeable performance problems. I even tested with 1,000 notes. The app startup time was not affected (Noticeably) and the memory usage was 50mb.
Why JSON? I have a Python rest API for cloud-syncing. When a user connects there account, notes will be pulled down in JSON format.
However, this app will also have offline editing capabilities. This means I have to either store a JSON file on the users computer, store some sort of computer-readable object, or use a SQLite database.
Here are some advantages and disadvantages I figure of dealing with everything in memory vs using a database:
Advantages of keeping data in memory and using JSON to store it
- It is very easy for me to filter and sort notes using QSortFilterProxyModel.
- I don't have to worry about SQL queries.
- I get to work with one format - The data sent to the cloud would be in JSON. The data stored on the user's computer would be JSON.
Disadvantages of keeping every note, notebook, and tag in memory:
- Might run into memory usage problems.
Advantages of using a SQLite database:
- Use less memory
- Sorting and filtering can be done in a SQL command.
- My QSortFilterProxyModel would do less work.
Disadvantages of using a SQLite database:
- I'm not sure. A decent amount of work to implement?
-
Hi, all!
I'm currently working on an open-source note-taking app, Vibrato Notes, (source code) and feel like I am at a bit of a crossroads with a software design choice.
When I was creating the main functionality of the app, I starting with a bunch of dummy data (Notes, notebooks, and tags) loaded from JSON files. When you open the app, it will take the data in the JSON, create 'Note', 'Notebook', and 'Tag' instances (classes), and add them to a 'database' object that will manage the objects, send signals, etc.
This has worked fine so far and hasn't caused any noticeable performance problems. I even tested with 1,000 notes. The app startup time was not affected (Noticeably) and the memory usage was 50mb.
Why JSON? I have a Python rest API for cloud-syncing. When a user connects there account, notes will be pulled down in JSON format.
However, this app will also have offline editing capabilities. This means I have to either store a JSON file on the users computer, store some sort of computer-readable object, or use a SQLite database.
Here are some advantages and disadvantages I figure of dealing with everything in memory vs using a database:
Advantages of keeping data in memory and using JSON to store it
- It is very easy for me to filter and sort notes using QSortFilterProxyModel.
- I don't have to worry about SQL queries.
- I get to work with one format - The data sent to the cloud would be in JSON. The data stored on the user's computer would be JSON.
Disadvantages of keeping every note, notebook, and tag in memory:
- Might run into memory usage problems.
Advantages of using a SQLite database:
- Use less memory
- Sorting and filtering can be done in a SQL command.
- My QSortFilterProxyModel would do less work.
Disadvantages of using a SQLite database:
- I'm not sure. A decent amount of work to implement?
@HighMemoryDaemon said in Should I manage my data in-memory or use SQLite?:
Might run into memory usage problems.
Is anybody likely to store more than 1 000 notes? How many more?
Assuming the memory usage raises linearly, you'd reach ~500MB with 10 000 notes. That's not a lot. Most PCs ship with 4-32GB of RAM nowadays.
Besides, you can always tweak your current solution to only keep parts of the data in memory (think of it like cache) and keep the rest on the hard drive. This way you solve the memory "issue" and don't have to rewrite everything to use SQLite.
Disadvantages of keeping every note, notebook, and tag in memory:
A big one: if your app crashes (or PC looses power), you potentially loose everything.
All in all, my opinion: stick with JSON.
-
Thanks!
One thing that might be a problem saving in JSON vs SQLite is that with SQLite you could very easily save a single a single entry. With saving JSON you have to save an entire file over and over again. I'm not entirely sure how a computer saves a file - where if you change a single character in a 1,000 character file does it have to re-write all the 1,000 characters? With SQLite it feels more intuitive to just update a single entry.
I could also just use SQLite as a basic storage and saving mechanism and slowly migrate things over as I feel needed - I could even use your caching idea.
-
Indeed, it would be load/modify/save for your json file.
Hence you can split it and then recombine it before sending it back to your REST service.
Note that your service should allow editing one entry at a time which should also help handling single entries.
-
I have used SQLite successfully in python applications. It is a very convenient data format. In python I used SQLAlchemy which abstracted the database to not have to deal with queries directly. I have not used the SQL interface in Qt, but it does look like it abstracts at least the different database types just like SQLAlchemy does (at least the Qt 5.12 docs show this), but it looks like you have to deal with queries still.
Anyway, you don't have to choose JSON or not JSON. Just store your sub JSON objects in the database and reassemble into higher tier objects. Or if it works out store the whole thing as a database entry. You might also consider abstracting how you communicate with the data store (file) so that QSortFilterProxyModel does not know which format you are using. This might give you more flexibility if you choose another format. Not sure if that makes sense.
Since you are considering SQL can I assume your data format lends to the layout of a database well? So each note could be a database table entry? JSON I think has more flexibility than SQL for structure if I remember right.
For me, I have had many positive experiences with SQLite as a datastore. It is nice and contain just about anything, like blobs of binary data if you really wanted. I just like it. I am biased.
