Multi tended database migration

[willow jasper]

I want to build a SaaS product where each tenant has a separate PostgreSQL database. I need to run migrations for each tenant. How can I do this using a query builder and the PG driver?

[vivid gale]

I'm going to assume you're using TypeORM's QueryBuilder right? If that's the case then take a read through the QueryRunner API here https://typeorm.io/docs/migrations/api

You can generate a migration through the commandtypeorm migration:generate -d <path/to/datasource> <migration-name> or manually.

To use the command line you might also need to add this script to your Package.json file "typeorm": "typeorm-ts-node-commonjs".

I have my datasource set up as follows (You can just call different envs for the databases):

dotenv.config()

import { DataSource } from 'typeorm';


export const AppDataSource = new DataSource({
  type: 'postgres',
  host: process.env.LOCAL_DB_HOST,
  port: Number(process.env.LOCAL_DB_PORT),
  username: process.env.LOCAL_DB_USERNAME,
  password: process.env.LOCAL_DB_PASSWORD,
  database: process.env.LOCAL_DB_DATABASE,
  entities: [__dirname + '/../**/*.entity{.ts,.js}'],
  migrations: [__dirname + '/migrations/*{.ts,.js}'],
  migrationsTableName: "databse-migrations"
});```

[willow jasper]

I use Kysely query builder. current manually run query for database change.

Want when new tended is coming that time create new database and run migration.
Another is if change schema and add new migration file then can update old database also.

[graceful axle]

Does each client have their own instance/server of the server code?

Is db changes automated to deployed version?

If either answer is no, you need to really think about solving those and within solving those you will probably not have to worry about running it per db, and just add a deployment check to confirm db is up to date with deployed code for that customer.