danabra.mov
forked from
samuel.fm/statusphere-react
the statusphere demo reworked into a vite/react app in a monorepo
samuel.fm
1import SqliteDb from 'better-sqlite3'
2import {
3 Kysely,
4 Migration,
5 MigrationProvider,
6 Migrator,
7 SqliteDialect,
8} from 'kysely'
9
10// Types
11
12export type DatabaseSchema = {
13 status: Status
14 auth_session: AuthSession
15 auth_state: AuthState
16 cursor: Cursor
17}
18
19export type Status = {
20 uri: string
21 authorDid: string
22 status: string
23 createdAt: string
24 indexedAt: string
25}
26
27export type AuthSession = {
28 key: string
29 session: AuthSessionJson
30}
31
32export type AuthState = {
33 key: string
34 state: AuthStateJson
35}
36
37export type Cursor = {
38 id: number
39 seq: number
40}
41
42type AuthStateJson = string
43
44type AuthSessionJson = string
45
46// Migrations
47
48const migrations: Record<string, Migration> = {}
49
50const migrationProvider: MigrationProvider = {
51 async getMigrations() {
52 return migrations
53 },
54}
55
56migrations['003'] = {
57 async up(db: Kysely<unknown>) {},
58 async down(_db: Kysely<unknown>) {},
59}
60
61migrations['002'] = {
62 async up(db: Kysely<unknown>) {
63 await db.schema
64 .createTable('cursor')
65 .addColumn('id', 'integer', (col) => col.primaryKey())
66 .addColumn('seq', 'integer', (col) => col.notNull())
67 .execute()
68
69 // Insert initial cursor values:
70 // id=1 is for firehose, id=2 is for jetstream
71 await db
72 .insertInto('cursor' as never)
73 .values([
74 { id: 1, seq: 0 },
75 { id: 2, seq: 0 },
76 ])
77 .execute()
78 },
79 async down(db: Kysely<unknown>) {
80 await db.schema.dropTable('cursor').execute()
81 },
82}
83
84migrations['001'] = {
85 async up(db: Kysely<unknown>) {
86 await db.schema
87 .createTable('status')
88 .addColumn('uri', 'varchar', (col) => col.primaryKey())
89 .addColumn('authorDid', 'varchar', (col) => col.notNull())
90 .addColumn('status', 'varchar', (col) => col.notNull())
91 .addColumn('createdAt', 'varchar', (col) => col.notNull())
92 .addColumn('indexedAt', 'varchar', (col) => col.notNull())
93 .execute()
94 await db.schema
95 .createTable('auth_session')
96 .addColumn('key', 'varchar', (col) => col.primaryKey())
97 .addColumn('session', 'varchar', (col) => col.notNull())
98 .execute()
99 await db.schema
100 .createTable('auth_state')
101 .addColumn('key', 'varchar', (col) => col.primaryKey())
102 .addColumn('state', 'varchar', (col) => col.notNull())
103 .execute()
104 },
105 async down(db: Kysely<unknown>) {
106 await db.schema.dropTable('auth_state').execute()
107 await db.schema.dropTable('auth_session').execute()
108 await db.schema.dropTable('status').execute()
109 },
110}
111
112// APIs
113
114export const createDb = (location: string): Database => {
115 return new Kysely<DatabaseSchema>({
116 dialect: new SqliteDialect({
117 database: new SqliteDb(location),
118 }),
119 })
120}
121
122export const migrateToLatest = async (db: Database) => {
123 const migrator = new Migrator({ db, provider: migrationProvider })
124 const { error } = await migrator.migrateToLatest()
125 if (error) throw error
126}
127
128export type Database = Kysely<DatabaseSchema>