How do I add a Postgres database to the codebase? - benhall/express-demo

Adding a Postgres Database to the Express Demo Codebase

This documentation provides guidance on integrating a Postgres database into the benhall/express-demo codebase.

1. Install Dependencies

First, install the necessary dependencies to connect and interact with the Postgres database:

npm install pg

This command installs the pg package, which provides a PostgreSQL client library for Node.js.

2. Configure Database Connection

Create a .env file in the root of your project and add the following environment variables:

DATABASE_URL=postgres://user:password@host:port/database

Replace the placeholders with your actual database credentials. For example:

DATABASE_URL=postgres://myuser:mypassword@localhost:5432/mydatabase

You can also set these environment variables directly in your terminal or through your operating system’s environment settings.

3. Establish Database Connection

In your app.js file, import the pg library and establish a connection to the database:

const pg = require('pg');

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

This code snippet establishes a connection pool using the environment variable DATABASE_URL and handles potential errors.

4. Interact with the Database

You can now use the pool object to interact with the database. For example, to execute a query:

pool.query('SELECT NOW()', (err, result) => {
  if (err) {
    console.error(err);
    return;
  }
  console.log(result.rows[0].now);
});

This code snippet executes a simple query to retrieve the current date and time from the database.

5. Incorporate Database Operations into Routes

Integrate database interactions into your routes in routes/index.js:

// ... existing code ...

router.get('/data', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM your_table');
    res.send(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).send('Error fetching data');
  }
});

This code snippet defines a route that retrieves data from the specified table (your_table) and sends it as a response.

6. Handle Database Transactions

For more complex operations involving multiple database modifications, consider using transactions:

pool.query('BEGIN', (err) => {
  if (err) {
    // Handle error
    return;
  }

  pool.query('INSERT INTO your_table (column1, column2) VALUES ($1, $2)', [value1, value2], (err, result) => {
    if (err) {
      // Handle error
      pool.query('ROLLBACK', (err) => {
        // Handle error
      });
      return;
    }

    // ... other database operations ...

    pool.query('COMMIT', (err) => {
      if (err) {
        // Handle error
      }
    });
  });
});

This code snippet starts a transaction, executes multiple queries, and commits or rolls back the transaction depending on the outcome of each query.

Note: This documentation provides a basic outline. You may need to adapt these steps based on the specific requirements of your application and database schema. Remember to implement proper error handling and security measures for production environments.

For more detailed information on interacting with Postgres databases in Node.js, refer to the official pg documentation: https://node-postgres.com/