CSE 340: Web Backend Development

W02 Learning Activity: Connecting to Databases from Node.js

Overview

In this activity, you will connect your Node.js application to the PostgreSQL database you created in the previous activity. This will allow your application to interact with the database, enabling you to perform operations such as querying data and inserting new records.

Activity Instructions

Follow these steps to connect your Node.js application to your PostgreSQL database.

Gather the Database Connection Information

To connect to your database, you will need to gather the connection information (database host, username, password, etc.). To find this information do the following:

1. Log in to your Render.com account.
2. Navigate to the Databases section from the dashboard.
3. Select the PostgreSQL database you created for this course.
4. Locate the Connections information, find the External Database URL item, and click the Copy to Clipboard button. This URL contains all the information needed to connect to your database.

Prepare the libraries and configuration files

The following steps will walk you through installing the necessary libraries and setting up enviornment variables.

1. Install the pg library, which is a PostgreSQL client for Node.js. You can do this by running the following command in your project directory:

   npm install pg

2. Update the .env file in the root of your project to securely store your database connection information. Add the following lines to the file:

   DB_URL=...
   ENABLE_SQL_LOGGING=true
   

   Make sure to replace the ... with your actual database details from the External Database URL you found earlier on the Render.com dashboard.

Create the Environment Variables at Render

Next, you need to set up the same environment variables on Render.com so that your deployed application can access the database as well.

1. Return to the Render.com dashboard for your project.
2. Select your web service application (not your database).
3. Go to the Environment tab.
4. Add a new environment variable named DB_URL and paste the External Database URL you copied earlier as the value. (Click Edit in the Environment Variables section, then click + Add to add a new variable.)
5. Add another environment variable named ENABLE_SQL_LOGGING and set its value to true for debugging purposes.
6. Save the changes.

Create the Database Connection File

This section helps you create a file that will handle the connection to the database. It uses a connection pool approach to make the connection process as efficient as possible.

Connection Pooling

Creating a new database connection for every query would be extremely inefficient. Database connections require network handshakes, authentication, and resource allocation, which takes time and server resources. Connection pooling solves this by maintaining a set of reusable connections that can be shared across multiple requests.

When your application needs to run a query, it borrows a connection from the pool, executes the query, and returns the connection to the pool for reuse. This dramatically improves performance and reduces the load on your database server.

Debugging Information

In the code, you will notice that it adds some debugging logic. In short, it wraps the calls to the database so that when you are in debugging mode (in your .env you have set ENABLE_SQL_LOGGING=true), it will log every query to the console. This is very helpful when you are finding and fixing bugs. Then, when you are not in debugging mode (in your .env you have set ENABLE_SQL_LOGGING=false) the queries will be sent directly to the database with no extra steps for debugging.

Create the Database Connection File

Create a new file named db.js in the src/models/ directory of your project. This file will handle the database connection logic.

Copy and paste the following code snippet into db.js:

import { Pool } from 'pg';

/**
 * Connection pool for PostgreSQL database.
 * 
 * A connection pool maintains a set of reusable database connections
 * to avoid the overhead of creating new connections for each request.
 * This improves performance and reduces load on the database server.
 * 
 * Uses a connection string from environment variables for simplified setup.
 * The connection string format is:
 * postgresql://username:password@host:port/database
 */
const pool = new Pool({
    connectionString: process.env.DB_URL,
    ssl: true
});

/**
 * Common SSL Issue:
 *
 * You may encounter SSL connection errors depending on your operating system, Node.js
 * version, or PostgreSQL server settings. If you have confirmed your credentials are
 * correct but still see SSL errors, try updating the 'ssl' property in the Pool
 * configuration above to:
 *
 * ssl: {
 *     rejectUnauthorized: false
 * }
 */

/**
 * Since we will modify the normal pool object in development mode, we need to create and
 * export a reference to the pool object. This allows us to use the same name for the
 * export regardless of whether we are in development or production mode.
 */
let db = null;

if (process.env.NODE_ENV === 'development' && process.env.ENABLE_SQL_LOGGING === 'true') {
    /**
     * In development mode, we wrap the pool to provide query logging.
     * This helps with debugging by showing all executed queries in the console.
     * 
     * The wrapper also adds timing information to help identify slow queries
     * and tracks the number of rows affected by each query.
     */
    db = {
        async query(text, params) {
            try {
                const start = Date.now();
                const res = await pool.query(text, params);
                const duration = Date.now() - start;
                console.log('Executed query:', { 
                    text: text.replace(/\s+/g, ' ').trim(), 
                    duration: `${duration}ms`, 
                    rows: res.rowCount 
                });
                return res;
            } catch (error) {
                console.error('Error in query:', { 
                    text: text.replace(/\s+/g, ' ').trim(), 
                    error: error.message 
                });
                throw error;
            }
        },

        async close() {
            await pool.end();
        }
    };
} else {
    // In production, export the pool directly without logging overhead
    db = pool;
}

/**
 * Tests the database connection by executing a simple query.
 */
const testConnection = async() => {
    try {
        const result = await db.query('SELECT NOW() as current_time');
        console.log('Database connection successful:', result.rows[0].current_time);
        return true;
    } catch (error) {
        console.error('Database connection failed:', error.message);
        throw error;
    }
};

export { db as default, testConnection };

Take the time to read through this code and understand each line. If you do not understand something, ask AI to explain it to you.

Update the Main Server File to Test the Connection

Now that you have created the database connection and setup files, you need to update your main server file (server.js) to test the database connection when the server starts.

1. Open the server.js file in the root of your project.
2. After your other import statements at the top of the server.js file, add the following code to import the testConnection function from the db.js file:

   import { testConnection } from './src/models/db.js';

3. Update the server startup code to call the testConnection function. Modify the app.listen section to look like this:

   app.listen(PORT, async () => {
     try {
       await testConnection();
       console.log(`Server is running at http://127.0.0.1:${PORT}`);
       console.log(`Environment: ${NODE_ENV}`);
     } catch (error) {
       console.error('Error connecting to the database:', error);
     }
   });
   

   This updated code contains a few changes:

   • The app.listen function is now an asynchronous function, allowing it to use the await keyword.
   • The testConnection function is called before the server starts listening for requests.
   • A try-catch block is used, so that if the connection test fails, an error message is logged to the console.

Run and Test Your Application

Now that you have set up the database connection and updated your server file, it's time to run your application and test the connection to the database.

1. In your terminal, navigate to your project directory.
2. Start your Node.js server by running the following command:

   npm run dev

3. Check the console output for messages indicating whether the database connection was successful. You should see a message like:

   Database connection successful: [current_time]

4. If you see an error message, double-check your database connection details in the .env file and ensure that your database is running and accessible.

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