-
Notifications
You must be signed in to change notification settings - Fork 6
/
migrations.ts
230 lines (203 loc) · 5.94 KB
/
migrations.ts
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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
'use strict'
import {Client} from 'pg'
import * as config from './database'
interface IMigration {
name: string
up: string
down: string
}
const migrations: IMigration[] = [
{
name: 'initial',
up: `
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
create table entities (
did text primary key,
data_count integer not null default 0,
deleted_count integer not null default 0,
blacklisted boolean not null default false
);
create table data (
id integer not null,
did text references entities not null,
cyphertext bytea null,
primary key (id, did)
);
create table deletions (
id integer not null,
data_id integer not null,
did text references entities not null,
signature text null,
primary key (id, did),
foreign key (data_id, did) references data
);
create table access_token (
uuid uuid default gen_random_uuid() primary key,
did text not null references entities,
validated_at timestamp with time zone
);
create table ip_call_count
(
ip varchar(39) not null,
created_at timestamp default now() not null,
updated_at timestamp default now() not null,
endpoint varchar(50) not null,
minute smallint default date_part('minute'::text, CURRENT_TIMESTAMP) not null,
count integer default 1 not null,
constraint ip_call_count_pkey primary key (ip, endpoint)
);
`,
down: `
drop table if exists ip_call_count;
drop table if exists access_token;
drop table if exists deletions;
drop table if exists data;
drop table if exists entities;
`,
},
{
name: 'access-control',
up: `
alter table entities add column admin boolean default false not null;
`,
down: `
alter table entities drop column admin;
`,
},
{
name: 'encrypted-indexes',
up: `
alter table data add column cypherindex bytea null;
`,
down: `
alter table data drop column cypherindex;
`,
},
// Note: This migration intentionally keeps the previous data.cypherindex column
// for historical purposes.
{
name: 'normalized-encrypted-indexes',
up: `
create table data_encrypted_indexes (
data_id integer not null,
data_did text not null,
cipherindex bytea not null,
created_at timestamp default now() not null,
constraint data_fk foreign key (data_id, data_did) references data,
PRIMARY KEY(data_id, data_did, cipherindex)
);
insert into data_encrypted_indexes (data_id, data_did, cipherindex)
select id, did, cypherindex
from data
where cypherindex is not null;
`,
down: `
drop table if exists data_encrypted_indexes;
`,
},
{
name: 'did-casing-compatibility',
up: `
CREATE EXTENSION IF NOT EXISTS citext;
alter table data_encrypted_indexes
alter column data_did set data type citext;
alter table access_token
alter column did set data type citext;
alter table deletions
alter column did set data type citext;
alter table data
alter column did set data type citext;
alter table entities
alter column did set data type citext;
`,
down: `
delete from access_token;
alter table entities
alter column did set data type text;
alter table data
alter column did set data type text;
alter table deletions
alter column did set data type text;
alter table access_token
alter column did set data type text;
alter table data_encrypted_indexes
alter column data_did set data type text;
drop extension if exists citext;
`,
},
{
name: 'add-data_id_and_did-index-on-data_encrypted_indexes',
up: `
create index data_encrypted_indexes_did_and_id on data_encrypted_indexes using btree(data_id, data_did);
create index data_did on data(did);
create index data_did_and_id on data using btree(id, did);
`,
down: `
`,
},
]
export async function up(conf: any, logs: boolean = true) {
const client = new Client(conf)
await client.connect()
logs && console.log('running migrations')
await client.query(
`create table if not exists migrations (name text primary key);`
)
for (const migration of migrations) {
const result = await client.query(
`select name from migrations where name = $1`,
[migration.name]
)
if (result.rowCount !== 0) {
continue
}
logs && console.log('running ' + migration.name)
try {
await client.query('BEGIN')
await client.query(`insert into migrations values ($1);`, [migration.name])
await client.query(migration.up)
await client.query('COMMIT')
} catch (e) {
await client.query('ROLLBACK')
throw e
}
}
await client.end()
}
export async function down(conf: any, logs: boolean = true) {
const client = new Client(conf)
await client.connect()
logs && console.log('reverting migrations')
await client.query(
`create table if not exists migrations (name text primary key);`
)
const reversed = migrations.slice().reverse()
for (const migration of reversed) {
const result = await client.query(
`select name from migrations where name = $1`,
[migration.name]
)
if (result.rowCount === 0) {
continue
}
logs && console.log('reverting ' + migration.name)
try {
await client.query('BEGIN')
await client.query(`delete from migrations where name = $1;`, [migration.name])
await client.query(migration.down)
await client.query('COMMIT')
} catch (e) {
await client.query('ROLLBACK')
throw e
}
}
await client.end()
}
process.on('unhandledRejection', (reason) => {
throw reason
})
if (require.main === module) {
up(config[process.env.NODE_ENV!]).catch((e) => {
throw e
})
}