How to use multiple database connections?

[tony133]

How to use multiple database connections?

[phuvo]

@Tony133 The issue happens because KnexCoreModule.forRoot is calling getConnectionToken with the string connection parameter to export it (in https://github.com/Tony133/nestjs-knexjs/blob/main/lib/knex-core.module.ts#L33=), yet getConnectionToken only supports a connection object; thus it always returns the default connection string. I've created a MR that extend getConnectionToken to support a string connection (#380), this would fix the issue.

@katsutosh For now the workaround is to call forRoot and InjectConnection as below:

KnexModule.forRoot({ ... }, { name: 'db1Connection' } as any);
@InjectConnection({ name: 'db1Connection' } as any) private readonly knexOne: Knex,

[katsutosh]

thanks both for fix & publish, now works perfect

[Tony133]

I have published the new version: 0.0.9, let me know if everything works correctly so closed then this problem.

[jaimecigna]

If i do solve it first i will make a PR for sure. I've been banging on it but no luck yet. Not sure why hmmm

[Tony133]

Hi @phuvo, thanks for solving this problem 👍, unfortunately I didn't have much time in this period to fix it.

Thank you so much again for contributing 👍

Today I publish a new version of the package so you can tell me if it works correctly.

[Tony133]

Hi @jaimecigna, it should be something like this:

@Module({
  imports: [
    KnexModule.forRoot({
      config: {
        client: 'mysql',
        version: '5.7',
        useNullAsDefault: true,
        connection: {
          host: '127.0.0.1',
          user: 'root',
          port: 3306,
          password: 'root',
          database: 'test1',
        },
      },
    }),
    KnexModule.forRoot({
      config: {
        client: 'mysql',
        version: '5.7',
        useNullAsDefault: true,
        connection: {
          host: '127.0.0.1',
          port: 3307,
          user: 'root',
          password: 'root',
          database: 'test2',
        },
      },
    }),
  ],
  controllers: [],
  providers: [],
})
export class AppModule {}

[jaimecigna]

I tried this, but i'm not sure how to inject the connection into a service on a child module.

do you know how i can import both connections into a service on a child module?

[Tony133]

I have to try it but it should be like this:

@Injectable()
export class UsersService {
  constructor(
    @InjectConnection('test1') private readonly knexConnectionOne: Knex,
    @InjectConnection('test2') private readonly knexConnectionTwo: Knex,
  ) {}

  async findAllOne() {
    const users = await this.knexConnectionOne.table('user');
    return { users };
  }

  async findAllTwo() {
    const users = await this.knexConnectionTwo.table('user');
    return { users };
  }

[jaimecigna]

I tried this:

constructor(
    @InjectConnection('default') protected readonly knex: Knex,
    @InjectConnection('cap') protected readonly cap: Knex,
  ){}
 

I get this error.....hmmm

Error: Nest can't resolve dependencies of the TileService (knex, ?). Please make sure that the argument capConnection at index [1] is available in the TileModule context.

[Tony133]

without parameter in the decorator perhaps it should not give error

@Injectable()
export class UsersService {
  constructor(
    @InjectConnection() private readonly knexConnectionOne: Knex,
    @InjectConnection() private readonly knexConnectionTwo: Knex,
  ) {}

  async findAllOne() {
    const users = await this.knexConnectionOne.table('user');
    return { users };
  }

  async findAllTwo() {
    const users = await this.knexConnectionTwo.table('user');
    return { users };
  }
}
´´´

[jaimecigna]

Without a parameter in the decorator it doesn't give an error, but then how does it know which connection to use??

the parameter is to specify the connection

[jaimecigna]

looking at the docs: https://docs.nestjs.com/techniques/database#multiple-databases-1

The KnexModule.forFeature() is missing from the KnexModule itself.

[Tony133]

I have to review it a bit, I created it in a bit of a hurry for a job with Node.js maybe I missed something while creating the module. Knex as a setting is a bit different from Typeorm

[jaimecigna]

thanks for your help! if you do figure something out please let me know!

[Tony133]

certainly, thanks for reporting!

[Tony133]

Hi @jaimecigna, I made some changes to the package since I had some free time and now you should be able to use the multi connection with Knex.

I created a new version 0.0.6 and also updated the README.md with the instructions

At the moment I leave this problem still open for any problems on multiple connections with knex.

[jaimecigna]

Thanks for adding this, however it doesn't seem to pickup on the 2nd connection when i use the @InjectConnection. The connection still seems to be set to the default one for some reason

[Tony133]

Thanks for the report, strange with the tests it seems to work, as soon as I find more free time I will do other checks.

[jaimecigna]

Thanks! FYI: i'm using postgres

[jaimecigna]

any update on this?

[Tony133]

unfortunately I am evaluating how to solve it yet it is strange because doing the tests e2e writes on two different databases, so the thing is correct only in a normal application it seems not to work.

However, if you should be able to solve it first, make a pull request if you want.

[katsutosh]

hey,
i have a similar problem with mutli database connections.
Me using mysql/mariadb
Creating the connection in my app.module file with KnexModule.forRoot

        KnexModule.forRoot({
                config: {
                    client: 'mysql',
                    connection: {
                        host: process.env.DATABASE_HOST,
                        user: process.env.DATABASE_USER,
                        password: process.env.DATABASE_PASSWORD,
                        database: 'element-main'
                    }
                }
            },
            'db1Connection'),
        KnexModule.forRoot({
                config: {
                    client: 'mysql',
                    connection: {
                        host: process.env.DATABASE_HOST,
                        user: process.env.DATABASE_USER,
                        password: process.env.DATABASE_PASSWORD,
                        database: 'element-cp'
                    }
                }
            },
            'db2Connection'),

When i try to use db2Connection in a .service file its not useing the connection in the decorator @InjectConnection('db2Connection'), It use always the first created connection.
To verify it, just log the knexCP.client

export class MyService {
    constructor(
@InjectConnection('db1Connection') private readonly knexOne: Knex,
        @InjectConnection('db2Connection') private readonly knexTwo: Knex) {
    }

    myFunction() {
        console.log(this.knexTwo.client);
    }
}