CSE 340: Web Backend Development

W02 Learning Activity: Creating and Connecting to your Database

Overview

In this activity, you will set up a PostgreSQL database for your web application.

Activity Instructions

Follow these steps to create a your database and establish a connection to it.

Create your PostgreSQL database

You will create a PostgreSQL database using Render.com, a cloud hosting service that provides free PostgreSQL databases for development purposes. You will use this database for the Node.js server running on your computer (127.0.0.1) and for the Node.js server hosted at Render.

Complete the following to create your database at Render.

1. In a browser, navigate to Render.com.
2. Login (if needed) by clicking the "Login" button in the top-right corner.
3. Click the GitHub option and provide your GitHub credentials as needed.
4. You should arrive at the dashboard.
5. Click the + New button in the top-right corner and select Postgres or PostgreSQL from the dropdown menu.
6. Fill out the form to create your database:
   • Name: Use a unique name for your database (for example, yourname_cse340_db).
   • Project: Select your project in the list. (It may be named "My Project" or something similar.)
   • Database: You may use the same name for your database or, if that is not allowed, use a slight variation.
   • User: (This is a username) Use the same name as your database to make it easy to remember.
   • Region: Select the region closest to you.
   • PostgreSQL version: The default is fine.
   • Datadog API Key: Leave this empty.
   • Plan Options / Instance Type: Select the free plan.
7. Click the Create Database button at the bottom of the form.
8. Wait for the database to be created. This may take a few minutes.
9. Once the database is created, you will see it listed on your Render dashboard. Click on the database name to view its details.
10. In the database details page, locate the "Connection Info" section. Here, you will find the information needed to connect to your database, including the host, port, database name, user, and password.

Connect to your database using PGAdmin

Now that you have created your PostgreSQL database on Render, you need to connect to it using PGAdmin, a popular database management tool for PostgreSQL.

Creating the Connection

Complete the following to connect to your database using PGAdmin:

1. Open PGAdmin on your computer. If you do not have it installed, refer to the setup instructions provided in W01: Tools and Setup.
2. In PGAdmin, right-click on Servers in the left sidebar and select Register > Server... to open the connection dialog.
3. In the General tab, give your connection a meaningful name like "cse-340". This name is just for your reference and helps you identify the connection later.
4. Switch to the Connection tab.
5. Fill out the connection details using the information from your Render database.
   • Host name/address:
     • On the Render.com dashboard, select your database service. On the Info tab look for the Connections box to find the External Database URL item and click the Copy to Clipboard button.
     • The Database URL is a connection string that has many different pieces in it. Unfortunately, pgAdmin is not able to parse it directly, so you need to get the host name from it.
     • Return to the pgAdmin application and paste the URL into the "Host name / address" box. It will be in this format: postgres://username:password@host:port/databasename
     • From this URL, you want to keep everything after "@" symbol and before the last "/" symbol. This portion is the host name.
       • Highlight all characters in the string from the first character all the way to and including the "@" symbol. Delete these characters. The highlighted characters in the example string below illustrate what should be removed:
         postgres://cse340db:odSoeaIrmf1bAz0hvCl4kH@dpg-cd4sj7hqpbpffhu0-a.oregon-postgres.render.com/cse340db
       • Move to the end of the remaining string. Highlight the forward slash "/" and all characters to the right. Delete these characters. The highlighted characters in the example string below illustrate what should be removed:
         dpg-cd4sj7hqpbpffhu0-a.oregon-postgres.render.com/cse340db
   • Port: Use the default PostgreSQL port of 5432.
   • Maintenance database: This is the name of your database (the same as you used when creating it). You can find it on the Render.com connection details.
   • Username: This is the username you specified when creating the database (it is likely the same as your database name).
   • Password: Enter the password provided in the Connection Info section of your Render database details.
   • Save Password: Switch this option to On or check the box to save your password for future connections.
6. Click on the Parameters tab.
7. Verify that the SSL Mode is set to Require (select it if not already selected).
8. Click the Save button to create the connection.

Establish a Connection

Now that you have created the connection, you can connect to your database any time you need to.

1. In PGAdmin, locate the server you just created in the left sidebar under Servers.
2. Right-click on the server name and select Connect Server.
3. If prompted, enter your password (if you did not choose to save it earlier) and click OK.
   • To find this password, go to Render.com, log in and return to the database dashboard.
   • Scroll to the Connections information.
   • Find the Password field and click the Copy to Clipboard button.
4. You should now be connected to your PostgreSQL database hosted on Render.com. You can expand the server node to view your databases, schemas, and other objects.

Closing the Connection

On the Free tier that Render.com offers, it is important to not overuse time or resources. It is recommended that when you are done with your interaction between pgAdmin and the database server that the connection be closed.

1. In PGAdmin, right-click on the server name in the left sidebar.
2. Select "Disconnect Server" from the context menu.

Next Step

Complete the other Week 02 Learning Activities

After you have completed all the learning activities for this lesson, return to Canvas to submit a quiz.

Other Links:

• Return to: Week Overview | Course Home