Express is one of the most popular JavaScript frameworks for building backend APIs and Postgres is a really popular relational database. How do we connect the two? If you look at the official documentation for Express, you'll see the section like this: pgp = ( )( ) db = pgp( ) db.one( , ) .then( { .log( , data.value) }) .catch( { .log( , error) }) var require 'pg-promise' /* options */ var 'postgres://username:password@host:port/database' 'SELECT $1 AS value' 123 ( ) function data console 'DATA:' ( ) function error console 'ERROR:' It works for sure but it's not the way you would write it in a full fledged production application. Some of the questions that come to mind are: How do you create the tables in the database? How do you track changes to the database? For example, when you alter a table or create a new table. Or create/drop an index on a field. How to keep track of all these changes in your git/cvs/svn repository? What if you switch from Postgres to some other database in future, say MariaDB for example? Do all your queries still work? There might be a lot more questions but to me, the most important one feels like keeping track of changes to database in your application codebase. If someone clones my repository to their local system, they should have a command to create all the database tables on their local setup. Also, as we make changes to the database like adding/dropping tables or indices or altering any of the tables, one should be able to run a single command to sync their local copy of the database structure with the same on production DB. I am talking about structure, not the data. All the tables on the local database should have the same structure as that in the production database to make the testing of your application easy on local machine. And if you don't have this sync mechanism automated, you're likely to run into a lot of issues that you'll be troubleshooting in production. To solve for these problems, we have libraries like and . These libraries provide a very neat API for writing SQL queries which are database agnostic and prevent issues like SQL injection attacks. Knex Sequelize They also provide transaction support to handle complex DB operations and streaming API to handle large volumes of data in a script. Also, to keep track of structural changes to your database in your code repo, these libraries use the concept of migrations. Migrations are files where you write structural changes you want to make to your database. For example, let's say you have a users table and want to alter the table to add a new column . You can write a Knex migration file like this: gender exports.up = knex.schema .alterTable( , (table) => { table.string( ) }); exports.down = knex.schema .alterTable( , (table) => { table.dropColumn( ); }); => knex 'users' 'gender' => knex 'user' 'gender' The function defines what to do when we run the migration and function defines what to do when we rollback the migration. You can run the migration like this: up down knex migrate:latest And you can roll it back like this: knex migrate:rollback Once you commit this file to your code repository, your other team members can pull the changes from the repo and run these commands at their end to sync up the database structure on their machines. In order to keep track of the database changes (migrations), Knex creates a few extra tables which contain information about what all migrations have been applied. So, for example if one of your team members hasn't synced their database in a long time and there are say 10 new migration scripts added since the last time they synced, when the pull the latest changes from the repo and run the migration command, all those 10 migrations will be applied in the sequence they were added to the repository. Anyway, coming back to the main topic of this post. and how do we use it to connect to our postgres database? Before we dive into this, there are some pre-requisites that should be met How do we add knex to our ExpressJS app Pre-Requisites Node JS version 8 or higher intalled Postgres installed on our localhost:5432 Steps We will divide this article into following steps: Creating the Express app Creating the API endpoint with some hard coded data Creating a database for our app Installing and configuring knex Populating seed data with knex Updating the API endpoint created in step 2 to fetch the data from database instead of returning hard coded data For this tutorial, we will be using Ubuntu Linux but these instructions should work fine other operating systems as well. So, without further ado, let's get started with creating our Express app. Step 1: Creating the Express app Open the terminal (command prompt or Powershell on Windows), navigate to the directory where you want to create this project and create the project directory. We will be calling our project (not very innovative I know :-) ) express-postgres-knex-app mkdir express-postgres-knex-app Go to the project directory and run the following command to generate some boilerplate code using express generator npx express-generator The output should look like this: create : public/ create : public/javascripts/ create : public/images/ create : public/stylesheets/ create : public/stylesheets/style.css create : routes/ create : routes/index.js create : routes/users.js create : views/ create : views/error.ejs create : views/index.ejs create : app.js create : package.json create : bin/ create : bin/www install dependencies: $ npm install run the app: $ DEBUG=express-postgres-knex-app:* npm start This will create the some files and directories needed for a very basic Express application. We can customize it as per our requirements. Among other things, it will create an file and a directory with and files inside. In order to run our application, we need to follow the instructions in the output shown above. First, install the dependencies: app.js routes index.js users.js npm install Then run the app using the following command: DEBUG=express-postgres-knex-app:* npm start This should start our server on port 3000. If you go to your browser, you should be able to see the express application on http://localhost:3000 Step 2: Creating the API endpoint with some hard coded data The automatically created a router for us. If you open the file , you should see the code like this: express generator users routes/users.js express = ( ); router = express.Router(); DB = ( ); router.get( , { res.send( ); }); .exports = router; var require 'express' var const require '../services/DB' /* GET users listing. */ '/' async ( ) function req, res, next return 'respond with a resource' module Here, we need to return the users array instead of a string . And we need to fetch those users from our database. So, for step 2, we don't need to do anything as we already have a route created for us by . In the later steps, we will modify this code to actually fetch the users from our database respond with a resource express generator Step 3: Creating a database for our app In this tutorial, we have a pre-requisite that postgres is installed on your machine. So, we need to connect to the postgres server and once you're inside, run the following command to create the database for our app: express-app; create database Step 4: Installing and configuring knex Install and modules (since we are using ) by running the following command: knex pg postgres npm knex pg install Once installed, initialize knex with a sample config file: knex init This should create a file in your project's root directory. This file contains the configuration to connect to the database. By default, the knexfile will be using for . We need to change this since we are using knexfile.js sqlite development postgres Modify your so it looks like this: knexfile.js PGDB_PASSWORD = process.env.PGDB_PASSWORD; .exports = { : { : , : { : , : , : , : PGDB_PASSWORD }, : { : , : }, : { : , : }, : { : } } }; // Update with your config settings. const module development client 'postgresql' connection host 'localhost' database 'express-app' user 'postgres' password pool min 2 max 10 migrations tableName 'knex_migrations' directory ` /db/migrations` ${__dirname} seeds directory ` /db/seeds` ${__dirname} Now, we need to create a service called where we initialize in our application with the config from . In the project's root directory, create a directory and inside the directory, create a file DB knex knexfile.js services services DB.js In that file, add the following code: config = ( ); knex = ( )(config[process.env.NODE_ENV]); .exports = knex; const require '../knexfile' const require 'knex' module Here, we are importing the config from and initializing the object using the same. Since, we will be running our app in mode, the value of will be and the config for the same will be picked from the . If you run the app in production, you'll need to add the production config in the . knexfile knex development NODE_ENV development knexfile.js knexfile.js Now, wherever in our app we need to pull data from the database, we need to import this DB.js Step 5: Populating seed data with knex So we have our express app up and running with knex integrated. And we have our postgres database created. But we don't have any tables and data in our database. In this step, we will use knex migrations and seed files to do the same. From the project's root directory, run the following commands: npx knex migrate :make initial_setup This will create a new file in the `db/migrations` directory. npx knex seed :make initial_data This will create a sample seed file under the directory. First, we need to modify our migration file to create the users table. Open the newly created file under directory and modify it so it looks like this: db/seeds db/migrations exports.up = { knex.schema.createTable( , { table.increments( ); table.string( , ).notNullable(); }); }; exports.down = { knex.schema.dropTable( ); }; ( ) function knex return 'users' ( ) function table 'id' 'name' 255 ( ) function knex return 'users' Here, in the function, we are creating a table with two fields: and . So, when we apply this migration, a new table will be created. And in the function, we are dropping the table. So, when we rollback our migration, the table will be deleted. up users id name down users users Also, open the newly created file under directory and modify it so it looks like this: db/seeds exports.seed = { knex( ) .del() .then( { knex( ).insert([ { : , : }, { : , : }, { : , : } ]); }); }; ( ) function knex // Deletes ALL existing entries return 'users' ( ) function // Inserts seed entries return 'users' id 1 name 'Alice' id 2 name 'Robert' id 3 name 'Eve' This will first remove any existing entries from our `users` table and then populate the same with 3 users. Now, that we have our migration and seed files ready, we need to apply them. Run the following command to apply the migration: npx knex migrate :latest And then run the following command to populate the seed data: npx knex seed: run Now, if you connect to your postgres database, you should be able to see the table with 3 entries. Now that we have our users table ready with data, we need to update the file to fetch the entries from this table. users users.js Step 6: Updating the API endpoint created in step 2 to fetch the data from database instead of returning hard coded data Open the file and modify the API endpoint to look like this: routes/users.js express = ( ); router = express.Router(); DB = ( ); router.get( , { users = DB( ).select([ , ]); res.json(users); }); .exports = router; var require 'express' var const require '../services/DB' /* GET users listing. */ '/' async ( ) function req, res, next const await 'users' 'id' 'name' return module Here, in the 3rd line we are importing the service. Then inside our route handler, we are fetching the users using the Knex's query builder DB users = DB( ).select([ , ]); const await 'users' 'id' 'name' Knex does the job of translating this to an SQL query: , ; SELECT id name FROM users And then we return the users (array of JSON objects) to the response. Now, go to the terminal where you started the application earlier. Stop the server. If you remember in the knexfile we created earlier, we were using an environment variable for passing the postgres password to our config. So we will need to export this variable with the password of our postgres server PGDB_PASSWORD =<enter your postgres password here> export PGDB_PASSWORD Then run the Express server again =express-postgres-knex-app:* npm start DEBUG Now if you go to the , you should see the JSON array of user objects fetched from your postgres database. http://localhost:3000/users Conclusion So, in this article we created an Express JS app and connected it with a postgres database using Knex. We also touched upon the benefits of using a robust library like Knex for handling database operations in our application and learned about the concept of migrations. Hope you found this article helpful. Also published on: https://codingfundas.com/how-to-connect-your-expressjs-app-with-postgres-using-knex
