Database Management - benhall/express-demo

Database Schema

The database schema is defined in the models/index.js file.

The schema includes the following tables:

  • users: This table stores information about users, including their username, email address, password, and role.
// models/index.js
const Sequelize = require('sequelize');
const config = require('../config/config.json');

const env = process.env.NODE_ENV || 'development';
const configObject = config[env];

let sequelize;
if (configObject.use_env_variable) {
  sequelize = new Sequelize(process.env[configObject.use_env_variable], configObject);
} else {
  sequelize = new Sequelize(
    configObject.database,
    configObject.username,
    configObject.password,
    configObject
  );
}

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.User = require('./user')(sequelize, Sequelize.DataTypes);
db.Task = require('./task')(sequelize, Sequelize.DataTypes);

db.User.hasMany(db.Task);
db.Task.belongsTo(db.User);

module.exports = db;
  • tasks: This table stores information about tasks, including their title, description, due date, and status.
// models/task.js
module.exports = (sequelize, DataTypes) => {
  const Task = sequelize.define('Task', {
    title: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    description: {
      type: DataTypes.TEXT,
    },
    dueDate: {
      type: DataTypes.DATE,
    },
    status: {
      type: DataTypes.ENUM('todo', 'inprogress', 'done'),
      defaultValue: 'todo',
    },
  });

  return Task;
};

This schema allows for the storage of user information and tasks, along with the ability to associate tasks with users through a foreign key relationship.

Querying the Database

This section details how to query the database in the benhall/express-demo application.

Basic Queries

The following code snippet demonstrates a basic query to fetch all records from the users table:

const express = require('express');
const app = express();
const db = require('./db'); // Assuming this file contains your database connection

app.get('/users', async (req, res) => {
  try {
    const users = await db.query('SELECT * FROM users');
    res.json(users.rows);
  } catch (err) {
    console.error(err);
    res.status(500).send('Error fetching users');
  }
});

app.listen(3000, () => console.log('Server started on port 3000'));

This code defines a route that handles GET requests to /users. It executes a SQL query to retrieve all records from the users table and sends the results as a JSON response.

Parameterized Queries

Parameterized queries are crucial for preventing SQL injection vulnerabilities. The following code demonstrates a parameterized query:

app.get('/users/:id', async (req, res) => {
  const userId = req.params.id;
  try {
    const user = await db.query('SELECT * FROM users WHERE id = $1', [userId]);
    res.json(user.rows);
  } catch (err) {
    console.error(err);
    res.status(500).send('Error fetching user');
  }
});

This code defines a route that handles GET requests to /users/:id. It retrieves the user ID from the request parameters and uses a parameterized query to fetch the corresponding record from the users table.

Inserting Data

To insert data into the database, you can use the db.query method with an INSERT statement. The following code demonstrates how to insert a new user:

app.post('/users', async (req, res) => {
  const { name, email } = req.body;
  try {
    const result = await db.query('INSERT INTO users (name, email) VALUES ($1, $2)', [name, email]);
    res.json({ message: 'User created successfully', id: result.rows[0].id });
  } catch (err) {
    console.error(err);
    res.status(500).send('Error creating user');
  }
});

This code defines a route that handles POST requests to /users. It retrieves the user’s name and email from the request body and uses a parameterized query to insert a new record into the users table.

Updating Data

Updating existing data in the database can be achieved using an UPDATE statement. The following code demonstrates how to update a user’s email address:

app.put('/users/:id', async (req, res) => {
  const userId = req.params.id;
  const { email } = req.body;
  try {
    const result = await db.query('UPDATE users SET email = $1 WHERE id = $2', [email, userId]);
    res.json({ message: 'User updated successfully' });
  } catch (err) {
    console.error(err);
    res.status(500).send('Error updating user');
  }
});

This code defines a route that handles PUT requests to /users/:id. It retrieves the user ID from the request parameters, the new email address from the request body, and updates the corresponding record in the users table.

Deleting Data

To delete data from the database, use a DELETE statement. The following code demonstrates how to delete a user:

app.delete('/users/:id', async (req, res) => {
  const userId = req.params.id;
  try {
    const result = await db.query('DELETE FROM users WHERE id = $1', [userId]);
    res.json({ message: 'User deleted successfully' });
  } catch (err) {
    console.error(err);
    res.status(500).send('Error deleting user');
  }
});

This code defines a route that handles DELETE requests to /users/:id. It retrieves the user ID from the request parameters and deletes the corresponding record from the users table.

Additional Resources