Why SQL integration matters in Express

Introduction

SQL integration helps your Express app talk to databases easily. It lets you save, find, and change data quickly.

You want to store user info like names and passwords.

You need to get a list of products from a database.

You want to update records when users change their settings.

You want to delete old data automatically.

You want to combine data from different tables for reports.

Syntax

Express

const { Client } = require('pg');

const client = new Client({
  connectionString: 'your-database-url'
});

client.connect();

client.query('SELECT * FROM users', (err, res) => {
  if (err) throw err;
  console.log(res.rows);
  client.end();
});

This example uses the 'pg' library for PostgreSQL with Express.

You connect to the database, run a query, then close the connection.

Examples

Insert a new user safely using placeholders to avoid errors.

Express

client.query('INSERT INTO users(name, email) VALUES($1, $2)', ['Alice', '[e-mail]']);

Update a user's email by their ID.

Express

client.query('UPDATE users SET email = $1 WHERE id = $2', ['[e-mail]', 5]);

Delete a user with a specific ID.

Express

client.query('DELETE FROM users WHERE id = $1', [10]);

Sample Program

This Express app connects to a PostgreSQL database. When you visit '/users', it fetches user IDs and names from the database and sends them as JSON.

Express

const express = require('express');
const { Client } = require('pg');

const app = express();
const client = new Client({
  connectionString: 'postgresql://user:password@localhost:5432/mydb'
});

app.get('/users', async (req, res) => {
  try {
    const result = await client.query('SELECT id, name FROM users');
    res.json(result.rows);
  } catch (error) {
    res.status(500).send('Database error');
  }
});

client.connect()
  .then(() => {
    app.listen(3000, () => {
      console.log('Server running on http://localhost:3000');
    });
  })
  .catch(err => {
    console.error('Database connection failed:', err);
  });

Important Notes

Always handle database errors to avoid crashes.

Use parameterized queries to protect against SQL injection.

Keep your database connection info safe and do not hardcode passwords in real apps.

Summary

SQL integration lets Express apps work with databases to store and get data.

It is useful for user info, products, and any data-driven features.

Use safe queries and handle errors for a smooth app experience.