1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
// spellchecker: words sqlocal kysely insertable updateable
import { SQLocalKysely } from "sqlocal/kysely";
import { Generated, Insertable, Kysely, Migration, Migrator } from "kysely";
const tableNames = {
mail: {
table: "mail",
columns: {
id: "id",
messageId: "message_id",
awsMessageId: "aws_message_id",
raw: "raw",
},
},
} as const;
interface MailTable {
[tableNames.mail.columns.id]: Generated<number>;
[tableNames.mail.columns.messageId]: string;
[tableNames.mail.columns.awsMessageId]: string | null;
[tableNames.mail.columns.raw]: string;
}
interface Database {
[tableNames.mail.table]: MailTable;
}
const migrations: Record<string, Migration> = {
"0001-init": {
// deno-lint-ignore no-explicit-any
async up(db: Kysely<any>): Promise<void> {
const names = tableNames.mail;
await db.schema
.createTable(names.table)
.addColumn(
names.columns.id,
"integer",
(col) => col.primaryKey().autoIncrement(),
)
.addColumn(
names.columns.messageId,
"text",
(col) => col.notNull().unique(),
)
.addColumn(names.columns.awsMessageId, "text", (col) => col.unique())
.addColumn(names.columns.raw, "text", (col) => col.notNull())
.execute();
for (
const column of [names.columns.messageId, names.columns.awsMessageId]
) {
await db.schema
.createIndex(`${names.table}_${column}`)
.on(names.table)
.column(column)
.execute();
}
},
// deno-lint-ignore no-explicit-any
async down(db: Kysely<any>): Promise<void> {
await db.schema.dropTable(tableNames.mail.table).execute();
},
},
};
export class DbService {
private _sqlocal;
private _db;
private _migrator;
constructor(public readonly path: string) {
this._sqlocal = new SQLocalKysely("database.sqlite3");
const db = new Kysely<Database>({ dialect: this._sqlocal.dialect });
this._db = db;
this._migrator = new Migrator({
db,
provider: {
getMigrations(): Promise<Record<string, Migration>> {
return Promise.resolve(migrations);
},
},
});
}
async migrate(): Promise<void> {
await this._migrator.migrateToLatest();
}
async addMail(mail: Insertable<MailTable>): Promise<void> {
await this._db.insertInto(tableNames.mail.table).values(mail)
.executeTakeFirstOrThrow();
}
async messageIdToAws(messageId: string): Promise<string | null> {
const row = await this._db.selectFrom(tableNames.mail.table).where(
tableNames.mail.columns.messageId,
"=",
messageId,
).select(tableNames.mail.columns.awsMessageId).executeTakeFirst();
return row?.aws_message_id ?? null;
}
async messageIdFromAws(awsMessageId: string): Promise<string | null> {
const row = await this._db.selectFrom(tableNames.mail.table).where(
tableNames.mail.columns.awsMessageId,
"=",
awsMessageId,
).select(tableNames.mail.columns.messageId).executeTakeFirst();
return row?.message_id ?? null;
}
}
|
