Database Integration (CRUD)

So far, our Express server has only returned hardcoded JavaScript objects. If the Node.js server process restarts, all that data vanishes instantly because it only lived in temporary RAM. To build a production application, your API must connect to a Persistent Database (like PostgreSQL, MySQL, or MongoDB).

Why not just connect the React frontend directly to the database? Security. The frontend code is completely public. If you embedded your database password in your React code, anyone could extract it and wipe your entire database.

Instead, your Express API acts as the secure middleman. It runs on a private, secure server. It holds the database passwords in hidden environment files. When a client requests data, the API authenticates the user, safely queries the database on their behalf, and returns only the data they are authorized to see.

Historically, backend developers wrote raw SQL string queries directly inside their Node.js code (e.g., db.query('SELECT * FROM users')). This practice is widely discouraged today because it is extremely vulnerable to SQL Injection—a devastating hack where malicious users type raw SQL commands into login forms to destroy your database.

Modern enterprise backends use an ORM (Object-Relational Mapper) like Prisma, Sequelize, or TypeORM. An ORM allows you to interact with your database using secure, standardized JavaScript methods instead of raw SQL strings.

// Instead of dangerous raw SQL:
// db.query("SELECT * FROM users WHERE id = " + req.params.id);

// You use a safe ORM method:
const user = await prisma.user.findUnique({
  where: { id: req.params.id }
});

The ORM takes your clean JavaScript code, automatically sanitizes any malicious user input, translates it into highly optimized SQL, and executes it securely.

When you execute an ORM command inside an Express route, your API has to send a network request to the database server (which might be physically located in a different data center), wait for the database to search its hard drives, and wait for the data to travel back.

Because of this delay, database operations are Asynchronous. You MUST use the await keyword before every single ORM call, and your route callback must be labeled async.

app.get('/users/:id', async (req, res) => {
  // 1. AWAIT the slow database query
  const user = await prisma.user.findUnique({
    where: { id: parseInt(req.params.id) }
  });
  
  // 2. ONLY proceed once the data has returned
  res.json(user);
});

If you forget the await keyword, Node.js will not wait. It will instantly execute res.json(), returning a blank 'Promise' object to the client instead of the actual user data.

We now combine our routing knowledge with our ORM.

To Read data (a GET request), you extract the target ID from req.params, pass it into the ORM's find method, await the result, and return it via res.json().

To Create data (a POST request), you extract the incoming JSON payload from req.body, pass it into the ORM's create method, await the insertion, and return the newly generated database record along with a 201 Created HTTP status code.

app.post('/users', async (req, res) => {
  const newData = req.body;
  const newUser = await prisma.user.create({ data: newData });
  res.status(201).json(newUser);
});

Database Integration. So far, our Express server has only returned hardcoded JSON objects. If the server restarts, all data is lost. To build a real application, the API must connect to a persistent Database (like PostgreSQL or MongoDB). Your Express API acts as the secure middleman. When it receives a request from the client, it authenticates the user, then speaks to the database on the user's behalf to read or write data.

The ORM (Object-Relational Mapper). You could write raw SQL queries directly inside your Express routes, but this is messy and prone to security vulnerabilities (like SQL Injection). Modern backends use an ORM (Object-Relational Mapper) like Prisma or Sequelize. An ORM allows you to interact with your database using standard JavaScript methods. The ORM takes your JavaScript code, securely translates it into SQL, and executes it against the database.

Why do modern Node.js developers prefer using an ORM (like Prisma) inside their Express routes instead of writing raw SQL strings?

• →Because ORMs allow you to write clean JavaScript while automatically protecting against vulnerabilities like SQL Injection.
• →Because ORMs are faster than raw SQL.

Executing CRUD: Read. Let's put it all together. A client sends a GET request to /api/users/5. Your Express route intercepts it. You extract the 5 from req.params. You pass that 5 into your ORM's find method. Because database queries take time, this must be an asynchronous operation (using await). Once the database returns the user object, you send it back to the client using res.json().

Executing CRUD: Create. Creating data requires a POST route. The client sends a JSON payload containing the new user's name and email. Your Express server uses the express.json() middleware to attach this data to req.body. You then pass req.body into your ORM's create method. Finally, you respond with a 201 status code (which specifically means 'Created') and the newly generated database record.

When a POST request successfully creates a new resource in the database, which HTTP status code is the most semantically correct to send back to the client?

• →200 OK
• →201 Created

Module Complete. You have completed the backend integration module. You now understand the full cycle: the Client uses Fetch to send a request; Express intercepts it, extracts variables from req.params or req.body, and queries the Database using an ORM; finally, Express sends the data back using res.json(). You are ready to build full-stack applications.