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.