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 😉

CREATE TABLE IF NOT EXISTS is our initialization query. Since there is no need for something like migration, but we still need to make sure our store is created each time our app starts, we need to create the table ourselves (if it does not exist yet).
store_name well, yes 😅 that’s our store name. But a little precision here, each store is in one table. Which means if the user wants to create multiple stores (maybe a multi domain app), the SQLite database will have multiple tables.
key TEXT PRIMARY KEY a key has to be unique and indexed by the database since that’s our unique way to retrieve elements.
value TEXT NOT NULL the value, a string that will be parsed by javascript to json when we get elements, and we stringify any data that will be stored.
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP do you remember when I said that our app will be local first but with online data sync ? This is to prevent conflict (we will use the last data always wins policy)

And that’s pretty it for the schema, let’s continue to the next step.

Step 2 : Queries

We are going to use the exact same queries again and again every time because the user will only call and use our javascript functions. So all the queries that are going to be used should be prepared and optimized from the beginning.

So… CRUD they say

Upsert item

This is something we’ve all seen when working with database system: create a resource if not exists, update it otherwise. Fortunately, SQL let us do this exact action with a simple query:

INSERT INTO store_name (key, value)
VALUES ('some_key', 'some_value')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;

Simple explanation here:

INSERT INTO store_name (key, value) VALUES ('some_key', 'some_value') Create the resource in the database
ON CONFLICT(key) Ensures that if the key already exists, the value is updated instead of throwing an error.
excluded.value Refers to the new value provided in the query.

Get item

This might be the easiest part, a simple SELECT query, the most basic way:

SELECT value FROM store_name WHERE key = 'some_key';

You nailed it, you big SQL developer 👏🏾.

Delete item

I bet you already guessed this one also:

DELETE FROM store_name WHERE key = 'some_key';

Exactly, you 10+ SQL specialist 🫡.

Some extras

Those actions are great and will surely cover 80% of the use cases our users will face, but let’s give them more possibilities:

Multiple upsert

BEGIN TRANSACTION;
INSERT INTO store_name (key, value)
VALUES ('key1', 'value1')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;
INSERT INTO store_name (key, value)
VALUES ('key2', 'value2')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;
COMMIT;

Get all keys in the store

SELECT key FROM store_name LIMIT 50 OFFSET 0;

Get key-values from some specific keys

SELECT key, value FROM store_name WHERE key LIKE 'user:%';

Get all key-value pair (for what reason ? I don’t know, I don’t make the rules)
```
SELECT key, value FROM store_name LIMIT 50 OFFSET 0;
```

Step 3 : SQLite Executor

Now, we can start writing some Typescript code (what you mean Javascript ? We are civilized here).

I will let you configure your project as you want.

As we said, this tool is meant to be used with SQLite but not a specific one, which means the user will have to provide and configure the SQLite adapter. But we will only need the query executor, so the user should provide a function that follows this structure :

export type SQLiteExecutor = <T=unknown>(query:string,params?:unknown[]) => Promise<T>

That’s all, the rest will be handled by us. So let’s write the core of our project.

Step 4 : Store object

As we said earlier, the first thing we should do is to make sure our table exist, but at the same time, we need the user to provide the store name first for our table name. So how do we do that ?

The constructor and the factory

We create the class that takes the executor and the store name.

import { SQLiteExecutor } from './types'

export default class Store {
  private executor: SQLiteExecutor
  private storeName: string

  constructor(executor: SQLiteExecutor, storeName: string) {
    this.executor = executor
    this.storeName = storeName
  }

  async initStore() {
    const init = `
    CREATE TABLE IF NOT EXISTS ${this.storeName} (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    );
    `

    await this.executor(init)
  }
}

But the user will never instantiate the class directly, instead, the user will use a factory that returns the store object that will be used all over the app (yes, the signleton pattern you OOP master jedi):

import Store from './store'
import { SQLiteExecutor } from './types'

export async function createStore(executor: SQLiteExecutor, storeName: string): Promise<Store> {
  const store = new Store(executor, storeName)
  await store.initStore()

  return store
}

This way, we make sure everything is fine when the user starts using the store.

The functions

Well, everything is ready for us to write the core functions, so here we go:

import { SQLiteExecutor } from './types'

export default class Store {
  ...
  async set(key: string, value: unknown) {
    const stringValue = JSON.stringify(value)
    const insert = `
    INSERT INTO ${this.storeName} (key, value)
    VALUES (?, ?)
    ON CONFLICT(key) DO UPDATE SET value = excluded.value;
    `

    await this.executor(insert, [key, stringValue])
  }

  async get<T>(key: string): Promise<T | undefined> {
    const select = `SELECT value FROM ${this.storeName} WHERE key = ?;`
    const result = await this.executor<Array<{ value: string }>>(select, [key])
    if (result.length === 0) {
      return undefined
    }
    return JSON.parse(result[0].value) as T
  }

  async delete<T>(key: string) {
    const del = `DELETE FROM ${this.storeName} WHERE key = ?`
    await this.executor<T>(del, [key])
  }

  async instertMultiple(items: Array<{ key: string; value: unknown }>) {
    const insert = `
    INSERT INTO ${this.storeName} (key, value)
    VALUES (?, ?)
    ON CONFLICT(key) DO UPDATE SET value = excluded.value;
    `

    await this.executor('BEGIN TRANSACTION;')

    try {
      for (const { key, value } of items) {
        await this.executor(insert, [key, JSON.stringify(value)])
      }
      await this.executor('COMMIT;')
    } catch (error) {
      await this.executor('ROLLBACK;') // Ensure rollback on failure
      throw error
    }
  }

  async getKeys(options?: { limit: number; offset: number }): Promise<string[]> {
    const optionsString = options ? `LIMIT ${options.limit} OFFSET ${options.offset}` : ''
    const select = `SELECT key FROM ${this.storeName} ${optionsString};`
    const result: Array<{ key: string }> = await this.executor(select)
    return result.map(row => row.key)
  }

  async getAll(options?: { limit: number; offset: number }): Promise<Record<string, unknown>> {
    const optionsString = options ? `LIMIT ${options.limit} OFFSET ${options.offset}` : ''
    const select = `SELECT key, value FROM ${this.storeName} ${optionsString};`
    const result: Array<{ key: string; value: string }> = await this.executor(select)
    return result.reduce(
      (acc, row) => {
        acc[row.key] = JSON.parse(row.value)
        return acc
      },
      {} as Record<string, unknown>
    )
  }

  async getSubFields<T>(prefix: string): Promise<Array<{ key: string; value: T }>> {
    const select = `SELECT key, value FROM ${this.storeName} WHERE key LIKE ?;`
    const result: Array<{ key: string; value: T }> = await this.executor(select, [`${prefix}%`])
    return result
  }
}

You can read the code, or just copy past it, it’all yours anyway now.

Ending up

Thank you for following me throughout this journey of madness, breaking the sacred rule of « don’t re-invent the wheel ». If you want to play with the code, you can find it here on Github.

Now, I am going to continue what I was doing, but feel free to give me feed back if you are planning to use the same kv system based on any SQL database too, it would be great to share knowledges like this.
’Till this moment, see you folks.

Bonus part

Of course we can do more, but the main part is already done so you can work with what we did. But if you want more, we can be more generous by giving our users a more complete solution to work with. So let’s create the adapter for the main usage our user may face : expo-sqlite adapter, libsql (Turso) adapter and better-sqlite3 adapter for node.js.

Adapter Factory

A simple type to generate any executor:

export type ExecutorFactory<T> = (db: T) => SQLiteExecutor

And this is the type we are going to use for every adapters:

Adapters

expo-sqlite

import * as ExpoSQLite from 'expo-sqlite'

import { ExecutorFactory } from '../types'

export const createExpoExecutor: ExecutorFactory<ExpoSQLite.SQLiteDatabase> = db => {
  return <T>(query: string, params: unknown[] = []) => {
    if (query.trim().toUpperCase().startsWith('SELECT')) {
      return db.getAllAsync(query, params as ExpoSQLite.SQLiteBindValue[]) as T
    } else {
      return db.runAsync(query, params as ExpoSQLite.SQLiteBindValue[]) as T
    }
  }
}

libsql (turso)

Note that for libsql, we can have an adapter for Node and for edge runtimes (whatever that means):

import * as LibSQLNode from '@libsql/client'
import * as LibSQLWeb from '@libsql/client/web'

import { ExecutorFactory } from '../types'

const createExecutor = <DB extends LibSQLNode.Client>(db: DB) => {
  return async <T>(query: string, params: unknown[] = []) => {
    const result = await db.execute({ sql: query, args: params as LibSQLNode.InArgs })

    return result.rows.map(row => {
      const res: Record<string, unknown> = {}
      Object.keys(row).forEach(key => {
        if (key !== 'length' && typeof key !== 'number') res[key] = row[key]
      })
      return res
    }) as T
  }
}

export const createLibSQLNodeExecutor: ExecutorFactory<LibSQLNode.Client> = createExecutor
export const createLibSQLEdgeExecutor: ExecutorFactory<LibSQLWeb.Client> = createExecutor

better-sqlite3

import BetterSQLite3 from 'better-sqlite3'

import { ExecutorFactory } from '../types'

export const createBetterSqliteExecutor: ExecutorFactory<BetterSQLite3.Database> = db => {
  return <T>(query: string, params: unknown[] = []) => {
    return new Promise<T>((resolve, reject) => {
      try {
        const stmt = db.prepare(query)
        let result
        if (query.trim().toUpperCase().startsWith('SELECT')) {
          result = stmt.all(params)
        } else {
          result = stmt.run(params)
        }
        resolve(result as T)
      } catch (error) {
        reject(error)
      }
    })
  }
}

Conclusion ?

Well, I already did this part. Note that I’m still active in evolving this project because it’s a tool that I (’m planning to) personaly use so you can check the repo here on Github(You can even offer to contribue 😉)

A simple key-value store with SQLite