Get All Records Endpoint

The Anatomy of a GET Request

Before we write any code, let's visualize the journey of a request to fetch all users:

1. Client Request: An API client (like a browser or Postman) sends a GET request to our server, for example, at http://localhost:5001/api/users.
2. Express Server: Our Express server receives the request.
3. Routing: Express matches the path /api/users to a specific handler function we'll define.
4. Database Query: The handler function connects to the PostgreSQL database and executes a SQL query, something like SELECT * FROM users;.
5. Database Response: PostgreSQL returns the requested data (a list of all users) to our application.
6. Server Response: Our handler function formats this data into a JSON array and sends it back to the client with a 200 OK status code.

We are going to build this entire flow from scratch.

Setting Up the API Project

Every house needs a foundation. For our API, this means setting up the project structure, installing the necessary tools, and creating a basic server. The following video provides a comprehensive walkthrough of this entire process, establishing a professional and scalable project structure that you'll use in real-world applications.

After following the video, you should have a basic Express server running and a clean folder structure ready for our API logic.

Connecting to the Database and Creating Our Table

With the server running, the next step is to connect it to our PostgreSQL database. We also need to create the actual users table based on the schema we designed previously. The video continues by showing how to do both of these things programmatically.

By now, your application can connect to the database, and it ensures the users table exists. We're finally ready to retrieve some data.

Implementing the GET Endpoint

We will implement our endpoint using a common pattern that separates data logic from request/response handling.

1. Service (or Model): A function whose only job is to talk to the database.
2. Controller: A function that handles the incoming request, calls the service to get data, and sends the final response.
3. Route: The Express definition that links a URL path to a controller.

This separation makes your code cleaner, easier to test, and more maintainable.

1. The Service: Querying the Database

First, let's create the function that fetches all users from the database. This function will live in the models folder (which the video author uses for data-related services).

The core of this step is pool.query(). It takes a SQL string, sends it to PostgreSQL, and returns a result object. The rows property of this object is an array of JavaScript objects, where each object represents a row from your table. Using async/await makes the asynchronous nature of database calls clean and easy to read.

2. The Controller and Route: Handling the Request

Now we need a controller to call our new service and a route to trigger the controller.

You now have a complete endpoint! The request comes into /api/user, the router passes it to the getAllUsers controller, the controller calls the getAllUsersService, which queries the database, and the results flow all the way back to the client.

Testing Your First Endpoint

The final step is to verify that our endpoint works. You can't just open this URL in a browser if you have no data yet. The best way to test an API is with a dedicated API client. Popular choices include Postman or a VS Code extension like Thunder Client, which is used in the video.

The video shows testing both a POST request (to add data) and then the GET request.

When you test your GET endpoint, you should see a JSON response containing an array of all the user records in your database, just like in this image:

import pool from '../config/db.js'; // Assuming db config is in this path

export const getAllPostsService = async () => {
  const result = await pool.query('SELECT * FROM posts ORDER BY created_at DESC');
  return result.rows;
};

Conclusion

Congratulations! You have successfully built and tested your first API endpoint. This is a massive step. You've connected a server to a database and exposed data to the outside world in a structured way.

Key Takeaways:

• Connection Pooling: Use the pg library's Pool to efficiently manage database connections.
• Separation of Concerns: Splitting logic into Routes, Controllers, and Services/Models makes your code organized and scalable.
• The Core Query: The pool.query('SELECT * FROM table_name') command is the key to fetching all records.
• Async/Await: This syntax is essential for handling asynchronous database operations cleanly.
• JSON Response: APIs communicate using JSON. Send data back using res.status(200).json(data).
• API Clients: Use tools like Postman or Thunder Client to test your endpoints thoroughly.

Preview of the Next Lesson:
Fetching all records is useful, but often we need to retrieve just one specific item. In the next lesson, we will build on today's code to implement a GET endpoint that retrieves a single record by its ID (e.g., /api/users/1). This will introduce you to route parameters and the SQL WHERE clause.