Why Database Design and API Design Belong Together?

You're making a peanut butter and jelly sandwich. You’ve got two slices of bread (your front-end app and back-end server), and the peanut butter and jelly (your database and API).

If you don’t spread them in the right order or you use the wrong amount, the sandwich could end up being a disaster. The same thing goes for database design and API design. They need to be in perfect harmony if you want your app to work like a charm.

Let’s dive into how database design and API design are more connected than you might think. When you're developing an API, you're essentially building a bridge to access data stored in a database. And just like in any good relationship, communication is key.

Let’s take a look at how you can connect database design and API design using concrete examples with some actual code. We'll go through a few concepts step-by-step to really bring it home. Buckle up! We're diving into the PB&J of development.

Schemas & Endpoints: Mapping Your Database to API Routes

The first thing we need is a schema for the database. Let’s say we’re building an API for a simple blogging platform. Here's what the database schema might look like for two main entities: Users and Posts.

Database Design (Schema):

-- Users table
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts table
CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

In this design:

• The users table stores the information about the users.
• The posts table stores blog posts and has a foreign key (user_id) that references the users table (showing the relationship between a user and their posts).

API Design (Endpoints):

Now, when designing the API to interact with this data, we need to expose endpoints for interacting with both users and posts.

• GET /users/{id} – Get a specific user.
• POST /users – Create a new user.
• GET /users/{id}/posts – Get all posts by a user.
• POST /posts – Create a new post.

// Step 1: Import necessary modules
const express = require('express');  
const app = express();  
const mysql = require('mysql');  // Import MySQL module to connect to the database
const bodyParser = require('body-parser');  

// Step 2: Set up the connection to the MySQL database
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',  // MySQL username
  password: 'password',  // MySQL password
  database: 'blog_db'  // The database name we're using (in this case, 'blog_db')
});

// Step 3: Middleware to parse JSON requests
app.use(bodyParser.json());  // This allows us to access `req.body` as a JSON object

// Step 4: Endpoint to get user details by their ID
// When a request is made to '/users/:id', this route will get a user from the database by their ID
app.get('/users/:id', (req, res) => {
  const userId = req.params.id;  // Get the user ID from the URL parameter

  // Query the database to get the user with the specified ID
  db.query('SELECT * FROM users WHERE id = ?', [userId], (err, result) => {
    if (err) {
      // If there’s a database error, send a 500 error response
      return res.status(500).send('Database error');
    }
    if (result.length === 0) {
      // If no user is found with the given ID, send a 404 response
      return res.status(404).send('User not found');
    }

    // Send the user data as a JSON response
    res.json(result[0]);
  });
});

// Endpoints
// Step 5: Endpoint to get all posts by a specific user
// This route returns all posts written by a user, using their user ID
app.get('/users/:id/posts', (req, res) => {
  const userId = req.params.id;  // Extract user ID from the URL

  // Query the database to get all posts where the user_id matches the specified user ID
  db.query('SELECT * FROM posts WHERE user_id = ?', [userId], (err, result) => {
    if (err) {
      // Handle any database errors by sending a 500 error response
      return res.status(500).send('Database error');
    }

    // Send the list of posts as a JSON response
    res.json(result);
  });
});

// Step 6: Endpoint to create a new post
// This route accepts data from the request body to create a new post
app.post('/posts', (req, res) => {
  const { user_id, title, content } = req.body;  // Extract post data from the request body

  // Query to insert a new post into the 'posts' table
  db.query(
    'INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)', 
    [user_id, title, content],  // The values to insert into the table
    (err, result) => {
      if (err) {
        // If there’s a database error, send a 500 error response
        return res.status(500).send('Database error');
      }

      // Send a response confirming the post creation with the new post's ID
      res.status(201).send(`Post created with ID: ${result.insertId}`);
    }
  );
});

// Step 7: Start the server and listen on port 3000
app.listen(3000, () => {
  // Log to console when the server starts successfully
  console.log('API running on http://localhost:3000');
});

What We Did Here:

1. /users/:id – Get User by ID

• This endpoint retrieves a specific user’s details from the database using their id.
• The SQL query SELECT * FROM users WHERE id = ? fetches the user info from the users table.

// Get user by ID
app.get('/users/:id', (req, res) => {
  const userId = req.params.id;  // Extract user ID from the URL parameter

  // Query the database to get the user with the specified ID
  db.query('SELECT * FROM users WHERE id = ?', [userId], (err, result) => {
    if (err) {
      // Handle any database errors by sending a 500 error response
      return res.status(500).send('Database error');
    }
    if (result.length === 0) {
      // If no user is found with the given ID, send a 404 response
      return res.status(404).send('User not found');
    }

    // Send the user data as a JSON response
    res.json(result[0]);
  });
});

• Database Query:
  • SQL: SELECT * FROM users WHERE id = ?
  • This fetches all details (*) of the user with the given id.
  • The ? is a placeholder for the actual id value that comes from req.params.id (the URL parameter).

2. /users/:id/posts – Get All Posts by User

• This endpoint retrieves all posts that belong to a specific user, using the user_id foreign key in the posts table.
• The SQL query SELECT * FROM posts WHERE user_id = ? fetches the posts associated with the user_id.

// Get posts by user
app.get('/users/:id/posts', (req, res) => {
  const userId = req.params.id;  // Extract user ID from the URL

  // Query the database to get all posts by the specified user
  db.query('SELECT * FROM posts WHERE user_id = ?', [userId], (err, result) => {
    if (err) {
      // Handle any database errors by sending a 500 error response
      return res.status(500).send('Database error');
    }

    // Send the list of posts as a JSON response
    res.json(result);
  });
});

Database Query:

• SQL: SELECT * FROM posts WHERE user_id = ?
• This retrieves all posts from the posts table where the user_id matches the id from the users table.
• The user_id in the posts table is the foreign key that links a post to a specific user.

3. POST /posts – Create a New Post

• This endpoint creates a new blog post. It requires the user_id, title, and content from the request body, and then inserts that data into the posts table in the database.
• The SQL query INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?) is used to insert the post into the database.

// Create a new post
app.post('/posts', (req, res) => {
  const { user_id, title, content } = req.body;  // Extract post data from the request body

  // Query to insert a new post into the 'posts' table
  db.query(
    'INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)', 
    [user_id, title, content],  // The values to insert into the table
    (err, result) => {
      if (err) {
        // Handle any database errors by sending a 500 error response
        return res.status(500).send('Database error');
      }

      // Send a response confirming the post creation with the new post's ID
      res.status(201).send(`Post created with ID: ${result.insertId}`);
    }
  );
});

Database Query:

• SQL: INSERT INTO posts (user_id, title, content) VALUES (?, ?, ?)
• This inserts the new post data into the posts table, associating it with the user through user_id.
• The ? placeholders are replaced by the actual values (user_id, title, content) from req.body.

Recap: API and Database Interaction

• /users/:id: Fetches user information by ID. Uses a SELECT query to retrieve data from the users table based on the user’s id.
• /users/:id/posts: Fetches all posts by a specific user. Uses the user_id foreign key to find posts linked to the user in the posts table.
• POST /posts: Creates a new post and links it to a user using user_id. Executes an INSERT query to store the new post in the posts table.

Express.js API endpoints directly interact with the MySQL database by querying and manipulating the data using SQL queries. The relationship between the API and the database is tightly integrated, allowing the API to perform CRUD operations seamlessly.

By following this structure, we map the database tables directly to API endpoints. It’s all about matching the way the database stores data with the way the API serves data.

Relationships: API Should Reflect the Database

In the database, we have a one-to-many relationship: one user can have many posts. The user_id in the posts table represents the foreign key that links a post to a specific user.

To reflect this relationship in the API, we need to join the posts and users tables to retrieve relevant data, such as the username of the user who created each post.

Example API with Relationships (Using JOIN)

Fetch Posts with User Info (Using JOIN)

// Get all posts for a user, including their username
app.get('/users/:id/posts', (req, res) => {
  const userId = req.params.id;
  db.query(
    'SELECT posts.*, users.username FROM posts JOIN users ON posts.user_id = users.id WHERE posts.user_id = ?',
    [userId],
    (err, result) => {
      if (err) return res.status(500).send('Database error');
      res.json(result); // Return posts along with the username from the users table
    }
  );
});

Explanation:

• Route: /users/:id/posts
• Database Interaction: We use an SQL JOIN to combine the posts and users tables. We select all columns from the posts table (posts.*), and additionally, we select the username column from the users table.
• Response: The API returns posts associated with a user along with the username of the user who created each post.

Data Types: Choose Wisely!

When designing an API, you must consider how data types in the database will be returned and formatted. For example, the created_at column in both users and posts tables might be stored as timestamps in the database. However, sending raw timestamps to the client might not be user-friendly.

We need to format this timestamp into a human-readable format before returning it.

Example API with Timestamp Formatting

Format the created_at Field (for Readability)

// Get all posts for a user, with formatted created_at timestamp
app.get('/users/:id/posts', (req, res) => {
  const userId = req.params.id;
  db.query('SELECT * FROM posts WHERE user_id = ?', [userId], (err, result) => {
    if (err) return res.status(500).send('Database error');
    
    // Format the created_at timestamp to a human-readable format
    result.forEach(post => {
      post.created_at = new Date(post.created_at).toLocaleString(); // Format timestamp
    });
    
    // Send the posts with formatted timestamps
    res.json(result);
  });
});

Explanation:

• Route: /users/:id/posts
• Database Interaction: We query the posts table to get all posts for the user.
• Response: Before returning the posts, we loop over the result and format the created_at timestamp using JavaScript’s Date().toLocaleString() method. This ensures the timestamp is human-readable (e.g., "April 23, 2025, 4:30 PM").

Wrapping It Up: A Perfect Sandwich

Just like making a good sandwich, you’ve got to get the balance right when designing a database and API. Here’s a quick recap:

1. Schemas = Blueprint: Your database schema directly influences your API endpoints.
2. Relationships = Connections: Ensure your API respects relationships between entities (e.g., user_id and posts).
3. Data Types = Match Them Up: API responses should match your database design, like formatting timestamps properly.

Building an API can be a challenge, especially when you have to make sure everything is synchronized — from the SQL database to the API design. You don't want your API to send data in an unexpected format, and you definitely don’t want mismatched names between your database tables and API endpoints. This is where EchoAPI comes into play.

How EchoAPI Helps Sync SQL and API Design: Consistency Is Key

EchoAPI is a powerful tool that can help bridge the gap between your database and API, making sure they talk the same language. It automates many aspects of the design process, ensuring that your database schema and API endpoints stay in sync.

EchoAPI helps keep your API and database design on the same page. By reading directly from your SQL schema, it can automatically shape your API endpoints to match the structure you've already defined. That means fewer inconsistencies, better alignment between backend and frontend, and a lot less time spent second-guessing field names or fixing mismatched data types.

With EchoAPI, your SQL and API grow together naturally—no extra effort required, just clean, predictable design from the start.

If you want to learn how to use EchoAPI to streamline your API design process and guarantee synchronization between your database and API, stay tuned. We'll dive deep into how to implement this tool and walk you through real-world examples!