Using Native SQL in Loopback

Quick Loopback Overview

For those of you not familiar with Loopback, it is a web framework, built upon Express.js designed primarily for RESTful APIs. It favours configuration over coding, and can very quickly have you up and running with standard REST end-points for common CRUD actions.

It’s not without it’s downsides, but it does a lot of the boilerplate coding for you and we use it extensively within Media Suite. For more detail, check out their website.

The Case for Native SQL

Loopback supports a number of different database back-ends, including NoSQL databases such as Mongo, and more traditional relational databases like PostgreSQL. Within Media Suite, we exclusively use PostgreSQL.

Unfortunately, Loopback’s flexibility with regard to database engine is also a problem for us. Its query syntax, for the most part, limits itself to the lowest common denominator, and as a result we’re left bereft of Joins.

And sometimes you really want to use a Join.

I know, we can get a super set and filter it down, but relational databases are really good at this kind of operation; they’ve been doing it for a long time. As swift as the Node runtime has become, it can’t, and shouldn’t compete in this space.

If we want to limit our query by values on other models, there are a couple of options:

Create a View and then map a Loopback model to this View.
It’s a great strategy for read-only features like a Search, but is not practical if you want to use the results to populate an existing model already mapped to a database table.
Write SQL, execute it and hydrate the Loopback models with the results by hand.

This blog post is going to examine the second of these options. Some of the code in here is specific to the PostgreSQL connector, though the same pattern will be appropriate for any relational database.

Getting Access to the Connector

Go to the working GitHub repo
Go to this file: server/models/book.js
Note: This code is working as of Loopback 2.22.0. As the examples use private method calls, future updates may invalidate some of the syntax.

To access any of methods required for running SQL, we need to get hold of the database connector.

const connector = app.dataSources.db.connector

If we deconstruct this a little bit, what are we doing?

The app is the main loopback object
The dataSources represents the data sources that are declared in the datasources.json (or equivalent js file)
db is the name of our chosen data source, in this case the PostgreSQL database
Finally the connector is the object that contains the useful methods for executing the SQL and managing the results.

Further reading:

Setting up programatic data sources
How to set up environment specific data sources (and a bit more detail on what the key/values in the datasource object are used for)
PostgreSQL connector info

Running the Query

Now we’ve got access to the Connector, this bit is really easy. For the sake of this example, let’s assume a simple query:

const sql = `

   select b.*, a.* from book b

   inner join author a on

      a.author_id = b.author_id

   where a.author_id = ${authorId};

`

To execute that SQL, we simply call the execute method on our connector:

connector.execute(sql, null, (err, resultObjects) => {

   // use your results here

})

Loopback is still heavily callback based, and since we’re quite deep into its API there is no promise equivalent of this method.

Hydrating the Models

We now have resultObjects; an array of objects, with each object representing one row of results from the query. Each row contains all the fields specified in the query, in this example from the book and author tables.

Warning: If you have columns of the same name in each table, only one field will be present in the JavaScript object. One table’s data will be lost. This is a real problem you’ll have to manage and design for in your database schema.

We have to turn the data we get back from the query into the correct structure for hydrating the Loopback model. We can do that using the fromRow method on the connector.

const bookData = connector.fromRow('book', rawRowData)

The fromRow function’s first argument is a string representing the Loopback model that you want the data in the correct format for, and the second a single row of raw data returned from the database. Then you can hydrate a new model instance, as below:

const book = new app.models.Book(bookData)

Putting these bits of code together, we can end up something like this:

const books = resultObjects.map(rawRowData => {

   const bookData = connector.fromRow(book, rawRowData)

   return new app.models.Book(bookData)

})

Further reading – see Data Mapping Properties for how the data mapping is managed. It’s normally not required to manually add these details, but here is where you can implement non-default options.

What About Related Models?

Within Loopback’s standard query syntax, we can add an include to retrieve related models, but with this approach, that’s not an option. You’ll note in the SQL there were two models’ worth of data returned. If you have unique field names, it is simple as passing the one result object (rawRowData in this example) into the fromRow method for each model.

const bookData = connector.fromRow('book', rawRowData)

const authorData = connector.fromRow('author', rawRowData)

It is then required to explicitly link the two models, as Loopback can’t infer this relationship simply from matching up Primary and Foreign keys in the model’s data.

book['author'](new app.models.Author(authorData))

And that’s it! Your models are hydrated, linked and ready to be sent back via your API.

Check out the GitHub repo mentioned above to see the code all together.

Banner image: Photo by Aleksandr Popov on Unsplash