Connecting a Database to Your Express Server

So far you've learned two things separately: building API servers with Express, and working with databases using SQL. Now we connect them.

In the previous Express examples, sample data lived in a JavaScript array:

const samples = [
  { id: "S001", name: "Blood Sample A", od: 1.85, status: "pass" },
  // ...
];

When you restart the server, this array resets to its initial state. Even if you registered new samples via POST, they vanish on restart. It's like writing in a lab notebook with pencil and erasing everything each time.

Connecting a database solves this. When Express stores and retrieves data from a database instead of an array — even if the server shuts down or the computer restarts, data stays safe.

Setup: Installing the mysql2 Package

To connect to MySQL from Node.js, you need the mysql2 package:

npm install mysql2

Then write the database connection configuration in code:

const mysql = require("mysql2");

const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "your_password",
  database: "lab_db"
});

db.connect(function(err) {
  if (err) {
    console.log("DB connection failed:", err.message);
    return;
  }
  console.log("DB connected successfully");
});

It's like logging into lab equipment — you provide the address (host), account (user/password), and which database to use (database).

Replacing Arrays with DB: SELECT

Instead of arrays, use SQL queries to fetch data in your Express code.

Before (array):

app.get("/samples", function(req, res) {
  res.json(samples);
});

After (DB):

app.get("/samples", function(req, res) {
  db.query("SELECT * FROM samples", function(err, rows) {
    if (err) {
      res.status(500).json({ error: "DB query failed" });
      return;
    }
    res.json(rows);
  });
});

db.query() sends SQL to the database and receives results via callback. rows comes back as an array — the same structure as the array you manually created before. No changes needed on the frontend.

Looking Up Specific Samples: WHERE and Placeholders

When querying specific samples via URL parameters, putting user input directly into SQL exposes you to a security attack called SQL injection. Use placeholders (?):

app.get("/sample/:id", function(req, res) {
  db.query(
    "SELECT * FROM samples WHERE id = ?",
    [req.params.id],
    function(err, rows) {
      if (err) {
        res.status(500).json({ error: "Query failed" });
        return;
      }
      if (rows.length === 0) {
        res.status(404).json({ error: "Sample not found" });
        return;
      }
      res.json(rows[0]);
    }
  );
});

The value [req.params.id] is safely inserted into the ? position. This way, even if a malicious user puts SQL code in the URL, the database treats it as data only.

It's like having a blank field in a protocol for the sample number — whatever goes in that field is interpreted only as a sample number and can't alter the protocol itself.

Registering Samples: INSERT

Register new samples via POST requests and permanently save them to the database:

app.use(express.json());

app.post("/samples", function(req, res) {
  const { name, od, status } = req.body;

  db.query(
    "INSERT INTO samples (name, od, status, created_at) VALUES (?, ?, ?, NOW())",
    [name, od, status],
    function(err, result) {
      if (err) {
        res.status(500).json({ error: "Registration failed" });
        return;
      }
      res.json({
        message: "Sample registered",
        id: result.insertId
      });
    }
  );
});

result.insertId is the auto-generated ID of the row just added. NOW() is a MySQL function that automatically inserts the current time.

Full Example: Sample Management CRUD API

Here's the complete server code combining everything learned:

const express = require("express");
const mysql = require("mysql2");
const app = express();

app.use(express.json());

const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "your_password",
  database: "lab_db"
});

// Full sample list
app.get("/samples", function(req, res) {
  db.query("SELECT * FROM samples ORDER BY created_at DESC", function(err, rows) {
    if (err) return res.status(500).json({ error: err.message });
    res.json(rows);
  });
});

// Look up specific sample
app.get("/sample/:id", function(req, res) {
  db.query("SELECT * FROM samples WHERE id = ?", [req.params.id], function(err, rows) {
    if (err) return res.status(500).json({ error: err.message });
    if (rows.length === 0) return res.status(404).json({ error: "Sample not found" });
    res.json(rows[0]);
  });
});

// Register sample
app.post("/samples", function(req, res) {
  const { name, od, status } = req.body;
  db.query(
    "INSERT INTO samples (name, od, status, created_at) VALUES (?, ?, ?, NOW())",
    [name, od, status],
    function(err, result) {
      if (err) return res.status(500).json({ error: err.message });
      res.json({ message: "Registration complete", id: result.insertId });
    }
  );
});

// Filter QC-passed samples only
app.get("/samples/passed", function(req, res) {
  db.query("SELECT * FROM samples WHERE status = 'pass'", function(err, rows) {
    if (err) return res.status(500).json({ error: err.message });
    res.json({ count: rows.length, samples: rows });
  });
});

app.listen(3000, function() {
  console.log("Sample management API server: http://localhost:3000");
});

What's special about this server — the API interface is identical to the array-based Express server. /samples, /sample/:id, /samples/passed — same URLs, same response format. Only the internal storage changed. Not a single line of frontend code needs modification.

This is the biggest benefit of separating backend and frontend. Whether you switch storage from files to MySQL, or MySQL to PostgreSQL, as long as the API stays the same, the frontend is unaffected.

Try It Yourself (Faded Example)

Fill in the blanks to complete an Express route that queries samples by researcher.

app.get("/researcher/:name/samples", function(req, res) {
db.(
  "SELECT * FROM samples WHERE researcher = ",
  [req..name],
  function(err, rows) {
    if (err) return res.status(500).json({ error: err.message });
    res.json(rows);
  }
);
});

Common Errors & Solutions

Q: ER_ACCESS_DENIED_ERROR: Access denied for user error

Check that user, password, and database in createConnection are correct. The MySQL account must exist and have access permissions for that database.

Q: ECONNREFUSED error

The MySQL server isn't running. On Mac, start it with brew services start mysql; on Linux, use sudo systemctl start mysql.

Q: Query returns an empty array []

Either the table has no data, or no rows match the WHERE condition. First run SELECT * FROM samples; directly in the MySQL client to verify data exists.

Q: Non-ASCII characters are garbled when saved

Check that the database and table charset is utf8mb4. You can change it with ALTER DATABASE lab_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;.