CODEWITHSUNDEEP
sqlnode.jsapicrudloopback
Nico Horn
Nico Horn

Reputation: 5

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.

Upvotes: 0

Views: 2024

Answers (2)

A. Johan
A. Johan

Reputation: 426

You can do it in your controller class as per loopback docs https://loopback.io/doc/en/lb4/Controller.html. As you will define the REST endpoint in the controller itself you can also do the insertion there using repository.execute() e.g.

  @get('/products/{name}')
  async doSomething(    
    @param.path.string('name') name: string,
  ): Promise<Product> {
  
  const sql = `SELECT * FROM some_table WHERE some_field="${name}"`;
  await this.productRepository.execute(sql)
        
         --- other lines of code & return value --
  }

Upvotes: 2

Miroslav Bajtoš
Miroslav Bajtoš

Reputation: 10795

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).

Upvotes: 2

Related Questions