preferReadReplica not respected with createQueryBuilder for Postgres

[jtuppy]

First, just want to say I am a huge fan of this library, it is very well done and thank you for the hard work making this for the community.

Describe the bug

I have a question about understanding how connection type is being decided and the preferReadReplica option. We wanted to gradually start adopting a read replica for certain queries in our app so wanted to leverage the preferReadReplica: false to selectively choose which queries would start using it. It appears that this option wasn't respected when using query builder, because we were hitting the read replica when none of our queries should have been using it.

Since we use the PostgresSqlDriver, it seems like the connectionType is not specified when using createQueryBuilder from the EntityManager.

It seems the createQueryBuilder function in AbstractSqlDriver respects these options when calling resolveConnectionType

mikro-orm/packages/knex/src/AbstractSqlDriver.ts

Lines 871 to 880 in 5a24ced

	createQueryBuilder<T extends object>(entityName: EntityName<T> | QueryBuilder<T>, ctx?: Transaction<Knex.Transaction>, preferredConnectionType?: ConnectionType, convertCustomTypes?: boolean): QueryBuilder<T> {
	const connectionType = this.resolveConnectionType({ ctx, connectionType: preferredConnectionType });
	const qb = new QueryBuilder<T>(entityName, this.metadata, this, ctx, undefined, connectionType);
	if (!convertCustomTypes) {
	qb.unsetFlag(QueryFlag.CONVERT_CUSTOM_TYPES);
	}
	return qb;
	}

but when calling it from the SqlEntityManager, the connectionType remains undefined

mikro-orm/packages/knex/src/SqlEntityManager.ts

Lines 15 to 19 in 5a24ced

	createQueryBuilder<T extends object>(entityName: EntityName<T> | QueryBuilder<T>, alias?: string, type?: ConnectionType): QueryBuilder<T> {
	const context = this.getContext();
	return new QueryBuilder<T>(entityName, this.getMetadata(), this.getDriver(), context.getTransactionContext(), alias, type, context);
	}

which means it could can take on a read connection in execute

mikro-orm/packages/knex/src/query/QueryBuilder.ts

Line 643 in 5a24ced

const type = this.connectionType || (method === 'run' ? 'write' : 'read');

Happy to put up a fix if this is indeed an issue, but wanted to make sure I'm tracing this out correctly first or if there is something I missed.

To Reproduce
Steps to reproduce the behavior:

1. Connect to postgres
2. set preferReadReplica to false
3. em.createQueryBuilder(Entity).select('...').execute() seems to use the read replica

Expected behavior
Would expect that the preferReadReplica is respected using the query builder

Versions

Dependency	Version
node	20
mikro-orm	5.6.16
your-driver	Postgres

[B4nan]

I don't recall when we added the preferReadReplicas option, I think this was a community addition. Looking at the code you are probably right, it should be respected in the QB too, same way as in the driver class.

PR welcome 👍 It will be a bit more work than just using the resolveConnectionType method (feel free to make it public if needed, just add @internal annotation to it), direct QB usage does not necessarily have the transaction context, and we need to use write connections for write queries based on the QB type.

[B4nan]

FYI 22e140e?w=1

[jtuppy]

Wow, coming back to this, and seems done, thank you!