对不起，兄弟。但在目前的开发状态下，TypeORM并没有让我们设置连接变量的功能。解决你问题的方法是这样做。
Iam在使用nest.js的supabase中使用行级安全性，那么我如何将运行时变量安全地设置到DB中，以便确保变量与每个应用程序用户同步(由于http请求触发了执行(？
我看到可以在事务中设置局部变量，但我不想用事务来包装所有查询。
谢谢&问候
我试着用nestjs中的订阅者执行这个操作，它运行得很好。但它不像beforeSelect或beforeLoad那样有功能，所以我把它放在上
import { Inject, Injectable, Scope } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { ContextService } from 'src/context/context.service';
import { DataSource, EntityManager, LoadEvent, RecoverEvent, TransactionRollbackEvent, TransactionStartEvent } from 'typeorm';
import {
EventSubscriber,
EntitySubscriberInterface,
InsertEvent,
UpdateEvent,
RemoveEvent,
} from 'typeorm';
@Injectable()
@EventSubscriber()
export class CurrentUserSubscriber implements EntitySubscriberInterface {
constructor(
@InjectDataSource() dataSource: DataSource,
private context: ContextService,
) {
dataSource.subscribers.push(this);
}
async setUserId(mng: EntityManager, userId: string) {
await mng.query(
`SELECT set_config('request.jwt.claim.sub', '${userId}', true);`,
);
}
async beforeInsert(event: InsertEvent<any>) {
try {
const userId = this.context.getRequest();
await this.setUserId(event.manager, userId);
} catch (err) {
console.log(err);
}
}
async beforeTransactionRollback(event: TransactionRollbackEvent) {
console.log('hello')
try {
const userId = this.context.getRequest();
await this.setUserId(event.manager, userId);
} catch (err) {
console.log(err);
}
}
async beforeUpdate(event: UpdateEvent<any>) {
try {
const userId = this.context.getRequest();
await this.setUserId(event.manager, userId);
} catch (err) {
console.log(err);
}
}
async beforeRemove(event: RemoveEvent<any>) {
try {
const userId = this.context.getRequest();
await this.setUserId(event.manager, userId);
} catch (err) {
console.log(err);
}
}
}
在我知道我们可以使用查询运行器而不是订阅者之后。但它不起作用，我还需要一个通用的方法来使用所有的查询
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Users } from 'src/common/entities';
import { DataSource, EntityManager, Repository } from 'typeorm';
@Injectable()
export class UsersService {
constructor(
@InjectRepository(Users) private userRepository: Repository<Users>,
private dataSource: DataSource,
private em: EntityManager,
) {}
getAllUsers(userId: string) {
const queryRunner = this.dataSource.createQueryRunner();
return new Promise(async (resolve, reject) => {
let res: any;
try {
await queryRunner.connect();
await queryRunner.manager.query(
// like this we can set the variable
`SELECT set_config('request.jwt.claim.sub', '${userId}', true);`,
);
// after setting config variable the query should return only one user by userId
res = await queryRunner.query('SELECT * FROM users');
// but it reurns every user
} catch (err) {
reject(err);
} finally {
await queryRunner.manager.query(`RESET request.jwt.claim.sub`);
await queryRunner.release();
resolve(res);
}
});
}
}
提前感谢。。。。
/**
* Note: Set current_tenant session var and executes a query on repository.
* Usage:
* const itens = = await tenantTransactionWrapper( manager => {
* return manager.getRepository(Entity).find();
* });
*
* @param {function} callback - a function thar receives an Entity Manager and returns a method to be executed by tenantTransactionWrapper
* @param {string} providedTenantId - optional tenantId, otherwise tenant will be taken from localStorage
*/
async function tenantWrapper<R>(
callback: (manager: EntityManager) => Promise<R>,
providedTenantId?: string,
) {
const tenantId = providedTenantId || tenantStorage.get();
let response: R;
await AppDataSource.transaction(async manager => {
await manager.query(`SET LOCAL smsystem.current_tenant='${tenantId}';`);
response = await callback(manager);
});
return response;
}
然后创建一个自定义存储库，使包装器的使用稍微简单一点。
const customRepository = <T>(entity: EntityTarget<T>) => ({
find: (options?: FindManyOptions<T>) =>
tenantTransactionWrapper(mng => mng.getRepository(entity).find(options))(),
findAndCount: (options?: FindManyOptions<T>) =>
tenantTransactionWrapper(mng =>
mng.getRepository(entity).findAndCount(options),
)(),
save: (entities: DeepPartial<T>[], options?: SaveOptions) =>
tenantTransactionWrapper(mng =>
mng.getRepository(entity).save(entities, options),
)(),
findOne: (options: FindOneOptions<T>) =>
tenantTransactionWrapper(async mng =>
mng.getRepository(entity).findOne(options),
)(),
remove: (entities: T[], options?: RemoveOptions) =>
tenantTransactionWrapper(mng =>
mng.getRepository(entity).remove(entities, options),
)(),
createQueryBuilder: () => {
throw new Error(
'Cannot create queryBuilder for that repository type, instead use: tenantWrapper',
);
},
tenantTransactionWrapper,
});
最后使用我们的customRepository:
class PersonsRepository implements IPersonsRepository {
private ormRepository: Repository<Person>;
constructor() {
this.ormRepository = AppDataSource.getRepository<Person>(Person).extend(
customRepository(Person),
);
}
public async create(data: ICreatePersonDTO): Promise<Person> {
const newPerson = this.ormRepository.create(data);
await this.ormRepository.save(newPerson);
return newPerson;
}
public async getAll(relations: string[] = []): Promise<Person[]> {
return this.ormRepository.find({ relations });
}
我希望这能帮助到一些人，如果有人能提供更好的解决方案，我会非常高兴。
首先，您必须创建一个自定义类来包装您的userId或任何东西
custome_service.ts==>
@Injectable()
export class UserIdWrapper {
constructor(private dataSource: DataSource) {}
userIdWrapper = (callback: (mng: QueryRunner) => Promise<any>, userId: string) => {
const queryRunner = this.dataSource.createQueryRunner();
return new Promise(async (resolve, reject) => {
let res: any;
try {
await queryRunner.connect();
await queryRunner.manager.query(
`SELECT set_config('your_variable_name', '${userId}', false)`,
);
//here is your function your calling in the service
res = await callback(queryRunner);
} catch (err) {
reject(err);
} finally {
await queryRunner.manager.query(`RESET your_variable_name`);
await queryRunner.release();
resolve(res);
}
});
};
}
现在你必须调用用户服务内部的函数
user.service.ts==>
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Users } from 'src/common/entities';
import { UserIdWrapper } from 'src/common/local-settup/userId_wrapper';
import { DataSource, EntityManager, QueryRunner, Repository } from 'typeorm';
@Injectable()
export class UsersService {
constructor(
@InjectRepository(Users) private userRepository: Repository<Users>,
private dataSource: DataSource,
private userIdWrapper: UserIdWrapper
) {}
async getAllUsers(userId: string) {
//This is your call back function that have to pass
const findOne = async (queryRunner: QueryRunner) => {
const res = await queryRunner.query('SELECT * FROM public.users');
return res;
};
try {
//hear we are passing the function in to the class function
return this.userIdWrapper.userIdWrapper(findOne, userId);
} catch (err) {
console.log(err);
}
}
}
不要忘记在用户服务的提供者内部提供自定义类服务。