Where do I execute native SQL queries in Loopback 4?

I’m new to Loopback 4 and I’ve been trying to execute a native SQL query. I found how to do it, the thing is that don’t have any clue of WHERE to put it in my code in order for it to work… here’s the documentation I found.

I need to know where should I put this:

const result = await repository.execute('SELECT * FROM Products');

in my Loopback project, which has many files. My goal is to make a REST endpoint like /products/[name] with [name] being a parameter inserted dynamically to the SQL query.

Answer

Personally, I would implement it as a new Repository method.

Let’s say your model is called Product, then you should have src/repositories/product.repository.ts file exporting ProductRepository class already present in your project. (You can run lb4 repository to create it.)

export class Product extends DefaultCrudRepository<
  Product,
  typeof Product,
  Product Relations
> {
  constructor(@inject('datasources.db') dataSource: DbDataSource) {
    super(Product, dataSource);
  }

  // your custom method
  async selectByName(name: string): Promise<Product[]> {
    const rawItems = await repository.execute('SELECT * FROM Products');
    // would you like to convert raw data into Product instances?
    return rawItems.map(it => new Product(it));
  }
}

Then you can call this new custom repository method from your controller in the same way as you would call e.g. repository.find(filter).