Como migrar seu aplicativo Node.js do SQL para o MongoDB

1
CREATE  TABLE  users (
2
 id  SERIAL PRIMARY KEY,
3
username VARCHAR(100) UNIQUE  NOT  NULL,
4
 password  VARCHAR(100) NOT  NULL,
5
email VARCHAR(100) UNIQUE  NOT  NULL,
6
created_at TIMESTAMP  DEFAULT  CURRENT_TIMESTAMP
7
);
8
CREATE  TABLE contacts (
9
 id  SERIAL PRIMARY KEY,
10
 name  VARCHAR(100) NOT  NULL,
11
phone VARCHAR(20) NOT  NULL,
12
email VARCHAR(100),
13
user_id INTEGER  REFERENCES  users(id) ON  DELETE  CASCADE,
14
created_at TIMESTAMP  DEFAULT  CURRENT_TIMESTAMP
15
);
16
CREATE  TABLE rates (
17
 id  SERIAL PRIMARY KEY,
18
 description  TEXT  NOT  NULL,
19
contact_id INTEGER  REFERENCES contacts(id) ON  DELETE  CASCADE,
20
user_id INTEGER  REFERENCES  users(id) ON  DELETE  CASCADE,
21
created_at TIMESTAMP  DEFAULT  CURRENT_TIMESTAMP
22
);

1
"dependencies": {
2
 "bcryptjs": "^2.4.3",
3
 "express": "^4.19.2",
4
 "jsonwebtoken": "^9.0.2",
5
 "pg": "^8.11.5"
6
}

1
npm install bcryptjs express jsonwebtoken pg

1
DB_USER=your_username
2
DB_HOST=localhost
3
DATABASE=your_DB_name
4
DB_PASSWORD=your_password
5
DB_PORT=5432
6
SECRET_KEY=your_jwt_secret_key

1
import express from  'express';
2
import bcrypt from  'bcryptjs';
3
import jwt from  'jsonwebtoken';
4
import pg from 'pg';
5
6
const { Pool } = pg;
7
8
const app = express();
9
const port = 3000;
10
11
app.use(express.json());
12
13
const pool = new Pool({
14
 user: process.env.DB_USER,
15
 host: process.env.DB_HOST,
16
 database: process.env.DATABASE,
17
 password: process.env.DB_PASSWORD,
18
 port: process.env.DB_PORT,
19
});
20
21
// JWT secret key
22
const SECRET_KEY = process.env.SECRET_KEY;
23
24
/* START FEATURE IMPLEMENTATION*/
25
26
/* END FEATURE IMPLEMENTATION*/
27
28
// Start server
29
app.listen(port, () => {
30
 console.log(`Server running on port ${port}`);
31
});

1
node --env-file=.env.postgres postgres.js

1
app.post('/register', async (req, res) => {
2
 const { username, email, password } = req.body;
3
 const salt = await bcrypt.genSalt(10);
4
 const hashedPassword = await bcrypt.hash(password, salt);
5
 const result = await pool.query(
6
 'INSERT INTO users (username, email, password) VALUES ($1, $2, $3) RETURNING *',
7
            [username, email, hashedPassword]
8
    );
9
    const {  ...user } = result.rows[0];
10
11
    res.status(201).json({...user, password: null});
12
});

1
app.post('/login', async (req, res) => {
2
    const { email, password } = req.body;
3
4
    const user = await pool.query('SELECT * FROM users WHERE email = $1', [email]);
5
6
    let validPass = false;
7
8
    if (user.rows[0]) validPass = await bcrypt.compare(password, user.rows[0].password);
9
10
    if (!validPass) return res.status(400).json({ message: 'Invalid credentials' });
11
12
    const token = jwt.sign({ id: user.rows[0].id, email: user.rows[0].email }, SECRET_KEY, { expiresIn: '1h' });
13
14
    res.status(200).json({ token });
15
16
});

1
const authenticateToken = (req, res, next) => {
2
 const token = req.header('Authorization');
3
 if (!token) return res.status(401).json({ message: 'Access Denied' });
4
 const verified = jwt.verify(token.split(" ")[1], SECRET_KEY);
5
    req.user = verified;
6
    next();
7
};

1
app.post('/contacts', authenticateToken, async (req, res) => {
2
 const { name, phone, email } = req.body;
3
 const result = await pool.query(
4
 'INSERT INTO contacts (name, phone, email, user_id) VALUES ($1, $2, $3, $4) RETURNING *',
5
            [name, phone, email, req.user.id]
6
        );
7
    res.status(201).json(result.rows[0]);
8
});

1
app.get('/contacts/:id', authenticateToken, async (req, res) => {
2
 const { id } = req.params;
3
 const result = await pool.query('SELECT * FROM contacts WHERE id = $1 AND user_id = $2', [id, req.user.id]);
4
 if (result.rows.length === 0) return res.status(404).json({ message: 'Contact not found' });
5
    res.status(200).json(result.rows[0]);
6
});

1
app.put('/contacts/:id', authenticateToken, async (req, res) => {
2
 const { id } = req.params;
3
 const { name, phone, email } = req.body;
4
 const result = await pool.query(
5
 'UPDATE contacts SET name = $1, phone = $2, email = $3 WHERE id = $4 AND user_id = $5 RETURNING *',
6
            [name, phone, email, id, req.user.id]\
7
    );
8
 if (result.rows.length === 0) return res.status(404).json({ message: 'Contact not found or not authorized' });
9
    res.status(200).json(result.rows[0]);
10
});

1
app.delete('/contacts/:id', authenticateToken, async (req, res) => {
2
 const { id } = req.params;
3
 const result = await pool.query('DELETE FROM contacts WHERE id = $1 AND user_id = $2 RETURNING *', [id, req.user.id]);
4
 if (result.rows.length === 0) return res.status(404).json({ message: 'Contact not found or not authorized' });
5
    res.status(200).json(result.rows[0]);
6
});

1
app.post('/contacts/:id/rates', authenticateToken, async (req, res) => {
2
 const { id } = req.params;
3
 const { description } = req.body;
4
 const result = await pool.query(
5
 'INSERT INTO rates (body, contact_id, user_id) VALUES ($1, $2, $3) RETURNING *',
6
    [description, id, req.user.id]
7
  );
8
  res.status(201).json(result.rows[0]);
9
});

1
app.get('/contacts', async (req, res) => {
2
 let { startDate, endDate } = req.query;
3
 if (!endDate) {
4
endDate = new  Date().toISOString();
5
} else {
6
endDate = new  Date(endDate).toISOString();
7
      }
8
9
 if (!startDate) {\
10
 const now = new  Date();
11
 const pastDate = new  Date(now);
12
pastDate.setDate(now.getDate() - 7);
13
        startDate = pastDate.toISOString();
14
} else {
15
startDate = new  Date(startDate).toISOString();
16
      }
17
18
 const result = await pool.query(`
19
            SELECT
20
                contacts.id, contacts.name, contacts.phone, contacts.created_at,
21
                users.username,
22
                COUNT(rates.id) AS call_rate
23
            FROM contacts
24
            LEFT JOIN users ON contacts.user_id = users.id
25
            LEFT JOIN rates ON contacts.id = rates.contact_id
26
            WHERE contacts.created_at BETWEEN $1 AND $2
27
            GROUP BY contacts.id, users.username
28
            ORDER BY call_rate DESC
29
        `, [startDate, endDate]);
30
  res.status(200).json(result.rows);
31
});

1
"dependencies": {
2
 "express": "^4.19.2",
3
 "mongodb": "^8.4.0",
4
 "bcryptjs": "^2.4.3",
5
 "jsonwebtoken": "^9.0.2",
6
}

1
npm install mongodb express bcryptjs jsonwebtoken

1
SECRET_KEY=your_jwt_secret_key
2
DB_USER=your_cluster_username
3
DB_PASSWORD=your_cluster_password
4
DATABASE=users
5
CLUSTER_URI=your_cluster_uri

1
import { MongoClient, ObjectId } from 'mongodb';
2
import express from  'express';
3
import bcrypt from  'bcryptjs';
4
import jwt from  'jsonwebtoken';
5
6
const app = express();
7
app.use(express.json());
8
9
const url = `mongodb+srv://${process.env.DB_USER}:${process.env.DB_PASSWORD}@${process.env.CLUSTER_URI}`;
10
const client = new MongoClient(url);
11
const dbName = process.env.DATABASE;
12
const SECRET_KEY = process.env.SECRET_KEY;
13
14
await client.connect();
15
const db = client.db(dbName);
16
const usersCollection = db.collection('users');
17
const contactsCollection = db.collection('contacts');
18
19
/* START FEATURE IMPLEMENTATION*/
20
21
/* END FEATURE IMPLEMENTATION*/
22
app.listen(3000, () => {
23
 console.log('Server is running on port 3000');
24
});

1
node --env-file=.env.mongodb mongodb.js

1
CREATE  TABLE  users (
2
 id  SERIAL PRIMARY KEY,
3
username VARCHAR(100) UNIQUE  NOT  NULL,
4
 password  VARCHAR(100) NOT  NULL,
5
email VARCHAR(100) UNIQUE  NOT  NULL,
6
created_at TIMESTAMP  DEFAULT  CURRENT_TIMESTAMP
7
);

1
app.post('/register', async (req, res) => {
2
 const { username, email, password } = req.body;
3
4
    const salt = await bcrypt.genSalt(10);
5
 const hashedPassword = await bcrypt.hash(password, salt);
6
 const user = { username, email, password: hashedPassword };
7
 const result = await usersCollection.insertOne(user);
8
    res.status(201).json({...user, password: null});
9
  });

1
app.post('/login', async (req, res) => {
2
 const { email, password } = req.body;
3
  const user = await usersCollection.findOne({ email });
4
5
  let validPass = false;
6
7
  if (user) validPass = await bcrypt.compare(password, user.password);
8
9
  if (!validPass) return res.status(400).json({ message: 'Invalid credentials' });
10
11
    const token = jwt.sign({ sub: user._id, email: user.email }, SECRET_KEY, { expiresIn: '1h' });
12
    res.status(200).json({ token });
13
});

1
const authenticateToken = (req, res, next) => {
2
 const token = req.header('Authorization');
3
 if (!token) return res.status(401).json({ message: 'Access Denied' });
4
 const verified = jwt.verify(token.split(" ")[1], SECRET_KEY);
5
    req.user = verified;
6
    next();
7
};

1
app.post('/contacts', authenticateToken, async (req, res) => {
2
 const { name, phone, email } = req.body;
3
 const contact = { name, phone, email, user_id: req.user.sub, createdAt: new Date().toISOString() };
4
 const result = await contactsCollection.insertOne(contact);
5
    res.status(201).json(contact);
6
});

1
app.get('/contacts/:id', authenticateToken, async (req, res) => {
2
 const { id } = req.params;
3
 const contact = await contactsCollection.findOne({ _id: new ObjectId(id), user_id: req.user.sub });
4
 if (!contact) return res.status(404).json({ message: 'Contact not found' });
5
    res.status(200).json(contact);
6
});

1
app.put('/contacts/:id', authenticateToken, async (req, res) => {
2
 const { id } = req.params;
3
 const { name, phone, email } = req.body;
4
 const contact = await contactsCollection.findOneAndUpdate(
5
{ _id: new ObjectId(id), user_id: req.user.sub },
6
{ $set: req.body },
7
{ returnOriginal: false }
8
      );
9
 if (!contact) return res.status(404).json({ message: 'Contact not found or not authorized' });
10
    res.status(200).json(contact);
11
});

1
app.delete('/contacts/:id', authenticateToken, async (req, res) => {
2
 const { id } = req.params;
3
 const contact = await contactsCollection.deleteOne({ _id: new ObjectId(id), user_id: req.user.sub });
4
 if (!contact.deletedCount) return res.status(404).json({ message: 'Contact not found or not authorized' });
5
    res.status(200).json(contact.value);
6
});

1
app.post('/contacts/:id/rates', authenticateToken, async (req, res) => {
2
 const { id } = req.params;
3
 const { description } = req.body;
4
 const rate = { description, createdAt: new  Date().toISOString(), user_id: new ObjectId(req.user.id) };
5
 const result = await contactsCollection.updateOne(
6
{ _id: new ObjectId(id), user_id: req.user.sub },
7
{ $push: { rates: rate } }
8
    );
9
 if (result.matchedCount === 0) return res.status(404).json({ message: 'Contact not found ' });
10
    res.status(201).json(rate);
11
});

1
app.get("/contacts", authenticateToken, async (req, res) => {
2
 let { startDate, endDate } = req.query;
3
4
 if (!endDate) {
5
endDate = new  Date().toISOString();
6
} else {
7
endDate = new  Date(endDate).toISOString();
8
  }
9
10
 if (!startDate) {
11
 const now = new  Date();
12
 const pastDate = new  Date(now);
13
pastDate.setDate(now.getDate() - 7);
14
15
    startDate = pastDate.toISOString();
16
} else {
17
startDate = new  Date(startDate).toISOString();
18
  }
19
20
 const contacts = await contactsCollection
21
    .aggregate([
22
      {
23
 $match: {
24
 createdAt: {
25
 $gte: startDate,
26
 $lte: endDate,
27
          },
28
 user_id: req.user.sub
29
        },
30
      },
31
      {
32
 $lookup: {
33
 from: "users",
34
 localField: "user_id",
35
 foreignField: "_id",
36
 as: "user",
37
        },
38
      },
39
      {
40
 $addFields: {
41
 call_rate: { $size: { $ifNull: ["$rates", []] } },
42
        },
43
      },
44
{ $sort: { call_rate: -1 } },
45
    ])
46
    .toArray();
47
48
  res.status(200).json(contacts);
49
});

1
mongoimport mongodb+srv://<cluster-username>:<cluster-password>@cluster0.33t5arb.mongodb.net/Node-API?retryWrites=true&w=majority&appName=Cluster0 --collection=user --file=users.json

1
// Select the database to use.
2
use('database-name');
3
// Insert documents into the users collection.
4
db.getCollection('users').insertMany("Copy and paste the content of your JSON file here");