How to write Knex Queries.

Knex is a query builder that sits on top of SQL databases. It allows for easier dynamic querying than straight SQL, while remaining lightweight in comparison to a full ORM.

SQL is a language meant to retrieve, manipulate, and store data within a relational database.

If you’re looking for instructions on spinning up a knex app, check out the blog I wrote on that.

Alright let’s take a look at 4 knex query examples.

INDEX Query

Here’s an example of calling down all rows from a table.

app.get('/api/surfers', (_, res) => {
   return database('surfers')
   .then(surfers => res.send(surfers))
   .catch(console.error)
})

So this one is pretty simple. When hitting the /api/surfers path, return the database table ‘surfers’, then send those surfers back as the response. If this fails, catch the error and put it in the console.

SHOW Query

Here’s an example of pulling down one row.

app.get('/api/surfers/:id', (req, res) => {
   const { id } = req.param
return database('surfers')
   .where('id', id)
   .then(surfer => res.send(surfer)
})

This one is a little more complex. When hitting /api/surfers/10 as an example, pull out the /:id of 10. Then pull out id from req.param. Once the app has the id, return a surfer row, where id equals our request. Then send that row back as the response.

SHOW Query (Raw)

You can also write your where statements with raw SQL.

app.get('/api/surfers/:id', (req, res) => {
   const { id } = req.param
return database('surfers')
   .whereRaw('id = ?', id)
   .then(surfer => res.send(surfer)
})

SEARCH Query

Let’s imagine you have a search bar function that may pass an age or date of birth.

app.get('/api/search', (req, res) => {
   const { query }= req.param.body
return database('surfers')
   .where(function() {
      this
         .where('age', query)
         .orWhere('yearOfBirth', query)
   })
   .then(surfers => res.send(surfers)
})

So again, grab the request body, grab the surfers table, then run your queries on it. Starting to see a pattern yet? :D

That’s pretty much the basics of building knex queries. There are tons of other where types and options.

// Search several criteria
.where({age: '20', name: 'Bobby'})
// Search a range
.whereIn('age', [10, 12, 14])
// Search negative criteria
.whereNot('age', 15)
// Search ranges
.whereBetween('age', [30, 40])

As you can see, especially if you already know SQL, knex makes for a very powerful querying tool.

Best of luck!