The Database
This page will stand as a technical reference for the database. It is not required to read or understand any of this to use Basemix in the same way that you don't have to understand how any application stores its data in order to make use of it, this section serves the more technically-curious and should stand as a reference to future-proof your data. I do not plan on disappearing or stopping development anytime soon, nor do I plan on letting the app fall into disrepair. Either way, contingency plans are always a good idea.
Again, I do not recommend directly editing or opening the database file because there's simply no good reason to if you use Basemix. You should be able to achieve everything you need through the app itself (and if you can't, let me know!). This page just exists for long-term safety, peace of mind, and the very curious.
Overview
The data is stored in a single SQLite database file, which is an incredibly popular and well supported file format. This means that no matter what happens to Basemix, there exists plenty of tools out there capable of opening this file up and accessing the raw data (granted, not in the most user-friendly way, but it's there). It should be stored in a "basemix" folder in your documents folder, depending on platform, called "db.sqlite".
| Platform | Location | Example |
|---|---|---|
| Windows | {drive}\Users\{username}\Documents\basemix | C:\Users\rory\Documents\basemix |
| macOS | /Users/{username}/Documents/basemix | /Users/rory/Documents/basemix |
| Android | Android stores app data in a special hidden folder. You cannot directly access it. | /storage/emulated/0/basemix |
The database schema (I.E. the structure of data inside) changes over time as new features are added. Newer databases are not compatible with older versions of Basemix but older databases will be safely upgraded when loaded by Basemix. Version information is stored in the database itself as a list of upgrade scripts which have been run against it, this information isn't very useful outside of Basemix but it's worth knowing what it is for. Because the structure changes often, it won't be documented here and will require some intuition to explore.
Basemix is designed to have sole control over the "basemix/db.sqlite" file. You should never modify or access this file while the application is running, nor is it necessarily safe to modify at all. I suggest exporting a backup file (which is a plain copy of the full database) if you want to use tools to explore the database.
Data types
SQLite stores information in very simple data types and it is up to the application to choose how best to save and represent information. Text is stored quite plainly, other types may be less obvious if directly accessing the database. Here's how basemix represents various data in the database:
| Type | Stored as | Example (stored) | Example (real value) | Notes |
|---|---|---|---|---|
| Dates & times | UNIX Timestamp | 1687365811 | Wed Jun 21 2023 16:43:31 GMT+0000 | This is the number of seconds that have passed since Jan 01 1970. Storing as a number makes it easier to perform calculations with rather than storing it as a timestamp. Many good converters exist to make sense of these numbers E.G. unixtimestamp.com |
| Boolean (True/False) | 0 or 1 | 1 | True (or a ticked checkbox) | 0 represents "false" and any other value represents "true" |
Opening the database
I recommend the following tools for exploring the database, but a search for "Open SQLite file" will find many more equally capable tools:
- DBeaver (Windows, macOS)
- SQLite Editor Master (android)
Other notes
Why is the working file called "db.sqlite" but the exported database called "basemix.sqlite3"?
An error on my part. The database file is a sqlite3 file but I gave it the unversioned extension in early development, and released the first versions with that name.
The good news is, the extension doesn't really matter, the exported files all have a more sensible name vs the app database (which you shouldn't directly touch anyway) and most sqlite tools will be able to open it regardless of extension.