From 34e2392b489d5e3250ceecc360f54104ec014f97 Mon Sep 17 00:00:00 2001 From: Flam3rboy <34555296+Flam3rboy@users.noreply.github.com> Date: Fri, 15 Oct 2021 18:39:19 +0200 Subject: :sparkles: automatically run db migrations --- util/scripts/migrate_db_engine.js | 109 ++++++++++++++++++++++ util/src/entities/Migration.ts | 18 ++++ util/src/entities/index.ts | 1 + util/src/migrations/1633881705509-VanityInvite.ts | 2 + util/src/migrations/1634308884591-Stickers.ts | 66 +++++++++++++ util/src/migrations/migrate_db_engine.js | 109 ---------------------- util/src/util/Database.ts | 20 +++- 7 files changed, 215 insertions(+), 110 deletions(-) create mode 100644 util/scripts/migrate_db_engine.js create mode 100644 util/src/entities/Migration.ts create mode 100644 util/src/migrations/1634308884591-Stickers.ts delete mode 100644 util/src/migrations/migrate_db_engine.js diff --git a/util/scripts/migrate_db_engine.js b/util/scripts/migrate_db_engine.js new file mode 100644 index 00000000..79e9d86f --- /dev/null +++ b/util/scripts/migrate_db_engine.js @@ -0,0 +1,109 @@ +const { config } = require("dotenv"); +config(); +const { createConnection } = require("typeorm"); +const { initDatabase } = require("../../dist/util/Database"); +require("missing-native-js-functions"); +const { + Application, + Attachment, + Ban, + Channel, + ConfigEntity, + ConnectedAccount, + Emoji, + Guild, + Invite, + Member, + Message, + ReadState, + Recipient, + Relationship, + Role, + Sticker, + Team, + TeamMember, + Template, + User, + VoiceState, + Webhook, +} = require("../../dist/entities/index"); + +async function main() { + if (!process.env.TO) throw new Error("TO database env connection string not set"); + + // manually arrange them because of foreign keys + const entities = [ + ConfigEntity, + User, + Guild, + Channel, + Invite, + Role, + Ban, + Application, + Emoji, + ConnectedAccount, + Member, + ReadState, + Recipient, + Relationship, + Sticker, + Team, + TeamMember, + Template, + VoiceState, + Webhook, + Message, + Attachment, + ]; + + const oldDB = await initDatabase(); + + const type = process.env.TO.includes("://") ? process.env.TO.split(":")[0]?.replace("+srv", "") : "sqlite"; + const isSqlite = type.includes("sqlite"); + + // @ts-ignore + const newDB = await createConnection({ + type, + url: isSqlite ? undefined : process.env.TO, + database: isSqlite ? process.env.TO : undefined, + entities, + name: "new", + synchronize: true, + }); + let i = 0; + + try { + for (const entity of entities) { + const entries = await oldDB.manager.find(entity); + + // @ts-ignore + console.log("migrating " + entries.length + " " + entity.name + " ..."); + + for (const entry of entries) { + console.log(i++); + + try { + await newDB.manager.insert(entity, entry); + } catch (error) { + try { + if (!entry.id) throw new Error("object doesn't have a unique id: " + entry); + await newDB.manager.update(entity, { id: entry.id }, entry); + } catch (error) { + console.error("couldn't migrate " + i + " " + entity.name, error); + } + } + } + + // @ts-ignore + console.log("migrated " + entries.length + " " + entity.name); + } + } catch (error) { + console.error(error.message); + } + + console.log("SUCCESS migrated all data"); + await newDB.close(); +} + +main().caught(); diff --git a/util/src/entities/Migration.ts b/util/src/entities/Migration.ts new file mode 100644 index 00000000..09df70fb --- /dev/null +++ b/util/src/entities/Migration.ts @@ -0,0 +1,18 @@ +import { Column, Entity, ObjectIdColumn, PrimaryGeneratedColumn } from "typeorm"; +import { BaseClassWithoutId } from "."; + +export const PrimaryIdAutoGenerated = process.env.DATABASE?.startsWith("mongodb") + ? ObjectIdColumn + : PrimaryGeneratedColumn; + +@Entity("migrations") +export class Migration extends BaseClassWithoutId { + @PrimaryIdAutoGenerated() + id: number; + + @Column() + timestamp: number; + + @Column() + name: string; +} diff --git a/util/src/entities/index.ts b/util/src/entities/index.ts index 31afed88..b52841c9 100644 --- a/util/src/entities/index.ts +++ b/util/src/entities/index.ts @@ -11,6 +11,7 @@ export * from "./Guild"; export * from "./Invite"; export * from "./Member"; export * from "./Message"; +export * from "./Migration"; export * from "./RateLimit"; export * from "./ReadState"; export * from "./Recipient"; diff --git a/util/src/migrations/1633881705509-VanityInvite.ts b/util/src/migrations/1633881705509-VanityInvite.ts index af9b98ae..45485310 100644 --- a/util/src/migrations/1633881705509-VanityInvite.ts +++ b/util/src/migrations/1633881705509-VanityInvite.ts @@ -1,6 +1,8 @@ import { MigrationInterface, QueryRunner } from "typeorm"; export class VanityInvite1633881705509 implements MigrationInterface { + name = "VanityInvite1633881705509"; + public async up(queryRunner: QueryRunner): Promise { try { await queryRunner.query(`ALTER TABLE "emojis" DROP COLUMN vanity_url_code`); diff --git a/util/src/migrations/1634308884591-Stickers.ts b/util/src/migrations/1634308884591-Stickers.ts new file mode 100644 index 00000000..fbc4649f --- /dev/null +++ b/util/src/migrations/1634308884591-Stickers.ts @@ -0,0 +1,66 @@ +import { MigrationInterface, QueryRunner, Table, TableColumn, TableForeignKey } from "typeorm"; + +export class Stickers1634308884591 implements MigrationInterface { + name = "Stickers1634308884591"; + + public async up(queryRunner: QueryRunner): Promise { + await queryRunner.dropForeignKey("read_states", "FK_6f255d873cfbfd7a93849b7ff74"); + await queryRunner.changeColumn( + "stickers", + "tags", + new TableColumn({ name: "tags", type: "varchar", isNullable: true }) + ); + await queryRunner.changeColumn( + "stickers", + "pack_id", + new TableColumn({ name: "pack_id", type: "varchar", isNullable: true }) + ); + await queryRunner.changeColumn("stickers", "type", new TableColumn({ name: "type", type: "integer" })); + await queryRunner.changeColumn( + "stickers", + "format_type", + new TableColumn({ name: "format_type", type: "integer" }) + ); + await queryRunner.changeColumn( + "stickers", + "available", + new TableColumn({ name: "available", type: "boolean", isNullable: true }) + ); + await queryRunner.changeColumn( + "stickers", + "user_id", + new TableColumn({ name: "user_id", type: "boolean", isNullable: true }) + ); + await queryRunner.createForeignKey( + "stickers", + new TableForeignKey({ + name: "FK_8f4ee73f2bb2325ff980502e158", + columnNames: ["user_id"], + referencedColumnNames: ["id"], + referencedTableName: "users", + onDelete: "CASCADE", + }) + ); + await queryRunner.createTable( + new Table({ + name: "sticker_packs", + columns: [ + new TableColumn({ name: "id", type: "varchar", isPrimary: true }), + new TableColumn({ name: "name", type: "varchar" }), + new TableColumn({ name: "description", type: "varchar", isNullable: true }), + new TableColumn({ name: "banner_asset_id", type: "varchar", isNullable: true }), + new TableColumn({ name: "cover_sticker_id", type: "varchar", isNullable: true }), + ], + foreignKeys: [ + new TableForeignKey({ + columnNames: ["cover_sticker_id"], + referencedColumnNames: ["id"], + referencedTableName: "stickers", + }), + ], + }) + ); + } + + public async down(queryRunner: QueryRunner): Promise {} +} diff --git a/util/src/migrations/migrate_db_engine.js b/util/src/migrations/migrate_db_engine.js deleted file mode 100644 index 79e9d86f..00000000 --- a/util/src/migrations/migrate_db_engine.js +++ /dev/null @@ -1,109 +0,0 @@ -const { config } = require("dotenv"); -config(); -const { createConnection } = require("typeorm"); -const { initDatabase } = require("../../dist/util/Database"); -require("missing-native-js-functions"); -const { - Application, - Attachment, - Ban, - Channel, - ConfigEntity, - ConnectedAccount, - Emoji, - Guild, - Invite, - Member, - Message, - ReadState, - Recipient, - Relationship, - Role, - Sticker, - Team, - TeamMember, - Template, - User, - VoiceState, - Webhook, -} = require("../../dist/entities/index"); - -async function main() { - if (!process.env.TO) throw new Error("TO database env connection string not set"); - - // manually arrange them because of foreign keys - const entities = [ - ConfigEntity, - User, - Guild, - Channel, - Invite, - Role, - Ban, - Application, - Emoji, - ConnectedAccount, - Member, - ReadState, - Recipient, - Relationship, - Sticker, - Team, - TeamMember, - Template, - VoiceState, - Webhook, - Message, - Attachment, - ]; - - const oldDB = await initDatabase(); - - const type = process.env.TO.includes("://") ? process.env.TO.split(":")[0]?.replace("+srv", "") : "sqlite"; - const isSqlite = type.includes("sqlite"); - - // @ts-ignore - const newDB = await createConnection({ - type, - url: isSqlite ? undefined : process.env.TO, - database: isSqlite ? process.env.TO : undefined, - entities, - name: "new", - synchronize: true, - }); - let i = 0; - - try { - for (const entity of entities) { - const entries = await oldDB.manager.find(entity); - - // @ts-ignore - console.log("migrating " + entries.length + " " + entity.name + " ..."); - - for (const entry of entries) { - console.log(i++); - - try { - await newDB.manager.insert(entity, entry); - } catch (error) { - try { - if (!entry.id) throw new Error("object doesn't have a unique id: " + entry); - await newDB.manager.update(entity, { id: entry.id }, entry); - } catch (error) { - console.error("couldn't migrate " + i + " " + entity.name, error); - } - } - } - - // @ts-ignore - console.log("migrated " + entries.length + " " + entity.name); - } - } catch (error) { - console.error(error.message); - } - - console.log("SUCCESS migrated all data"); - await newDB.close(); -} - -main().caught(); diff --git a/util/src/util/Database.ts b/util/src/util/Database.ts index 8bce3a6f..6124ffab 100644 --- a/util/src/util/Database.ts +++ b/util/src/util/Database.ts @@ -2,6 +2,7 @@ import path from "path"; import "reflect-metadata"; import { Connection, createConnection } from "typeorm"; import * as Models from "../entities"; +import { Migration } from "../entities/Migration"; import { yellow, green } from "nanocolors"; // UUID extension option is only supported with postgres @@ -33,10 +34,27 @@ export function initDatabase(): Promise { bigNumberStrings: false, supportBigNumbers: true, name: "default", + migrations: [path.join(__dirname, "..", "migrations", "*.js")], }); - promise.then((connection) => { + promise.then(async (connection: Connection) => { dbConnection = connection; + + // run migrations, and if it is a new fresh database, set it to the last migration + if (connection.migrations.length) { + if (!(await Migration.findOne({}))) { + let i = 0; + + await Migration.insert( + connection.migrations.map((x) => ({ + id: i++, + name: x.name, + timestamp: Date.now(), + })) + ); + } + } + await connection.runMigrations(); console.log(`[Database] ${green("connected")}`); }); -- cgit 1.4.1