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;