Now that you have a good understanding of the Knex's capabilities, let's look at adding Knex to our Express app endpoints. Quit the nodemon drills.js process and start the server.
Open Postman and navigate to http://localhost:8080/restaurants. You should get back a list of restaurants. The SELECT statement is straight forward, the only thing to point out is the addition of in the `.then(results => res.json(results) );
Let's tackle the grade first.
Recall INNER JOIN. If we join the restaurants and grades tables then sort the results by date. We get the list of results where to first row is the results we are looking for. Then LIMIT the results to 1 row and we have our output for a single restaurant.
SELECT restaurants.id, name cuisine, borough,
grades.id, grade, date as "inspectionDate", score
FROM restaurants
INNER JOIN grades ON grades.restaurant_id = restaurants.id
WHERE restaurants.id = 1
ORDER BY date DESC
LIMIT 1;Next, let's update the Knex query to accomplish the same inner join.
knex.select('restaurants.id', 'name', 'cuisine', 'borough', 'grades.id', 'grade', 'date as inspectionDate', 'score')
.from('restaurants')
.where('restaurants.id', 1)
.innerJoin('grades', 'restaurants.id', 'grades.restaurant_id')
.orderBy('date', 'desc')
.limit(1)
.then(results => res.json(results));Finally add a new endpoint to retrieve and return a specific restaurant by ID.
.get('/restaurants/:id', (req, res) => {
// Add query and response here...
});Be sure to swap out the hard-coded id for the req.params.id.
Now let's tackle the address. How do we combine the values from multiple columns in SQL/Knex?
In SQL you can combine multiple values into a single results using the CONCAT() function. In psql or your GUI tool run the following command.
SELECT CONCAT(address_building_number, ' ', address_street, ' ', address_zipcode ) as address
FROM restaurants
WHERE borough = 'Manhattan';Unfortunately, Knex does now support a similar syntax. It does, however, support a raw SQL using the knex.raw() method. Please note, knex.raw() passes the query as-is which can open you up to SQL injection attacks. Do not use this approach for user-entered values until to your understand SQL-injection attacks and Knex Raw Parameter Bindings.
Update the knex query in the .get endpoint with the following
knex.select('id', 'name', 'cuisine', 'borough')
.select(knex.raw("CONCAT(address_building_number, ' ', address_street, ' ', address_zipcode ) as address"))
.from('restaurants')
.limit(10)
.then(results => res.json(results));knex.first('restaurants.id', 'name', 'cuisine', 'borough', 'grades.id', 'grade', 'date as inspectionDate', 'score')
.select(knex.raw("CONCAT(address_building_number, ' ', address_street, ' ', address_zipcode ) as address"))
.from('restaurants')
.where('restaurants.id', 1)
.innerJoin('grades', 'restaurants.id', 'grades.restaurant_id')
.orderBy('date', 'desc')
.then(results => res.json(results));For more information on the .first() methods check out the documentation
SQL query results are formatted in a flat tabular (table-like) form, but most web applications expect data to be hierarchical, known as an Object Graph. So data must be converted. This process is commonly known as hydrating and dehydrating.
The process of converting the tabular data to an object graph is (conceptually) straight forward. Simply create an empty object, loop over the rows creating a new property for each item or appending (pushing) data if the property already exists. In practice, however, it can be quite tricky.
Let's take a look at a simple example. Imagine we have a table of people and each person has zero or more pets. This is a one-to-many relationship. The following knex query might produce the JSON output below
Sample Knex query
knex.select('id', 'name', 'age', 'pets.name as petName', 'pets.type as petType')
.from('people')
.innerJoin('pets', 'people.id', 'pets.people_id')
.then(results => res.json(results));People and Pets output. Notice how the people are repeated for each pet.
[
{
id: 2,
name: 'John Doe',
age: 34,
petName: 'Rex',
petType: 'dog',
},
{
id: 2,
name: 'John Doe',
age: 34,
petName: 'Rex',
petType: 'dog',
},
{
id: 3,
name: 'Mary Jane',
age: 19,
petName: 'Mittens',
petType: 'kitten',
},
{
id: 3,
name: 'Mary Jane',
age: 19,
petName: 'Fluffy',
petType: 'cat'
}
];The desired output is an array of people objects. Each person has an array of pets.
[
{
"2": {
"id": 2,
"name": "John Doe",
"age": 34,
"pets": [
{
"name": "Rex",
"type": "dog"
},
{
"name": "Rex",
"type": "dog"
}
]
},
"3": {
"id": 3,
"name": "Mary Jane",
"age": 19,
"pets": [
{
"name": "Mittens",
"type": "kitten"
},
{
"name": "Fluffy",
"type": "cat"
}
]
}
}
]To hydrate the tabular data::
- start with an empty object.
- loop over each row
- if the 'id' property does not exist, then create it and populate it with people data
- Then push on the pet info
const hydrated = {};
people.forEach(row => {
if ( !(row.id in hydrated) ) {
hydrated[row.id] = {
id: row.id,
name: row.name,
age: row.age,
pets: []
}
}
hydrated[row.id].pets.push({
name: row.petName,
type: row.petType,
});
});
console.log(hydrated);Copy the data above and method above to the drills.js and work thru the algorithm make sure you understand the process.
Next, try to hydrate the tablular data results from the following restaurants query
knex.select('restaurants.id', 'name', 'cuisine', 'borough', 'grades.id as gradeId', 'grade', 'score')
.from('restaurants')
.innerJoin('grades', 'restaurants.id', 'grades.restaurant_id')
.orderBy('date', 'desc')
.limit(10)
.then(results => console.log(results));The goal is to create an array of restaurant objects that look like this:
{
"1": {
"name": "Dj Reynolds Pub And Restaurant",
"cuisine": "Irish",
"borough": "Manhattan",
"grades": [
{
"gradeId": 1,
"grade": "A",
"score": 2
},
{
"gradeId": 3,
"grade": "A",
"score": 11
},
{
"gradeId": 5,
"grade": "A",
"score": 12
},
{
"gradeId": 7,
"grade": "A",
"score": 12
}
]
}
}Implement the same process using Treeize. You can find the documentation here
Create a .post('/restaurants/) which accepts a single restaurant with multiple grades, like the object below. The endpoint will need to create (insert) the restaurant and related grades. Remember to return the correct status code, see Creating POST endpoints. And provide the path to the new document in the location header.
{
"name": "Terra Byte Cafe",
"cuisine": "American",
"borough": "Brooklyn",
"grades": [
{
"grade": "C",
"score": 42
},
{
"grade": "B",
"score": 72
},
{
"grade": "D",
"score": 35
},
{
"grade": "A",
"score": 95
}
]
}
Hint: Is the
req.bodyproperty undefined? Did you install and requirebody-parserand usebodyParser.json()?
Solutions can be found in the "solutions" branch of the node-restaurants-app-knex start repo.