Yes, you can and you probably fucking should use a relational database in your Node/Express apps. Most data you come across is relational so I’ll never understand this dogma being preached that if you use Node as your back-end then you have to pair it with MongoDB as your persistence layer (a database). You know, that MEAN stack crap everyone cheers on. Bullshit! It’s utter bullshit and anyone who’s used Node.js whilst actually thinking about their data models can see it. You use the database that best fits your dataset. Sort of like that whole “right tool for the job” idea (which is excellent advice, by the way)? Now let’s get you set up with the Bookshelf ORM and do some relational database modeling. We’ll also talk about integrating this into an Express application.
What’s an ORM?
ORM stands for Object Relational Mapping which is a technique that maps object properties to database tables. The idea is that you create classes (in any language) that are representations of real word concepts or physical objects. They have properties and they have methods. A simple example would be of a User model. A user in most web applications has a username and a password as well as a bunch of other stuff like email address and such. A user can also have methods like owning a number of other model objects like blog posts, for example. The class has a direct correlation with a table in the database where each of the table’s fields is represented by one of the class’ properties. Each foreign key in the table becomes a method on the class which performs a join query to pull in related data. Every mainstream language has at least two different relational ORMs to choose from. Node.js only has the MongoDB driver for the longest time. They had to create their own wrapper for it to build an ORM. Then Mongoose (technically an ODM – object document modeler – since its for a non-relational databases) can around and filled that voice. Since then everyone and their mother was using Mongoose with MongoDB and creating schemas for their non-relational databases. How odd. A schema for a database that claims to be non-relational? Sure, there are good reasons to enforce a schema on Mongo but the ability to create arbitrary fields should be enabled by default.
This section will walk you through installing the production and development dependencies needed to use Bookshelf in an Express app.
Note that as of this writing I’m using the 0.9.x release of Bookshelf, the 0.10.x release of Knex, and the latest SQLite3 driver (3.1.2 right now).
Bookshelf is an ORM that lets you model relational data. It’s like Node’s version of ActiveRecord and way easier to set up and use than Sequelize, another Node.js relational ORM (Hooray for choice! Hooray for competition!). Unfortunately the creators of Bookshelf decided that their very thorough, straightforward, and easy-to-read documentation might make their library popular and easier to use so they fucked it all up with whatever is on their site now which includes far fewer so people like me have to dig through GitHub issues, outdated wiki articles, and StackOverflow questions to get enough information to write articles like this so that beginners can get up to speed and fill in the gaps that the Bookshelf docs shouldn’t be missing.
Bookshelf is an amazing and simple ORM! Please don’t let the docs scare you off! Relational data is awesome in Node and Bookshelf is one of the best ORMs for relational data in Node. Even if you don’t use Bookshelf, don’t default to using MongoDB because “that’s what Node dev do”.
The first step toward having a usable Express app backed by a relational database is to install Knex globally. So run:
1 2 3 4 5
This should get you all of the dependencies you need to get started with connecting to a database. You’ll need Bookshelf and Knex as general dependencies, SQLite3 as a dev dependency, MySQL (or another database package) for Knex to use as a database adapter in production, and you’ll also need Knex installed globally so you can use its CLI tools to generate and run migrations. As far as the production database drivers go, you’re free to use Postgres, MariaDB or any other Knex-compatible database driver. See the docs for more info.
Express setup and libraries
Now we need to create a web application and we’ll build it on top of Express, the Node framework that gives you the basic routing, middleware, and request/response facilities you need in any web application while leaving the implementation of each route’s body up to you. There are a lot of great ways to set up an Express app and here I’ll show you my favorite way.
Run these commands:
Easy. There’s a whole lot more you can add on top of this but we’ll start with the basics and just get Express and Handlebars templating installed as production dependencies.
With the minimum amount packages installed you can now start using them in your Express app. Here we assume that in addition to all of the database drivers and ORMs installed you also have Express and it’s dependencies installed. If not, you should get acquainted with running an Express app.
In addition, let’s assume that your file structure looks like this:
Here’s a simplified version of that file structure based on the image above:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
Here you see that we have a root folder filled with the usual stuff like a
knexfile.js, etc. The
server/ folder is where all of the app’s guts live. It’s where we store our controllers, models, and views while the root of the project is where we keep our project configuration files, task runner files, and other project setup files.
Before you get into writing any code you need to run a command to generate that
knexfile.js you see. That command is
This will create a Knexfile in your project root. The development environment should be good to go for your situation. The only time you’ll need to revisit this is when you get ready to deploy your application.
In the image above you can see the contents of my
models/ folder which contains a file called database.js (see it’s code on GitHub here). The contents of that file are responsible for connecting each model to the database. For those of you who are wondering about connection pooling, it is my understanding that because of the way in which Node requires modules, each use of a model will not cause you to open up a new pool of DB connections but instead will simply use any connection pool that already exists. Let’s go through the database connection file (
database.js) line by line. Pay close attention to the comments in the file as these will explain exactly what’s going on at each point along the way:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
Now you have a database connection file that, because of how Node imports or
requires files, will give you a persistent connection to the database when you use your first model. But models are backed by database tables so you’ll need to create those. Again, we turn to Knex to generate those tables.
Running the command
knex migrate:make create_users_table will create a new migration file in a new folder that’s created for your called
migrations. Within that folder you’ll notice a file has been generated. Within that file you’ll define the database table you want to create. Check out the schema building section of the Knex docs for more. A basic migration looks like this:
1 2 3 4 5 6 7 8 9 10 11
This is how you should model your own migrations. Obviously you’ll have different requirements but this is how to use the starter code given to you in each migration generated.
Once you’ve written your migration you need to actually apply it to your database. To do this you run
With the database migrations applied you need an actual model to actually use that database connection. Here’s how we create it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
The above model (held in
server/models/user.js) describes a user model with relations to a Favorites and Profile mode. A hasMany and hasOne relation respectively. Remember that registry plugin we used earlier in our database connection script? This is where it gets used. You’ll notice that we’re referencing other, related models, by their names as strings and not as variables. This avoids circular dependency errors. Without it your only option is to define all of your models in a single file. Our goal here is to keep our application modular so that Bookshelf registry plugin is priceless to us.
In this example of a User model we’re exporting a
Bookshelf.model(‘User’, User) (the
'User' string relates to the
User variable we created which is our User model. There are only a few hard and fast rules when it comes to Bookshelf models. Obviously the syntax matters but beyond that you have a lot of freedom to define methods and custom properties for your model. Let’s break the user model down.
Bookshelf is our variable that holds the connection to the database. Our model needs this. Once a single model calls on it all others will use that same connection so you won’t end up making redundant database connections each time you use a model.
1 2 3 4 5 6 7 8 9 10
The only “required” properties in this model are the
hasTimestamps. The first because Bookshelf needs to know what table to base your model on. It’ll read your database table and your model’s properties will match the fields in your database table. The second property is only required if your model is set up with time stamp fields. If your table uses timestamps then setting this to true will automatically set this fields each time you create or update a record with no code required to write by you.
Then we get into the favorites and profile properties. Those properties are there only to define the relation to another model. This is why, if you look at the full model presented above, you’ll see we require two other model files but don’t assign them to variables. This is to avoid circular dependencies but also so we can define relationships between models through these methods that in this case we’re calling
profile. These aren’t required unless you need to define relationships between two tables. Note however, that your migrations need to define a relationship between the tables your models represent based on foreign keys.
Running a server
Uh oh. We’ve hit a wall. This is up to you. There are as many ways to do this as there are developers. My preferred way is to use a Grunt or Gulp task that’ll run a local Node server with live reloading capabilities. For a bare-bones setup you can just run
Setting up the main server file
This part is mundane and easy. Here’s a basic skeleton:
1 2 3 4 5 6 7 8 9 10 11 12
That’ll get you a server running. Now you need just one controller to start using your models. Here’s an example of a model that has a route that looks up all users with the email entered into an input field:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
In this controller if someone makes a GET request to
http://localhost:3000/ a page will be rendered showing all users with the email address in params. I won’t show how you render those results – that’s on you to figure out.
There it is. That’s how to set up Bookshelf in an Express app. Hopefully this article along with Bookshelf’s docs help you put enough pieces together to get things working for your own app. This post is dedicated to my most recent batch of students who have been asking for a Bookshelf tutorial considering how inaccessible the new Bookshelf docs are to new developers. Good luck out there and remember – the MEAN stack is a mistake. Relational data is far more common than non-relational data. Don’t just slap a connection to MongoDB to your Node app because that’s what’s you do, because that’s how the MEAN stack says to do it. Build your app based on specs. Don’t write your specs according to your pre-existing app.