Introduction to PostgreSQL with Express

In this assignment, you will connect your Express application to a PostgreSQL database and replace the static course and faculty data with database-driven content. This represents a major step toward building scalable web applications that can store and retrieve data dynamically instead of being limited by hardcoded objects.

You will install the necessary PostgreSQL packages, establish a database connection, integrate pre-built database setup files, and modify your existing route handlers to work with real database queries. By the end of this assignment, your university catalog system will be powered by a PostgreSQL database instead of static JavaScript objects.

The Problem with Static Data

Your current application uses static JavaScript objects to store course and faculty information. While this approach works for learning and small demonstrations, it has significant limitations that become apparent as applications grow:

Scalability Issues: Imagine your university offering 10,000 courses with 500 faculty members. Loading all this data into memory every time the server starts would consume massive amounts of RAM and slow down server startup significantly.

Data Persistence Problems: Any changes to course schedules, faculty information, or new course offerings require code changes and server restarts. There is no way to update information dynamically or allow administrators to modify data through a user interface.

Concurrent Access Limitations: Multiple users cannot simultaneously access or modify the same data safely. Static objects provide no built-in mechanisms for handling concurrent access, data validation, or maintaining data integrity.

Databases solve these problems by providing persistent storage, efficient querying capabilities, concurrent access controls, and the ability to handle large datasets without loading everything into application memory.

Understanding Database Connections

Before diving into implementation, it is important to understand how web applications connect to databases and why we need special patterns for managing these connections efficiently.

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.

Database Setup Scripts

Professional applications need a reliable way to ensure that the database structure matches what the application expects. Setup scripts handle this by creating tables, indexes, and initial data automatically. These scripts are designed to be idempotent, meaning they can run multiple times safely without causing errors or data loss.

Our setup script uses SQL commands like CREATE TABLE IF NOT EXISTS and ON CONFLICT DO NOTHING to avoid errors when tables or data already exist. This allows the setup to run every time the server starts without breaking if the database is already configured.

Preparation

You will need database credentials in order to complete this assignment. See Unit 3 in Canvas for how to get them. We do not link to the credential process anywhere outside of Canvas for security reasons.

Before starting this assignment, follow the instructions in Canvas to retrieve your PostgreSQL database credentials. If you cannot locate your credentials, ask your team for help before contacting your instructor. You will receive individual credentials that include a username, password, host, port, and database name.

You will use these to construct a database connection string that looks like this:


        postgresql://[username]:[password]@[host]:[port]/[database]

Replace the bracketed sections with your actual credentials. For example, if your username is student123, password is secret456, host is postgres.university.edu, port is 5432, and database is catalog_db, your connection string would be:


        postgresql://student123:secret456@postgres.university.edu:5432/catalog_db

Download SSL Certificate

When you retrieved your credentials by following the instructions in Canvas, you were directed to a site that provided an SSL certificate. Create a bin folder at the root of your project and save the certificate there as byuicse-psql-cert.pem. This certificate is required for your Node.js application to connect to the database securely and is not used by pgAdmin or any other graphical tools.

Assignment Instructions

1. Installing PostgreSQL Packages

Install the PostgreSQL client package that provides Node.js connectivity to PostgreSQL databases:


        pnpm install pg

The pg package is the official PostgreSQL client for Node.js and provides connection pooling capabilities, parameterized query support to prevent SQL injection attacks, and comprehensive error handling for database operations.

2. Adding Database Configuration

Update your .env file in your project root to include your database connection string and sql logging flag:


        DB_URL=postgresql://[username]:[password]@[host]:[port]/[database]  # new
        ENABLE_SQL_LOGGING=true                                             # new
        NODE_ENV=development
        PORT=3000

Replace the bracketed placeholders with your actual database credentials. The ENABLE_SQL_LOGGING flag will show you all executed queries in the console during development, which is helpful for understanding what your application is doing and debugging database issues.

Protect Your Database Credentials

Double-check that your .env file is listed in your .gitignore file. Database credentials are sensitive information that should never be committed to version control. Anyone with access to these credentials could potentially access or modify your database.

3. Download and Integrate Database Files

Download the database setup files and merge all of the provided files into your project structure. The files include database connection management, schema creation, initial data seeding, and updated model functions that work with PostgreSQL instead of static objects.

The new files follow this structure and should be placed in your existing project directory:


        [project-root]
        ├── src/
        │   ├── controllers/
        │   │   └── catalog/
        │   │       └── catalog.js      # provided but needs fixing
        │   └── models/
        │       ├── catalog/
        │       │   ├── catalog.js      # new file
        │       │   └── courses.js      # replaces existing
        │       ├── faculty/
        │       │   └── faculty.js      # replaces existing
        │       ├── sql/
        │       │   └── seed.sql        # new file
        │       ├── db.js               # new file
        │       └── setup.js            # new file
        └── bin/
            ├── byuicse-psql-cert.pem   # SSL certificate (you downloaded this separately)
            └── README.md               # instructions for SSL certificate (delete when done)

Take a moment to review each of these files to understand their roles and how they differ from your previous static data approach. Below is a brief overview of the purpose and functionality of each new file:

db.js: Manages the PostgreSQL connection pool and provides query logging in development mode.
setup.js: Creates database tables and populates them with initial course, faculty, and catalog data.
sql/seed.sql: Seeds the database with SQL INSERTs; faster and more reliable than loading JSON in application code.
catalog.js: Handles queries related to course sections and schedules.
courses.js: Replaces your static course data with database queries.
faculty.js: Replaces your static faculty data with database queries.

Important Notes About Provided Files

A catalog controller is provided to prevent server crashes, but it needs fixing (see next section). The faculty controller is NOT provided — you must update your existing one. All templates will need updates for the new data structure.

Understanding Slugs

The database structure uses "slugs" instead of simple IDs for URL routing. A slug is a URL-friendly version of a name that contains only lowercase letters, numbers, and hyphens. For example, "CSE 110" becomes "cse-110" and "Brother Keers" becomes "brother-keers". This creates cleaner, more readable URLs while maintaining database efficiency.

4. Update Server Startup Process

Modify your server.js file to initialize the database connection and setup when the server starts. Add this import near the top of your file with your other imports:


        import { setupDatabase, testConnection } from './src/models/setup.js';

Then update your existing server startup code at the bottom of the file to include database initialization:


        app.listen(PORT, async () => {
            await setupDatabase();
            await testConnection();
            console.log(`Server is running on http://127.0.0.1:${PORT}`);
        });

Your application's global error handler will catch any database connection or setup errors, preventing the server from starting and displaying clear error messages in the console.

The database setup and seeding process runs automatically each time the server starts. It is designed to be safe and non-destructive: tables and initial data are only created if they do not already exist, so restarting your server will not overwrite or delete existing data. This approach allows you to develop confidently without worrying about accidental data loss.

Resetting Database Setup

The setup script only creates tables and inserts data if they don't already exist. To force a full re-seed during development, delete all rows from the faculty table by running DELETE FROM faculty; or TRUNCATE TABLE faculty; in pgAdmin, then restart your server. The setup script detects the missing faculty data and will recreate tables and seed all initial data.

5. Test Your Database Integration

Start your server and verify that the database setup completes successfully. With ENABLE_SQL_LOGGING=true in your .env file, you will see extensive console output. On your very first successful run you should see output similar to the following:


        Error in query: {
            text: 'SELECT EXISTS (SELECT 1 FROM faculty LIMIT 1) as has_data',
            error: 'relation "faculty" does not exist'
        }
        Seeding database...
        Executed query: {
            text: "... 10044 more characters,
            duration: '457ms',
            rows: undefined
        }
        Database seeded successfully
        Executed query: { text: 'SELECT NOW() as current_time', duration: '45ms', rows: 1 }
        Database connection successful: 2026-01-07T19:57:17.323Z
        Server is running on http://127.0.0.1:3000

Refactoring Required

At this point your site will be partially functional. The database connection and setup are working, but your controllers and templates still rely on the old static data structure. You need to update your controllers to use the new async database model functions and modify your templates to match the new data structure (see steps 6-8).

If the detailed logging becomes overwhelming during development, you can disable it by changing ENABLE_SQL_LOGGING=false in your .env file and restarting your server. If you encounter any errors during the connection or setup process there is a trouble-shooting section at the end of this assignment to help you resolve common issues.

6. Update Catalog Controller

The catalog controller was provided to prevent crashes, but it needs fixes. It currently uses getCourseById() and getSectionsByCourseId(), but your URLs contain slugs, not IDs. Update the imports and function calls to work with the slug-based routing.

Open src/controllers/catalog/catalog.js and update the imports at the top of the file:


        // Update these imports:
        import { getAllCourses, getCourseBySlug } from '../../models/catalog/courses.js';
        import { getSectionsByCourseSlug } from '../../models/catalog/catalog.js';

Then find the courseDetailPage function and update it to use slug-based functions instead of ID-based functions:


        // In courseDetailPage function, replace:
        const courseId = req.params.courseId;
        const course = await getCourseById(courseId);
        const sections = await getSectionsByCourseId(courseId, sortBy);
        
        // With:
        const courseSlug = req.params.slugId;
        const course = await getCourseBySlug(courseSlug);
        const sections = await getSectionsByCourseSlug(courseSlug, sortBy);

We ensured that the new model functions still return an empty object ({}) when no data is found. This means the existing error handling logic in the controller will work correctly with minimal changes. Feel free to add more to the error messages if desired:


        // Replace the existing error:
            if (Object.keys(course).length === 0) {
            const err = new Error(`Course ${courseId} not found`);
            err.status = 404;
            return next(err);
        }

        // With:
        if (Object.keys(course).length === 0) {
            const err = new Error(`Course ${courseSlug} not found`);
            err.status = 404;
            return next(err);
        }

Because we renamed the route parameter to :slugId, catalog detail pages will not work until you update the catalogs route definitions in src/controllers/routes.js:


        // In your route definitions, update:
        router.get('/catalog/:courseId', courseDetailPage);
        
        // To:
        router.get('/catalog/:slugId', courseDetailPage);

7. Update Faculty Controller

A new faculty controller was not included in the download files. You'll need to update your existing src/controllers/faculty/faculty.js file to work with the new database-driven model functions, following the same approach you used in the previous step for the catalog controller.

First, update the imports at the top of your faculty controller file to use the async model functions:


        import { getFacultyBySlug, getSortedFaculty } from '../../models/faculty/faculty.js';

Next, update both controller functions with the following changes:

Convert both functions to use async
Add await before all database calls
Update your error checking logic: database functions return an empty object ({}) when data is not found, so check with Object.keys(facultyMember).length === 0 instead of checking for null or undefined

Check Your Work

Here is what your updated faculty controller should look like:


            const facultyListPage = async (req, res) => {
                const validSortOptions = ['name', 'department', 'title'];
                const sortBy = validSortOptions.includes(req.query.sort) ? req.query.sort : 'department';
                const facultyList = await getSortedFaculty(sortBy);
                
                res.render('faculty/list', {
                    title: 'Faculty Directory',
                    faculty: facultyList,
                    currentSort: sortBy
                });
            };

            const facultyDetailPage = async (req, res, next) => {
                const facultySlug = req.params.facultySlug;
                const facultyMember = await getFacultyBySlug(facultySlug);

                if (Object.keys(facultyMember).length === 0) {
                    const err = new Error(`Faculty member ${facultySlug} not found`);
                    err.status = 404;
                    return next(err);
                }
                
                res.render('faculty/detail', {
                    title: `${facultyMember.name} - Faculty Profile`,
                    faculty: facultyMember
                });
            };

Here is what the faculty route definitions should look like:


            router.get('/faculty/:facultySlug', facultyDetailPage);

8. Update Your Templates

Now that your controllers are working with the database, you need to update all your templates (views) to use the new data structure and property names. The database returns data with different property names than your static objects used.

Use console.log() in your controllers to inspect the actual data structure being passed to your templates. This will help you understand exactly what properties are available.

Understanding the New Data Structure

The database uses different property names and structures than your static objects. Here are the key differences you need to know:

Courses: Use courseCode, name, creditHours, department, slug instead of old properties like id, title, credits
Sections: Sections are now a separate array with properties courseCode, courseName, professor, professorSlug, time, room (not embedded in the course object)
Faculty: Use firstName, lastName, name, slug, department, title, office, phone, email

Update catalog.ejs, course-detail.ejs, faculty/list.ejs, and faculty/detail.ejs to match these new property names. Always use console.log() in your controllers to verify the actual data structure before making changes to your templates, but don't forget to remove those logs afterward to keep your console clean.

Don't forget some courses have no sections! Your course detail templates should handle empty section lists gracefully but you could add conditional logic to display a message like "No sections available" if the sections array is empty. Try a different course that has sections to fully test your template.

9. Test Your Complete Application

With your controllers and templates updated, your application should now be fully functional with the database integration. Test all the major features to verify everything works correctly.

Test Core Pages

Visit each of these URLs to verify that your pages display correctly with the database-driven content:

Course catalog: http://127.0.0.1:3000/catalog - Should display all available courses with proper formatting
Course details: http://127.0.0.1:3000/catalog/cse-110 - Should show CSE 110 information and all available sections
Faculty directory: http://127.0.0.1:3000/faculty - Should list all faculty members
Faculty profile: http://127.0.0.1:3000/faculty/nathan-jack - Should display Nathan Jack's complete profile information

Test Sorting Functionality

Verify that sorting works correctly with query parameters:

http://127.0.0.1:3000/faculty?sort=name - Faculty sorted alphabetically by name
http://127.0.0.1:3000/faculty?sort=department - Faculty grouped by department
http://127.0.0.1:3000/catalog/cse-110?sort=time - Course sections sorted by time
http://127.0.0.1:3000/catalog/cse-110?sort=professor - Course sections sorted by professor name

Test Error Handling

Verify that your application handles errors gracefully by trying invalid URLs:

http://127.0.0.1:3000/faculty/invalid-person - Should display your 404 error page
http://127.0.0.1:3000/catalog/fake-course - Should display your 404 error page

If all tests pass, your database integration is complete and working correctly. If you encounter any issues, review the troubleshooting section at the end of this assignment for common problems and solutions.

Understanding What You Built

This assignment introduced several important concepts that form the foundation of database-driven web applications. Let's examine what you accomplished and why these patterns are essential for scalable development.

Connection Pool Management

Your application now uses a connection pool to manage database connections efficiently. Instead of creating a new connection for every query (which would be slow and resource-intensive), the pool maintains a set of reusable connections. This pattern is essential for applications that handle multiple concurrent users and frequent database operations.

Asynchronous Data Access

By converting your route handlers to use async/await, your application can now handle database queries without blocking other requests. When a query is running, Node.js can process other incoming requests, dramatically improving your application's ability to serve multiple users simultaneously.

Slug-Based Routing

The transition from simple IDs like "CS121" to slugs like "cse-110" provides several benefits: URLs are more user-friendly and SEO-optimized, the format is consistent regardless of course naming conventions, and internal database structure is not exposed in URLs.

Data Consistency and Relationships

The database structure includes proper relationships between courses, faculty, departments, and catalog entries. This ensures data consistency (a faculty member's department information is stored once and referenced everywhere) and enables complex queries that would be difficult with static objects.

Non-Destructive Setup Process

The setup script demonstrates professional database management practices. It can run multiple times safely, updates existing data when needed, and avoids losing information during development. This pattern is crucial for applications that need to evolve their database schema over time.

Key Concepts Summary

This assignment successfully transitioned your application from static data to a fully database-driven system. You learned how to establish database connections using connection pooling, integrate database setup scripts that run safely multiple times, convert synchronous route handlers to asynchronous patterns, and implement proper error handling for database operations.

More importantly, you experienced the practical benefits of database-driven development: your application can now handle much larger datasets without performance issues, data can be modified without code changes or server restarts, multiple users can access the same information concurrently, and the foundation is in place for advanced features like user-generated content and administrative interfaces.

The patterns you implemented — connection pooling, async operations, and proper error handling — are industry standards that you will use throughout your career in web development. Understanding how these pieces work together prepares you for building professional-grade applications that can scale to meet real-world demands.

Troubleshooting Common Issues

If you encounter problems during this assignment, here are some common issues and their solutions:

SSL Certificate Connection Errors

Verify the SSL certificate is downloaded and placed in the bin/ directory as byuicse-psql-cert.pem.

Database Connection Errors

If you see "Database connection failed" errors, verify that your database credentials in the .env file are correct and match the format exactly. Check that your connection string has no extra spaces or special characters, and confirm that your database server is accessible from your development environment.

Common Mistake

We use symbols for your database passwords which break connection strings if not URL-encoded. If your password contains any kind of symbols it needs to be URL-encoded. For example, if your password is p@ssw0rd!, it should be encoded as p%40ssw0rd%21 in the connection string. We provide the encoded versions from the same place you get your database credentials.

Server Startup Issues

If your server fails to start after adding database code, ensure that all your import paths use the .js extension and that file paths are correct relative to the importing file. Verify that you have installed the pg package with pnpm install pg.

Catalog Detail Page Shows 404 for Valid Courses

The provided catalog controller uses getCourseById but URLs contain slugs. Update it to use getCourseBySlug as shown in step 6.

Faculty Pages Not Working

The faculty controller was not provided. You must update your existing controller to use async/await and the new model functions (see step 7).

Templates Show Undefined or Missing Data

Property names have changed. Use console.log() in your controllers to inspect the actual data structure, then update your templates to use the new property names (see step 8).

404 Errors on Previously Working URLs

Remember that the database uses slugs instead of your previous ID format. URLs like /catalog/CS121 should now be /catalog/cse-110. Check the console output during database setup to see the actual slugs being created for your data.

Getting Help

When seeking help with database issues, include the specific error message, the relevant code section, and what you were trying to accomplish. Database errors often provide detailed information that can help identify the root cause quickly.

Conclusion

You have successfully transformed your static university catalog system into a dynamic, database-driven application. This represents a significant milestone in your development journey, as you now understand how real-world web applications store and retrieve data efficiently.

The database integration you completed provides the foundation for advanced features like content management systems, user authentication, data analytics, and administrative interfaces. You have learned industry-standard patterns for connection management, error handling, and asynchronous operations that will serve you throughout your career.

In future assignments, you will build upon this foundation by implementing more complex database queries, adding data validation and security measures, and creating user interfaces that allow dynamic data management. The skills you developed in this assignment — working with databases, handling asynchronous operations, and managing application state — are fundamental to modern web development.