Welcome back! , we looked at a very "low-level" way to interact with a relational database by sending it raw SQL strings and retrieving the results. We created a very simple Express application that we can use as an example and deployed it on Heroku with a Postgres database. In the first part of this series In this part, we're going to examine a few libraries which build on top of that foundation, adding layers of abstraction that let you read and manipulate database data in a more "JavaScript-like" way. So far, we've seen how to send raw SQL to the database; statements like: SELECT * FROM users If we wanted to get comments by a specific user, say the user whose id is 1, we might use something like this: SELECT * FROM comments WHERE user_id = 1 There's nothing wrong with interacting with your database in this way, but it can feel a bit cumbersome, and it requires you to keep "shifting gears" mentally. You think about your JavaScript code in one way, but when you need to think about the data in your database, you have to start thinking in SQL. The purpose of the rest of the database libraries we're going to consider is to let you treat the data in your database more like the JavaScript objects and code in your application. "Under the hood" it's all SQL, but you won't need to care much about that unless you want to. Knex — Abstracting Away SQL The first library we'll talk about is . The documentation page describes Knex as a "query builder", and its purpose is to provide a layer of abstraction on top of raw SQL. Knex Installing Knex Knex requires pg (or MySQL if you're using a MySQL database). We already have pg installed, so we just add knex like this: npm install knex git add package.json package-lock.json git commit -m "Install the knex library" Using Knex The NPM page for knex describes it as a "query builder." Knex abstracts SQL to a certain extent, but not very far. We still need to understand the underlying SQL, but we can write it in a more JavaScript-like syntax, rather than having to slice and dice SQL strings. More importantly, we can use composition to chain knex terms in a way that is much more comfortable for JavaScript programmers. So, when we used pg, we had this statement: const result = await db.query('SELECT * FROM users'); When we use knex, we can write this: const result = await db.select().from('users'); That might not look like much of a difference, but because of the way we can compose knex function calls, we can also do things like this: const result = await db.select().from('users').limit(5).offset(8); Here, we're getting 5 user records, starting at position 8 in the total set of all possible user records that match our query. You can see the full set of options available in the . knex documentation Let's change our Express app to use knex to display some records from our database. First, in index.js replace these two lines: { Pool } = ( ); conn = Pool({ : process.env.DATABASE_URL }); const require 'pg' const new connectionString ...with this: db = ( )({ : , : process.env.DATABASE_URL 
}); const require 'knex' client 'pg' connection Then, change the implementation of to this: listUsers { { result = db.select().from( ).limit( ).offset( ); results = { : (result) ? result : };

    res.render( , results );
  } (err) { .error(err); 

    res.send( + err); 
  }

} async ( ) function listUsers req, res try const await 'users' 5 5 const 'users' null 'pages/index' catch console "Error " Our views/pages/index.ejs file can stay exactly the same as before. Commit, push, and deploy: git add index.js git commit -m "Use knex to display user data" git push heroku master When you refresh your browser, you should see user records 6 to 10 on the page. You can see the code with these changes here. Object Relational Mapping (ORM) Knex gives us a way of interacting with our database, which is much more like JavaScript, but we are still required to think in a database-centric way when we need to manipulate data. The next three libraries we're going to talk about are all built on top of knex (which is built on top of pg or MySQL), and are examples of "object relational mapping" or ORM libraries. As the name implies, the purpose of an ORM library is to translate between data in a relational database and JavaScript objects in your application. What that means is, instead of thinking about records in the table when you write your JavaScript code, you can think about user objects. users Objection The first library we'll look at is , which is built on top of knex: objection npm install objection git add package.json package-lock.json git commit -m "Install the objection library" To highlight some of the utility of ORM libraries, we're going to alter our application to display users and their comments. Objection is built on top of knex, so in our index.js file, we have to leave the knex block in place, and add a bit more code (I'm putting everything in the index.js file, to keep things simple. In a real application you would break the code into separate files): const { Model } = require('objection'); Model.knex(db); This gives us a Model class from which we can inherit to define two classes User and Comment. We'll define Comment first: { get tableName() { ; 
  } 
} class Comment extends Model static return 'comments' Our class needs to extend , and must implement a function to tell Objection which database table contains the underlying records. Model tableName The class is similar, but we're going to add some behavior to our class; a function, which we can use in our view template. We're also going to tell Objection that have (i.e. a user owns zero or more comments). In ORM-speak, this is usually described as a "has many relationship" - i.e. a user has many comments. Here's what the code for this looks like: User fullName Users Comments { get tableName() { ; 
  } 

  fullName() { ; 
  } get relationMappings() { { : { : Model.HasManyRelation, : Comment, : { : , : } 
      } 
    }; 
  } 
} class User extends Model static return 'users' return ` ` ${ .first_name} this ${ .last_name} this static return comments relation modelClass join from 'users.id' to 'comments.user_id' We define a relationMappings object inside our User class, with a single key of comments and a value telling Objection that this is a HasManyRelation on the Comment class, where the value of the id column of the users table matches the value of the user_id column of the comments table. Now that we've defined our classes, let's use them in our code. Here's the new implementation of listUsers: { { users = User.query().limit( ); (i users) { user = users[i]; 
       user.comments = User.relatedQuery( ).for(user.id); 
     } results = { : users }; 

     res.render( , results ); 
   } (err) { .error(err); 

     res.send( + err); 
   } 
 } async ( ) function listUsers req, res try const await 5 for in const await 'comments' const 'users' 'pages/index' catch console "Error " Here, we fetch 5 users, then for each of those users, we fetch their comments and assign those to the comments property of our user object. In views/pages/index.ejs we can display our users and their comments like this: Users { %> - { %> < > h1 </ > h1 < > ul < (( ) => % users.map user < > li < %> %= user.id < () %> %= user.fullName </ > li < > ul < (( ) => % user.comments.map comment < > li < %> %= comment.body </ > li < }); %> % </ > ul < }); %> % </ > ul You can see the code with these changes As usual, commit, and push to deploy: here . git add index.js views/pages/index.ejs git commit -m "Show users and comments using Objection" git push heroku master Now, when you reload the page, you should see users and comments. The “N+1 Selects” Problem This code highlights a common problem that people run into when using ORM libraries, known as the "N+1 selects" problem. This is the block of code we used to fetch users and their comments: users = User.query().limit( ); (i users) { user = users[i]; 
  user.comments = User.relatedQuery( ).for(user.id); 
} const await 5 for in const await 'comments' This works, but is very inefficient. First, we fetch 5 users, then for each of those 5 users, we fetch their comments . So, we've made 1 call for the users, then another 5 calls to get the comments. That's 5 calls plus the first 1, i.e. 5+1 or N+1 where N == 5. Hence the "N+1 selects" problem. by making another call to the database Unless your database queries are very complex, the time it takes to make a round-trip call to the database is a lot longer than the time it takes the database to calculate and transmit the results of your queries. So, to keep our applications fast, we need to minimize the number of calls to the database, as much as we can. The code above is the exact opposite of this. For this trivial example, you won't notice any difference, but for real-world applications, the performance hit can be very serious, and cause lots of problems. Fortunately, every ORM library has features that make it easy to avoid this problem (provided you know it's there). Here's how Objection does it; in index.js, replace the code block above with this: const users = await User.query().limit(5).withGraphFetched('comments'); This one line does the same as the code block above, but in a much more database-efficient way. Objection will use the relationship information we provided to figure out how to fetch the user data and the comments data in a single query, and unpack and stitch together the results into the same object structure we built before using our for loop. You can see the code with these changes here. Bookshelf The next ORM library we'll look at is . Bookshelf A lot of the differences between ORM libraries depend on what use-case the library is optimized for. In the case of Bookshelf, it's clearly designed to make it as easy as possible to render paginated lists of data, which is a very common use case in web applications. Let's replace Objection with Bookshelf in our application: npm uninstall objection npm install bookshelf git add package.jsonpackage-lock.json git commit -m "Replace Objection with Bookshelf" In index.js, replace these lines: { Model } = ( ); 
Model.knex(db); const require 'objection' ...with this: bookshelf = ( )(db); const require 'bookshelf' Replace our class definitions with these: Comment = bookshelf.model( , { : }); User = bookshelf.model( , { : , 

  comments() { .hasMany( ); 
  } 
}); const 'Comment' tableName 'comments' const 'User' tableName 'users' // by default, bookshelf infers that the foreign key is 'user_id' return this 'Comment' Our function now looks like this: listUsers { { models = User() 
     .fetchPage({ : , : , : [ ]  
      }); 

    users = []; 

    models.map( { user = m.attributes; comments = m.related( ); 

      user.comments = comments.map( c.attributes); 
      users.push(user); 
    }); results = { : users }; 

    res.render( , results ); 
  } (err) { .error(err); 

    res.send( + err); 
  } 
} async ( ) function listUsers req, res try const await new pageSize 5 page 1 withRelated 'comments' => m const const 'comments' => c const 'users' 'pages/index' catch console "Error " As you can see, the definition of the classes is a bit more concise, but Bookshelf needs a more verbose definition of how to unpack our data to build the users/comments structure. Notice also how the concept of pages of data is built directly into the library's API. The code in views/pages/index.ejs is almost identical (I've removed the fullName function from the User class): Users { %> - { %> < > h1 </ > h1 < > ul < (( ) => % users.map user < > li < %> %= user.id < %> %= user.first_name < %> %= user.last_name </ > li < > ul < (( ) => % user.comments.map comment < > li < %> %= comment.body </ > li < }); %> % </ > ul < }); %> % </ ul You can see the code with these changes . And of course, once again commit and deploy. here git add index.js views/pages/index.ejs git commit -m "Show users and comments using Bookshelf" git push heroku master Sequelize The last library we're going to look at is . Sequelize Sequelize is quite opinionated in the way it expects your data to be structured. If you follow its conventions, you can write less code and allow Sequelize to do a lot of the work for you. In particular, Sequelize has a lot of features to help create tables for you, and by default, it will create them following its own structure and naming conventions. The database we've been using isn't structured in exactly the way Sequelize expects, so we need to add a bit of extra configuration to allow Sequelize to work with it. Installing Sequelize To remove bookshelf and install sequelize, run these commands: npm uninstall bookshelf npm install sequelize git add package.json package-lock.json git commit -m "Replace Bookshelf with Sequelize" Using Sequelize In index.js, replace these lines: db = ( )({ : , : process.env.DATABASE_URL 
}); bookshelf = ( )(db) const require 'knex' client 'pg' connection const require 'bookshelf' ...with these: { Sequelize, DataTypes } = ( ); sequelize = Sequelize(process.env.DATABASE_URL); const require 'sequelize' const new Then, replace the class definitions for User and Comment with this code: User = sequelize.define( , { : { : DataTypes.STRING }, : { : DataTypes.STRING }, : { : DataTypes.STRING } 
  }, 
  { : , : }
); Comment = sequelize.define( , { : { : DataTypes.STRING } 
  }, { : , : }
); 

User.hasMany(Comment, { : }); const 'User' first_name type last_name type email type tableName 'users' timestamps false const 'Comment' body type tableName 'comments' timestamps false foreignKey 'user_id' Note that we passed two objects to . The first object defined our object's properties, and the second contained some metadata. sequelize.define In this case, we told Sequelize that the database table that underpins the User class is called 'users' (by default, Sequelize would infer that the table was called 'Users'), and the tells Sequelize that our table does not have timestamp columns, called createdAt and updatedAt. timestamps: false Sequelize makes it very easy to write code that will create tables for you, and when it does so, it adds these timestamp columns and sets their values accordingly when you write to the database. The is excellent, and has more about this. sequelize documentation The that we pass to hasMany is another of the places where we have to tell Sequelize we're not following its conventions. It expects (and would create for us) a column called UserId to link comments to users. foreignKey: 'user_id' Inside our function, we can replace all of this code: listUsers models = User() 
  .fetchPage({ : , : , : [ ] 
  }); 

users = []; 

models.map( { user = m.attributes; comments = m.related( ); 

  user.comments = comments.map( c.attributes);  
  users.push(user); 
}); const await new pageSize 5 page 1 withRelated 'comments' => m const const 'comments' => c ...with this single line: users = User.findAll({ : Comment }); const await include We also have to make one tiny change in views/pages/index.ejs. Replace this line: <% user.comments.map((comment) => { %> ...with this (the difference is user.Comments instead of user.comments): <% user.Comments.map((comment) => { %> You can see the code with these changes here. git add index.js views/pages/index.ejs git commit -m "Show users and comments using Sequelize" git push heroku master So Which Option Is Best? So there you have it — 5 ways you can query a relational database from your JavaScript application. We started with raw SQL via the pg/mysql library, then looked at the knex query builder, before moving on to three ORM libraries; objection, bookshelf, and sequelize. So, which is the right choice for your application? As always, it depends. There's nothing you can do with an ORM library that you can't do using a query builder or even raw SQL. Since everything works using SQL "under the hood". That's not surprising. Also, even if you decide to use an ORM, most libraries still give you a way to send raw SQL to your database. So what level of abstraction you use depends on the problem you're trying to solve, and what kind of code you want to focus your attention on. If you're making heavy use of the features of your database, maybe with complex views or stored procedures, you might find it easier to use knex or raw SQL. But, for most web applications it's quite likely that an ORM library will make your life easier by abstracting away the table structure and allowing you to think about your application data as JavaScript objects. If you've decided on ORM, the choice of ORM library to use isn't always clear-cut. The landscape of JavaScript libraries is very dynamic. New libraries are created quite often, and older ones fall out of favor. Here are a few things to think about when making your choice: which Scan through the documentation of the library, and see if it's clear and comprehensive. Then, decide if the way the API is put together makes sense to you. Different libraries use different approaches, and you might find one of them a better fit than others for your requirements and preferences. This is especially true if you are writing code to work with an existing database, or creating your database as you develop your application. Have a look at the community around the library. Is it something a lot of people are actively using? If so, there will probably be plenty of help and advice available if you need it. Some libraries also have extensive plugin ecosystems around them, and it may be that particular plugins make your life much easier. A related issue is the age of the library. If it's been around for a while, it's more likely that common problems have been found and fixed. If it's a relatively new library, you might have to figure more things out for yourself (which might be a good thing if you're someone who loves to play with new, shiny toys and solve puzzles). Performance is more likely to depend on how you use the library than on the library itself. But, if you absolutely, positively must squeeze the last few microseconds of latency out of your application, then working closer to the database using SQL or knex will be a little faster. Be warned that this is usually quite a marginal benefit, and the cost in the maintainability of your code is very likely to be higher than the gain in benchmark performance. Happy querying!

Chain

Fetch

Heroku

I write about technology michael.bogan@gmail.com

2022 - HackerNoon Contributor of the Year - Api

2022 - HackerNoon Contributor of the Year - Devops

2022 - HackerNoon Contributor of the Year - Microservices

2022 - HackerNoon Contributor of the Year - Software

2022 - HackerNoon Contributor of the Year - Software Architecture

2022 - Software Developer of the Year

Nominated for 2022 - HackerNoon Contributor of the Year - Software Architecture

Nominated for 2022 - HackerNoon Contributor of the Year - Microservices

Nominated for 2022 - HackerNoon Contributor of the Year - Devops

Nominated for 2022 - HackerNoon Contributor of the Year - Api

Nominated for 2022 - Software Developer of the Year

Nominated for 2022 - HackerNoon Contributor of the Year - Software

Too Long; Didn't Read

Let Your Engineers & PMs focus on Product. Apply for $50K Credits!

[Tutorial] Top 5 Ways to Query Your Relational Database in JavaScript [Part 2]

[Tutorial] Top 5 Ways to Query Your Relational Database in JavaScript [Part 2]

Too Long; Didn't Read

Companies Mentioned

Coin Mentioned

@MichaelB

RELATED STORIES