Create new postgres model with express
Here is an example of how to create a new PostgreSQL model using Express.js:
Step 1: Install required dependencies
Run the following command in your terminal:
npm install express pg
This will install Express.js and the pg
module, which is a PostgreSQL driver for Node.js.
Step 2: Create a new Express.js app
Create a new file called app.js
and add the following code:
const express = require('express');
const app = express();
const pg = require('pg');
// Set up PostgreSQL connection
const db = new pg.Pool({
user: 'your_username',
host: 'your_host',
database: 'your_database',
password: 'your_password',
port: 5432
});
app.use(express.json());
// Define the model
const User = {
tableName: 'users',
attributes: {
id: 'integer',
name: 'text',
email: 'text'
}
};
// Define the model's methods
User.findAll = async () => {
const result = await db.query(`SELECT * FROM ${User.tableName}`);
return result.rows;
};
User.findById = async (id) => {
const result = await db.query(`SELECT * FROM ${User.tableName} WHERE id = $1`, [id]);
return result.rows[0];
};
User.create = async (data) => {
const result = await db.query(`INSERT INTO ${User.tableName} (name, email) VALUES ($1, $2) RETURNING *`, [data.name, data.email]);
return result.rows[0];
};
User.update = async (id, data) => {
const result = await db.query(`UPDATE ${User.tableName} SET name = $1, email = $2 WHERE id = $3 RETURNING *`, [data.name, data.email, id]);
return result.rows[0];
};
User.delete = async (id) => {
await db.query(`DELETE FROM ${User.tableName} WHERE id = $1`, [id]);
};
// Export the model
module.exports = User;
This code defines a User
model with methods for finding all users, finding a user by ID, creating a new user, updating a user, and deleting a user.
Step 3: Use the model in your Express.js app
Create a new file called routes.js
and add the following code:
const express = require('express');
const router = express.Router();
const User = require('./models/User');
router.get('/users', async (req, res) => {
const users = await User.findAll();
res.json(users);
});
router.get('/users/:id', async (req, res) => {
const id = req.params.id;
const user = await User.findById(id);
res.json(user);
});
router.post('/users', async (req, res) => {
const data = req.body;
const user = await User.create(data);
res.json(user);
});
router.put('/users/:id', async (req, res) => {
const id = req.params.id;
const data = req.body;
const user = await User.update(id, data);
res.json(user);
});
router.delete('/users/:id', async (req, res) => {
const id = req.params.id;
await User.delete(id);
res.json({ message: 'User deleted successfully' });
});
module.exports = router;
This code defines routes for finding all users, finding a user by ID, creating a new user, updating a user, and deleting a user.
Step 4: Start the Express.js app
Run the following command in your terminal:
node app.js
This will start the Express.js app and make the routes available at http://localhost:3000
.
That's it! You now have a new PostgreSQL model with Express.js. You can use the routes to interact with the model and perform CRUD operations.