Building a Basic API with Node.js and Express

1. The Database Connection Layer

First things first, our API needs to talk to a database. Instead of opening a new connection inside every controller method, we'll create a dedicated class to manage the database connection. This centralizes the connection logic and makes it reusable. We will use PDO (PHP Data Objects), which provides a consistent interface for accessing various databases.

Before we write the code, you'll need a database and a table. You can use a tool like phpMyAdmin or the MySQL command line to run the following SQL script. This creates a simple product table that we'll use for our examples.

CREATE DATABASE product_db;
USE product_db;

CREATE TABLE product (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(128) NOT NULL,
    size INT DEFAULT NULL,
    is_available BOOLEAN DEFAULT TRUE,
    PRIMARY KEY (id)
);

-- Insert some sample data
INSERT INTO product (name, size, is_available) VALUES
('Small Widget', 10, true),
('Medium Widget', 25, false),
('Large Widget', 50, true);

Now, let's create the PHP class to handle the connection.

Based on the video, you'll create a new file, for example at src/Database.php:

<?php

class Database
{
    private ?PDO $conn = null;

    public function __construct(
        private string $host,
        private string $name,
        private string $user,
        private string $password
    ) {}

    public function getConnection(): PDO
    {
        if ($this->conn === null) {
            $dsn = "mysql:host={$this->host};dbname={$this->name};charset=utf8";
            
            $this->conn = new PDO($dsn, $this->user, $this->password, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_STRINGIFY_FETCHES => false,
to_string => false
            ]);
        }
        
        return $this->conn;
    }
}

Note: I've slightly modified the class from the video to instantiate the PDO object only when getConnection() is first called (a pattern called lazy initialization) and added some common, recommended PDO attributes.

2. The Gateway: Separating Logic from Data Access

Our ProductController should handle the flow of the request, but it shouldn't contain raw SQL queries. To keep our code organized and maintain a clear separation of concerns, we will introduce a Table Data Gateway.

A Gateway is a class that acts as an interface to a single database table. All the SQL needed to interact with the product table will live inside a ProductGateway class. The controller will then use this gateway to perform database operations without ever needing to know the underlying SQL.

Let's see how to build this gateway.

Following the video's example, your src/ProductGateway.php would look like this:

<?php

class ProductGateway
{
    private PDO $conn;

    public function __construct(Database $database)
    {
        $this->conn = $database->getConnection();
    }

    public function getAll(): array
    {
        $sql = "SELECT * FROM product";
        $stmt = $this->conn->query($sql);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

3. Processing a GET Request

Now we have all the pieces to handle a GET request to /products. We need to update our index.php and ProductController.php to wire everything together.

index.php (updated)

<?php

// Assume an autoloader is set up
spl_autoload_register(function ($class) {
    require __DIR__ . "/src/$class.php";
});

header("Content-type: application/json; charset=UTF-8");

// Database connection
$database = new Database("localhost", "product_db", "your_username", "your_password");

// Gateway
$gateway = new ProductGateway($database);

// Controller
$controller = new ProductController($gateway); // Pass the gateway to the controller

// ... (rest of the routing logic from previous lesson)
$parts = explode("/", $_SERVER["REQUEST_URI"]);
// ... and so on

ProductController.php (updated)
We need to modify the controller to accept the gateway and use it.

<?php

class ProductController
{
    public function __construct(private ProductGateway $gateway)
    {
    }

    public function processRequest(string $method, ?string $id): void
    {
        if ($id) {
            $this->processResourceRequest($method, $id);
        } else {
            $this->processCollectionRequest($method);
        }
    }

    private function processCollectionRequest(string $method): void
    {
        switch ($method) {
            case "GET":
                // Call the gateway to get all products
                $products = $this->gateway->getAll();
                // Output the data as JSON
                echo json_encode($products);
                break;
            // ... other cases
        }
    }
    
    // ... processResourceRequest method
}

Now, when you make a GET request to /products, the controller calls the gateway, which queries the database, and the results are returned as a JSON array. We have successfully processed a request and returned data from the database!

4. Processing a POST Request

Handling a POST request to create a new product is more involved. We need to:

1. Read the incoming data from the request body (which will be in JSON format).
2. Validate the data.
3. Insert the new record into the database using a prepared statement.

Using prepared statements is critical for security. They separate the SQL query from the data, which is the most effective way to prevent SQL injection attacks—a key topic in your cybersecurity interests.

This next video segment covers this entire process in detail.

Let's add the create method to our ProductGateway:

ProductGateway.php (updated create method)

public function create(array $data): string
{
    $sql = "INSERT INTO product (name, size, is_available)
            VALUES (:name, :size, :is_available)";
            
    $stmt = $this->conn->prepare($sql);
    
    $stmt->bindValue(":name", $data["name"], PDO::PARAM_STR);
    $stmt->bindValue(":size", $data["size"] ?? 0, PDO::PARAM_INT);
    $stmt->bindValue(":is_available", $data["is_available"] ?? false, PDO::PARAM_BOOL);
    
    $stmt->execute();
    
    return $this->conn->lastInsertId();
}

And update the ProductController to handle the POST case:

ProductController.php (updated POST case)

// ... inside processCollectionRequest method
switch ($method) {
    case "GET":
        // ... as before
        break;
    case "POST":
        // 1. Get and decode the JSON body
        $data = (array) json_decode(file_get_contents("php://input"), true);
        
        // 2. Simple validation (we'll cover this more later)
        if (empty($data['name'])) {
            http_response_code(422); // Unprocessable Entity
            echo json_encode(["errors" => "Name is required"]);
            break;
        }

        // 3. Call the gateway to create the product
        $id = $this->gateway->create($data);
        
        // 4. Return a success response
        http_response_code(201); // Created
        echo json_encode([
            "message" => "Product created",
            "id" => $id
        ]);
        break;
    // ... other cases
}

Now you can use a tool like Postman or Insomnia to send a POST request to /products with a JSON body like {"name": "New Gadget", "size": 5} and see a new product get created in your database.

Conclusion

Excellent work! You've made a huge leap forward. Your API is no longer a set of dummy endpoints; it's a functional application that can read from and write to a database.

Key Takeaways:

• Database Class: A dedicated class for managing the PDO connection is a clean and reusable pattern.
• Table Gateway Pattern: Using a gateway class (ProductGateway) isolates all your SQL from your application logic (ProductController), which makes your code easier to maintain.
• Processing GET: The controller uses the gateway to fetch data and then json_encode()s it for the response.
• Processing POST: The controller reads the JSON request body, validates it, and uses the gateway to insert the data.
• Security: Using prepared statements in your gateway is non-negotiable for preventing SQL injection attacks.

Preview of the Next Lesson:
We've started sending JSON and setting HTTP status codes (201, 422), but we can make our responses more consistent and robust. In the next lesson, we will focus on how to "Format and return responses in JSON format with correct HTTP status codes". We'll build a standardized response structure and ensure we're always sending the right status code for every situation, from success to different types of errors.