The Complete Tutorial on the Top 5 Ways to Query your Relational Database in JavaScript - Part 1

If you're developing web applications, you're almost certainly going to be constantly interacting with a database. And when it comes time to select the way you'll interact, the choices can be overwhelming.

In this article, we're going to look in detail at 5 different ways to interact with your database using JavaScript, and we'll talk about the pros and cons of each. We'll start with the lowest-level choice — SQL Commands — then move through to higher-level abstractions. 

Choosing the right database library for your JavaScript application can have a big impact on the maintainability, scalability, and performance of your code, so it's worth spending some time to figure out your options.

Our Sample Application

We're going to use a trivial Express application hosted on Heroku as our example. All the code for this article is in this GitHub repository. Feel free to clone it and follow along.

Pre-requisites

To run the sample application, you'll need the following software on your machine:

• A unix-like terminal environment (Mac OSX and Linux are fine. If you're using Windows, you'll need the Windows Subsystem for Linux).
• git (and a github account).
• npm (version 6 or later).
• The Heroku command-line tool.

If you don't have a Heroku account already, you'll need to sign up for a free account. If you don't want to sign up for Heroku, you can also run the application locally against a local Postgres instance. If you're comfortable with that, it should be pretty easy to see what changes you need to make instead of deploying to Heroku.

Once you've installed all the above, run heroku login in a terminal, and you're ready to get started.

Build and Deploy the Hello World App

To start, we'll set up the following:

• A trivial Express application that just serves a "Hello, World" web page.
• A Postgres database.
• Two tables, representing "users" and "comments" (a user has many comments).
• Some sample data (in this case, generated via mockaroo.com).

I've created a sample application which will set all this up for you (provided you've run heroku login  as mentioned above). To set it up, please execute the following commands from the command line:

git clone https://github.com/digitalronin/query-database-javascript.git 

cd query-database-javascript make setup 

This will take a few minutes to complete. While you are waiting, you can view the makefile to see the relevant commands, which carry out the following:

• Create a new Heroku application.
• Add a Postgres database instance.
• Deploy the application to Heroku.
• Run a command on Heroku to set up the database tables and import the CSV sample data.
• Open the URL of your Heroku application in a new browser window.

At the end of this process, you should see "Hello, World" on a web page.

Fetching Data With SQL

OK - we're all set up! We've created a database with two tables and some sample data. But we're not doing anything with it yet. The next step is to enable our web application to retrieve data from the database.

Whenever you interact with a relational database, you do so by sending SQL commands to the network socket on which the database is listening. This is true for all the libraries we're going to look at in this article — at the lowest level, they all send SQL commands to the database and retrieve whatever output comes back.

So, the first way we're going to look at interacting with our database is to do just that — send SQL commands. To do this, we're going to install the pg JavaScript library, which lets us send SQL to a Postgres database and retrieve the results.

To install the pg library, execute the following command:

npm install pg 

This will fetch and install the library, and it will add it to your package.json and package-lock.json files. Let's commit those changes:

git add package.json package-lock.json git 

commit -m "Install the pg library"  

To talk to our database, we need some details:

• The hostname of the machine Postgres is running on.
• The network port Postgres is listening on.
• The name of the database our data is in.
• A username and password with permission to access that data.

Most database libraries will let us establish a connection either by supplying an object to the library which has keys and values for all of those details, or by combining them all into a single "database URL", which is what we're going to do.

When you add a database to a Heroku application, you automatically get an environment variable called DATABASE_URL, containing all the details you need to connect to the database. You can see the value of your DATABASE_URL by running:

heroku config 

This will output all the environment variables your application can use. There should only be one for now, so you should see something like this in the output:

DATABASE_URL: postgres://clqcouauvejtvw:1b079cad50f3ff9b48948f15a7fa52123bc6795b875348d668864
[email protected]:5432/dfb3aad8c026in 

In the case of our example, that breaks down like this:

xxxxxxxxxx

{

  "hostname": "ec2-52-73-247-67.compute-1.amazonaws.com",

  "port": 5432,

  "database": "dfb3aad8c026in",

  "username": "clqcouauvejtvw",

  "password": "1b079cad50f3ff9b48948f15a7fa52123bc6795b875348d66886407a266c0f5b"

}

Your DATABASE_URL value will be different, but the structure will be the same.

Now that we have the pg library installed, and we know how to connect to our database, let's execute our first example of interacting with a database. We'll simply fetch the list of users and display them on our web page. At the top of our index.js file, we'll require our pg library, and create a database connection object.

xxxxxxxxxx

const { Pool } = require('pg');

const conn = new Pool({ connectionString: process.env.DATABASE_URL });

In the express() block, we'll alter the get line to call a method that displays a list of users from the database:

 .get('/', (req, res) => listUsers(req, res)) 

Finally, we'll implement the listUsers function:

xxxxxxxxxx

async function listUsers(req, res) {

  try {

    const db = await conn.connect()

    const result = await db.query('SELECT * FROM users');

    const results = { users: (result) ? result.rows : null};

    res.render('pages/index', results );

    db.release();

  } catch (err) {

    console.error(err);

    res.send("Error " + err);

  }

}

This code waits until a connection is established to our database, then sends an SQL query using the query function and retrieves the result.

Now, this step could fail for lots of different reasons, so in the code we test to ensure we've got some data and, if we do, we assign result.rows to the key users of our results object. Next, we pass results to the render function, then release our database connection.

In views/pages/index.ejs we have access to the results object, so we can display our user data like this:

xxxxxxxxxx

<h1>Users</h1>

<ul>

<% users.map((user) => { %>

  <li><%= user.id %> - <%= user.first_name %> <%= user.last_name %></li>

<% }); %>

</ul>

You can see the code with these changes here.  first_name  and  last_name  are the names of two columns from the users table of our database.

Let's deploy these changes so we can see the data in our Heroku application:

 git add index.js views/pages/index.ejs 

 git commit -m "Display a list of users" 

 git push heroku master 

This will take a minute or two to deploy. When that command has finished executing, reload your browser and you should see a list of users on the web page.

MySQL Example

The above example is for Postgres, but the code for other common relational databases will be similar. For example, if you are using MySQL:

• Instead of  npm install pg use  npm install mysql2  (use mysql2, not mysql - mysql2 is faster and supports async/await)
• In index.js you would require mysql like this:

 const mysql = require('mysql2/promise'); 

• The listUsers function would look like this:

xxxxxxxxxx

async function listUsers(req, res) {

 try {

   const conn = await mysql.createConnection(process.env.DATABASE_URL);

   const [rows, fields] = await conn.execute('SELECT * FROM users');

   const results = { 'users': rows };

   res.render('pages/index', results );

   await conn.end();

 } catch (err) {

   console.error(err);

   res.send("Error " + err);

 }

}

views/pages/index.ejs remains the same.

You can see the sample project with these changes here.

Conclusion

We've covered a lot of ground here, but this is all fundamental to understanding how all database access works. In the next part, we'll see how query builder and object-relational modeling libraries build on top of this foundation to allow you to work with database data in your code in a way that feels more like working with JavaScript functions and objects.