Switch sqlite database at runtime with nestjs and typeorm

Question

I have build an nestjs+typeorm backend that uses a single sqlite db.

Now I want to use multiple sqlite database files for my project. All the database files will be exactly the same. I want to give the user a property that tells me which database to use. I can get this information at my validation function or everywhere else from my @User() object but how can I tell nestjs/typeorm to use a specific database file?

Currently I set the database in the app.module.ts inside imports like that:

imports: [
    ...
    TypeOrmModule.forRootAsync({
      useFactory: () => {
        return {
          type: 'sqlite',
          database: __dirname + '/db/database.db',
          keepConnectionAlive: true,
          entities: ['dist/**/entities/*.entity{.ts,.js}'],
          migrations: ['dist/db/migrations/*{.ts,.js}'],
          synchronize: true,
          logging: false,
        };
      },
    }),
    ...
]

But how can I change this within an function? Or better, with an controller decorator that automaticly takes user.table and sets the database to use.

---

Edit: I found this github issue where a user asked the same question but for mysql. I tryed to copy the first example and changed my code:

app.modul.ts stayed untouched and currently just loads my default database.

I created a getDbConnection() function that takes my payload token and creates or returns an already established connection for that specific database:

import { PayloadToken } from 'src/auth/models/token.model';
import { ConnectionOptions, createConnection, getConnection } from 'typeorm';

export const getDbConnection = async (user: PayloadToken) => {
  user.db = 'new';
  try {
    return getConnection(user.db);
  } catch (err) {
    // err = no connection with that name was found.
  }

  const options: ConnectionOptions = {
    name: user.db, // name will be 'new'
    type: 'sqlite',
    entities: ['dist/**/entities/*.entity{.ts,.js}'],
    migrations: ['dist/db/migrations/*{.ts,.js}'],
    database: __dirname + `/db/${user.db}.db`, // db file will be 'new.db'
    synchronize: true,
    logging: false,
  }
  console.log('New connection!');
  
  return createConnection(options);
};

After that I replaced an function that finds me a single player inside my player.service.ts:

From:

return this.playerRepository.findOne(id);

To:

return (await getDbConnection(user)).manager.findOne(id);

But this will resolve to an error (The UUID is the id I want to query for):

[Nest] 12072  - 14.02.2024, 10:17:54   ERROR [ExceptionsHandler] No metadata for "9fd8b32f-b058-4c37-b099-ea0f67e17e83" was found.
EntityMetadataNotFoundError: No metadata for "9fd8b32f-b058-4c37-b099-ea0f67e17e83" was found.
    at EntityMetadataNotFoundError.TypeORMError [as constructor] (C:\Users\Me\Desktop\myProject\Backend\TestVersion\src\error\TypeORMError.ts:7:9)       
    at new EntityMetadataNotFoundError (C:\Users\Me\Desktop\myProject\Backend\TestVersion\src\error\EntityMetadataNotFoundError.ts:7:9)
    at Connection.getMetadata (C:\Users\Me\Desktop\myProject\Backend\TestVersion\src\connection\Connection.ts:345:19)
    at EntityManager.<anonymous> (C:\Users\Me\Desktop\myProject\Backend\TestVersion\src\entity-manager\EntityManager.ts:810:42)
    at step (C:\Users\Me\Desktop\myProject\Backend\TestVersion\node_modules\tslib\tslib.js:144:27)
    at Object.next (C:\Users\Me\Desktop\myProject\Backend\TestVersion\node_modules\tslib\tslib.js:125:57)
    at C:\Users\Me\Desktop\myProject\Backend\TestVersion\node_modules\tslib\tslib.js:118:75
    at new Promise (<anonymous>)
    at __awaiter (C:\Users\Me\Desktop\myProject\Backend\TestVersion\node_modules\tslib\tslib.js:114:16)
    at EntityManager.findOne (C:\Users\Me\Desktop\myProject\Backend\TestVersion\node_modules\typeorm\entity-manager\EntityManager.js:524:38)

I think it has something to do with the paths beeing wrong inside the ConnectionOptions so I did try to change them to:

entities: [__dirname + '/../../**/entities/*.entity{.ts,.js}'],
migrations: [__dirname + '/../../db/migrations/*{.ts,.js}'],
database: __dirname + `/../../../db/${user.db}.db`,

since my __dirname is rootDir/dist/src/db-management this should point to the same location as if I run this from app.module.ts where I copied those paths from but I still get the same error message.

Answer 1

I got it.

I did create an dbManager.ts with the code:

import { PayloadToken } from 'src/auth/models/token.model';
import { ConnectionOptions, createConnection, EntityTarget, getConnection } from 'typeorm';

const getDbConnection = async (user: PayloadToken) => {
  user.db = 'test';
  try {
    return getConnection(user.db);
  } catch (err) {
    //  err = no connection with that name was found.
  }

  const options: ConnectionOptions = {
    name: user.db,
    type: 'sqlite',
    entities: [__dirname + '/../../**/entities/*.entity{.ts,.js}'],
    migrations: [__dirname + '/../../db/migrations/*{.ts,.js}'],
    database: __dirname + `/../../../db/${user.db}.db`,
    synchronize: true,
    logging: false,
  }
  console.log(`New DB connection to ${user.db}!`);
  
  return createConnection(options);
};

export async function getDbRepository<Entity>(user: PayloadToken, entity: EntityTarget<Entity>) {
  return (await getDbConnection(user)).manager.getRepository(entity);
}

The getDbConnection function will return a new or already established Connection with the name of user.db.

Now wherever I want to use not the default db, I need to replace my repository calls with the new getDbRepository from the dbMangert.ts.

For example:

this.playerRepository.findOne(id);

will be

(await getDbRepository(user, Player)).findOne(id); // Player is the entity

Thats my current way to do it. If there is any better or more optimal way please tell me.