rodydavis Using SQLite as a Key Value Store
SQLite is a very capable edge database that can store various shapes of data.
Key/Value databases are popular in applications for storing settings, and other non-relational data.
By using SQLite to store the key/values you can contain all the data for a user in a single file and can attach it to other databases or sync it to a server.
Create the table
To store key/value type data we need to first create our table.
CREATE TABLE key_value (
key TEXT NOT NULL PRIMARY KEY,
value,
UNIQUE(key)
);
key
value
user_id
1
foo
bar
active
1
guest
0
SQLite has optional column types and can be very useful for dynamic values.
Save a value
To save a value for a given key we can run the following:
INSERT OR REPLACE
INTO key_value (key, value)
VALUES (:key, :value)
RETURNING *;
key
value
user_id
1
Since the key is UNIQUE we do not have to worry about conflicts as it will overwrite the value as intended.
Read a value
To read a value we can pass in a key to our query:
SELECT value FROM key_value
WHERE key = :key;
value
1
This will only return a single value column with a max of 1 rows.
Delete a value
To delete a value or key we can run the following:
DELETE FROM key_value
WHERE key = :key;
Search for key or value
We can also search for a specific key or value (if it is a string) with the following:
SELECT key, value
FROM key_value
WHERE key LIKE :query
OR value LIKE :query;
key
value
bar
1
foo
bar
Drift Support
If you are using Drift in dart, create a new file key_value.drift and add the following:
CREATE TABLE key_value (
"key" TEXT NOT NULL PRIMARY KEY,
value TEXT,
UNIQUE("key")
);
setItem:
INSERT OR REPLACE
INTO key_value ("key", value)
VALUES (:key, :value)
RETURNING *;
getItem:
SELECT value FROM key_value
WHERE "key" = :key;
deleteItem:
DELETE FROM key_value
WHERE "key" = :key;
searchItem:
SELECT "key", value
FROM key_value
WHERE "key" LIKE :query
OR value LIKE :query;