_Rokas_Tenys_Alamy.jpg?#)
A simple key-value store with SQLite
A little context first Hello folks. One day, when working on a side project, I was on the database design step. I knew from the beginning that I did not want to store my data in a relational format but rather as a simple key-value store (even though the data have relations). The thing is, in my opinion, the best service out there to store data is Turso database which is a SQLite database. Also, my project was about a mobile app, local first but with online syncing, so SQLite too. I know Expo provides a simple key-value storage (based on SQLite by the way), but the inconsistency with the local and the online storage would bother me. But hey, since Expo made that key-value storage with SQLite, why not create one that works for both local and online data storage ? Well… let's do it! Step 1 : Data Schema What? What do you mean "data schema"? I thought we were going to create a simple key-value storage? While users of our tool will interact with a simple key-value, schemaless storage, but for us, we will be working with a SQLite database behind the scenes, which requires a strict data schema. To keep things simple, this is our database schema: CREATE TABLE IF NOT EXISTS store_name ( key TEXT PRIMARY KEY, value TEXT NOT NULL, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ); Things are pretty clear here but let’s get it clearer
A little context first
Hello folks. One day, when working on a side project, I was on the database design step. I knew from the beginning that I did not want to store my data in a relational format but rather as a simple key-value store (even though the data have relations). The thing is, in my opinion, the best service out there to store data is Turso database which is a SQLite database. Also, my project was about a mobile app, local first but with online syncing, so SQLite too.
I know Expo provides a simple key-value storage (based on SQLite by the way), but the inconsistency with the local and the online storage would bother me. But hey, since Expo made that key-value storage with SQLite, why not create one that works for both local and online data storage ?
Well… let's do it!
Step 1 : Data Schema
What? What do you mean "data schema"? I thought we were going to create a simple key-value storage?
While users of our tool will interact with a simple key-value, schemaless storage, but for us, we will be working with a SQLite database behind the scenes, which requires a strict data schema.
To keep things simple, this is our database schema:
CREATE TABLE IF NOT EXISTS store_name (
key TEXT PRIMARY KEY,
value TEXT NOT NULL,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
Things are pretty clear here but let’s get it clearer
