Join the DZone community and get the full member experience.Join For Free
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.
Our Sample Application
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:
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.
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:
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:
In the case of our example, that breaks down like this:
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.
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:
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:
You can see the code with these changes here.
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.
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 pguse
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:
views/pages/index.ejs remains the same.
You can see the sample project with these changes here.
Opinions expressed by DZone contributors are their own.