A decentralized music tracking and discovery platform built on AT Protocol 馃幍
rocksky.app
spotify
atproto
lastfm
musicbrainz
scrobbling
listenbrainz
1import Database from "better-sqlite3";
2import { Kysely, SqliteDialect } from "kysely";
3import { defineDriver } from "unstorage";
4
5interface TableSchema {
6 [k: string]: {
7 id: string;
8 value: string;
9 created_at: string;
10 updated_at: string;
11 };
12}
13
14export type KvDb = Kysely<TableSchema>;
15
16const DRIVER_NAME = "sqlite";
17
18export default defineDriver<
19 {
20 location?: string;
21 table: string;
22 getDb?: () => KvDb;
23 },
24 KvDb
25>(
26 ({
27 location,
28 table = "kv",
29 getDb = (): KvDb => {
30 let _db: KvDb | null = null;
31
32 return (() => {
33 if (_db) {
34 return _db;
35 }
36
37 if (!location) {
38 throw new Error("SQLite location is required");
39 }
40
41 const sqlite = new Database(location, { fileMustExist: false });
42
43 // Enable WAL mode
44 sqlite.pragma("journal_mode = WAL");
45
46 _db = new Kysely<TableSchema>({
47 dialect: new SqliteDialect({
48 database: sqlite,
49 }),
50 });
51
52 // Create table if not exists
53 _db.schema
54 .createTable(table)
55 .ifNotExists()
56 .addColumn("id", "text", (col) => col.primaryKey())
57 .addColumn("value", "text", (col) => col.notNull())
58 .addColumn("created_at", "text", (col) => col.notNull())
59 .addColumn("updated_at", "text", (col) => col.notNull())
60 .execute();
61
62 return _db;
63 })();
64 },
65 }) => {
66 return {
67 name: DRIVER_NAME,
68 options: { location, table },
69 getInstance: getDb,
70
71 async hasItem(key) {
72 const result = await getDb()
73 .selectFrom(table)
74 .select(["id"])
75 .where("id", "=", key)
76 .executeTakeFirst();
77 return !!result;
78 },
79
80 async getItem(key) {
81 const result = await getDb()
82 .selectFrom(table)
83 .select(["value"])
84 .where("id", "=", key)
85 .executeTakeFirst();
86 return result?.value ?? null;
87 },
88
89 async setItem(key: string, value: string) {
90 const now = new Date().toISOString();
91 await getDb()
92 .insertInto(table)
93 .values({
94 id: key,
95 value,
96 created_at: now,
97 updated_at: now,
98 })
99 .onConflict((oc) =>
100 oc.column("id").doUpdateSet({
101 value,
102 updated_at: now,
103 }),
104 )
105 .execute();
106 },
107
108 async setItems(items) {
109 const now = new Date().toISOString();
110
111 await getDb()
112 .transaction()
113 .execute(async (trx) => {
114 await Promise.all(
115 items.map(({ key, value }) => {
116 return trx
117 .insertInto(table)
118 .values({
119 id: key,
120 value,
121 created_at: now,
122 updated_at: now,
123 })
124 .onConflict((oc) =>
125 oc.column("id").doUpdateSet({
126 value,
127 updated_at: now,
128 }),
129 )
130 .execute();
131 }),
132 );
133 });
134 },
135
136 async removeItem(key: string) {
137 await getDb().deleteFrom(table).where("id", "=", key).execute();
138 },
139
140 async getMeta(key: string) {
141 const result = await getDb()
142 .selectFrom(table)
143 .select(["created_at", "updated_at"])
144 .where("id", "=", key)
145 .executeTakeFirst();
146 if (!result) {
147 return null;
148 }
149 return {
150 birthtime: new Date(result.created_at),
151 mtime: new Date(result.updated_at),
152 };
153 },
154
155 async getKeys(base = "") {
156 const results = await getDb()
157 .selectFrom(table)
158 .select(["id"])
159 .where("id", "like", `${base}%`)
160 .execute();
161 return results.map((r) => r.id);
162 },
163
164 async clear() {
165 await getDb().deleteFrom(table).execute();
166 },
167
168 async dispose() {
169 await getDb().destroy();
170 },
171 };
172 },
173);