CRUD application with express.js

When we hear about CRUD applications, we think of restful API's. On this blog I will write about engineering a CRUD application using Express.js and the postgres database and how we can manage to control different functions in a student system such as: creating a new student, getting information about the students, updating an existing student and deleting a student from the database. Ride with me as we dive into the technical parts of the story.

creating the express.js web server.

We must have our receiving point, a point where we can receive information via the forms from the front end or where we can access our database services from.

1.Importing Dependencies:

• const express = require('express');: This line imports the Express.js framework, allowing you to create and manage a web server.

• const studentRoutes = require('./src/students/routes');: This line imports a module (studentRoutes) containing route definitions for handling student-related operations.

• const app = express();: This line initializes an Express application.

2*.Middleware Configuration:*

• app.use(express.json());: This line configures Express to use the built-in JSON parsing middleware. It allows the server to automatically parse incoming JSON data in request bodies.

3.Port Configuration:

• const port = 3000;: This line sets the port number to 3000, on which the server will listen for incoming HTTP requests.

4.Root Route:

• app.get("/", (req, res) => { /* ... */ });: This code defines a route for the root URL ("/"). When a client sends an HTTP GET request to the root URL, the server responds with the string "My name is Nyakundi". This is a simple introductory message.

5.Mounting Routes:

• app.use('/api/v1/students', studentRoutes);: This line mounts the studentRoutes module under the path "/api/v1/students". It means that all routes defined in studentRoutes will be accessible under this path. For example, if there's a route defined in studentRoutes like "/create", it will be accessible as "/api/v1/students/create" on the server.

6.Server Start:

• app.listen(port, () => console.log(app listening on port ${port}));: This line starts the Express server and makes it listen on port 3000. When the server starts, it logs a message to the console indicating that it's listening on the specified port.

Setting up a connection pool to a postgreSQL database.

In this section we aim at setting up a connection pool to our postgreSQL database using the 'pg' library and export the pool object for use in other sections of the application. Let me break down each step of the process.

1.const Pool = require('pg').Pool;

• This line imports the Pool class from the pg library. The pg library is a popular Node.js library for interacting with PostgreSQL databases.

2.const pool = new Pool({ /* ... */ });

• Here, a new instance of the Pool class is created, and it's configured with an object containing connection parameters. The object specifies how to connect to the PostgreSQL database. The configuration includes:

  • user: The username for accessing the database (postgres in this case).

  • host: The hostname or IP address where the PostgreSQL database server is running (in this case, it's set to localhost).

  • database: The name of the PostgreSQL database to connect to (students in this case).

  • password: The password for the specified user (seku1308 in this case).

  • port: The port number on which the PostgreSQL database server is listening (usually 5432 for PostgreSQL).

3.module.exports = pool;

• This line exports the pool object so that it can be used in other parts of your application. By exporting it, you make the pool object accessible to other modules that require this module.

Overall, this code sets up a connection pool to a PostgreSQL database, which is a recommended approach for handling database connections in a Node.js application. Using a connection pool helps manage and reuse database connections efficiently, improving the performance and scalability of your application when dealing with database interactions. The exported pool object can be imported and used in other parts of your application to execute database queries and manage connections.

Creating our routes

These two lines are importing modules in a Node.js environment using the require function. Let me break down each line for you:

1. const pool = require('../../db');

   • require: In Node.js, the require function is used to import external modules or files.

   • '../../db': This is the path to the module or file you want to import. In this case, it is going up two levels in the directory structure (../../) and then looking for a module or file named db. The ../../ syntax means going up two directory levels from the current file.

   • const pool: The imported module or file is assigned to the variable pool. This variable can now be used to access the functionalities provided by the imported module.

Putting it all together, this line is importing a module or file located in the db directory that is two levels above the current file. The exported functionalities from that module are then accessible through the pool variable.

1. const queries = require('./queries');

   • require: Again, this is the Node.js function used for importing modules or files.

   • './queries': This is the path to the module or file you want to import. In this case, it is looking for a module or file named queries in the same directory as the current file ('./' represents the current directory).

   • const queries: The imported module or file is assigned to the variable queries. This variable can now be used to access the functionalities provided by the imported module.

Similar to the first line, this line is importing a module or file named queries from the current directory.

In summary, these lines are common in Node.js scripts or applications where modules or files are organized into different directories, and the require function is used to import them for use in the current script or module. The variables (pool and queries in this case) allow access to the functionalities provided by the imported modules.

the endpoints.

This code appears to be defining a function named getStudents that is intended to handle a request and send a response in a Node.js/Express application. Let's break down the code:

const getStudents = (req, res) => { // Using the 'pool' object to make a query to the database pool.query(queries.getStudents, (error, results) => { // Callback function handling the results of the query or an error if (error) throw error;

// If the query is successful, send a JSON response with the results res.status(200).json(results.rows); }); };

Explanation:

1. Function Declaration:

   • const getStudents = (req, res) => { ... }: This line declares a function named getStudents that takes two parameters, req (request) and res (response). This suggests that the function is intended to be used as a handler for HTTP requests, commonly in an Express.js application.

2. Database Query:

   • pool.query(queries.getStudents, (error, results) => { ... }): This line is using the pool object to make a query to the database. The actual query is obtained from queries.getStudents. The second argument is a callback function that handles the results of the query or any error that might occur during the execution of the query.

3. Error Handling:

   • if (error) throw error;: This line checks if there is an error in the query. If an error occurs, it is thrown, which would typically stop the execution of the script and log the error details. This is a basic form of error handling.

4. Response Handling:

   • res.status(200).json(results.rows);: If the query is successful (no error), this line sends a JSON response with a status code of 200 (OK) and the rows of results from the database. It assumes that the results object has a property named rows, which is common when using database libraries like pg in a Node.js environment.

In summary, this function is designed to handle an HTTP request, query a database using a connection pool (pool), and send a JSON response with the results. It's part of a server-side application, likely using Node.js with Express and a database library like pg.

get student by ID function

const getStudentById = (req, res) => { // Extracting the 'id' parameter from the URI and converting it to an integer const id = parseInt(req.params.id, 10);

// Checking if the extracted 'id' is a valid number if (isNaN(id)) { // If 'id' is not a valid number, return a 400 Bad Request response return res.status(400).json({ error: 'Invalid' }); }

// Querying the database to get a student by their ID pool.query(queries.getStudentById, [id], (error, results) => { // Handling errors that might occur during the database query if (error) { // If there's a database error, return a 500 Internal Server Error response return res.status(500).json({ error: 'Database error' }); }

// Checking if any results were returned from the database if (results.rows.length === 0) { // If no results are found, return a 404 Not Found response return res.status(404).json({ error: 'Student not found' }); }

// If everything is successful, return a 200 OK response with the student data res.status(200).json(results.rows[0]); }); };

Certainly! Let's break down the code for the getStudentById function:

javascriptCopy codeconst getStudentById = (req, res) => {
    // Extracting the 'id' parameter from the URI and converting it to an integer
    const id = parseInt(req.params.id, 10);

    // Checking if the extracted 'id' is a valid number
    if (isNaN(id)) {
        // If 'id' is not a valid number, return a 400 Bad Request response
        return res.status(400).json({ error: 'Invalid' });
    }

    // Querying the database to get a student by their ID
    pool.query(queries.getStudentById, [id], (error, results) => {
        // Handling errors that might occur during the database query
        if (error) {
            // If there's a database error, return a 500 Internal Server Error response
            return res.status(500).json({ error: 'Database error' });
        }

        // Checking if any results were returned from the database
        if (results.rows.length === 0) {
            // If no results are found, return a 404 Not Found response
            return res.status(404).json({ error: 'Student not found' });
        }

        // If everything is successful, return a 200 OK response with the student data
        res.status(200).json(results.rows[0]);
    });
};

Explanation:

1. Parameter Extraction:

   • const id = parseInt(req.params.id, 10);: This line extracts the id parameter from the URI (Uniform Resource Identifier) using req.params.id. It then uses parseInt to convert the extracted value to an integer with a base of 10.

2. Validation of 'id':

   • if (isNaN(id)) { return res.status(400).json({ error: 'Invalid' }); }: This block checks if the extracted id is a valid number. If not (isNaN), it returns a 400 Bad Request response with a JSON object containing an error message.

3. Database Query:

   • pool.query(queries.getStudentById, [id], (error, results) => { ... }): This line queries the database using the getStudentById query from the queries module. It provides the id as a parameter in the query. The callback function handles the results or any errors that may occur during the query.

4. Error Handling in Database Query:

   • if (error) { return res.status(500).json({ error: 'Database error' }); }: This block checks if there was an error during the database query. If an error occurs, it returns a 500 Internal Server Error response with a JSON object containing an error message.

5. Checking for Results:

   • if (results.rows.length === 0) { return res.status(404).json({ error: 'Student not found' }); }: This block checks if the database query returned any results. If no results are found (length is 0), it returns a 404 Not Found response with a JSON object containing an error message.

6. Sending Response:

   • res.status(200).json(results.rows[0]);: If the query is successful and results are found, it returns a 200 OK response with the data of the first (and presumably only) row from the query results.

In summary, this function is designed to handle an HTTP request for retrieving a student by their ID. It extracts and validates the ID from the URI, queries the database, and sends an appropriate response based on the success or failure of the query.

add student function, the post http method.

const addStudent = (req, res)=>{ const {name, email, age, dob} = req.body; //req.body is an object with key/ values items and we use destructuring to get those items from the req.body.

//logic to check if the email already exists. pool.query(queries.checkEmailExists, [email], (error, results)=>{ if(results.rows.length) { res.send("Email already Exists."); } // if there is an array that already exists with a row then... });

//logic for adding the student to the db if the email does not exist. pool.query(queries.addStudent, [name, email, age, dob], (error, results)=>{ if (error) throw error; res.status(201).send("Student created successfully!"); }); };

Explanation:

1. Destructuring req.body:

   • const { name, email, age, dob } = req.body;: This line uses destructuring to extract the values of name, email, age, and dob from the req.body object. The req.body object typically contains data sent in the request body, commonly in a POST or PUT request.

2. Checking Email Existence:

   • pool.query(queries.checkEmailExists, [email], (error, results) => { ... });: This line queries the database using the checkEmailExists query from the queries module, providing the email as a parameter. The callback function handles the results or any errors that may occur during the query.

3. Handling Email Existence:

   • if (results.rows.length) { res.send("Email already exists."); }: This block checks if there are any rows returned from the database query. If there are, it means that the email already exists, and the function sends a response indicating the conflict.

4. Adding Student to Database:

   • pool.query(queries.addStudent, [name, email, age, dob], (error, results) => { ... });: If the email does not exist (based on the previous check), this line queries the database using the addStudent query from the queries module, providing the student details as parameters. The callback function handles the results or any errors that may occur during the query.

5. Handling Database Error:

   • if (error) { throw error; }: This block checks if there was an error during the database query. If an error occurs, it is thrown, which would typically stop the execution of the script and log the error details.

6. Sending Success Response:

   • res.status(201).send("Student created successfully!");: If the student is added to the database successfully, this line sends a 201 Created response with a success message.

In summary, this function is designed to handle an HTTP request for adding a new student to the database. It first checks if the email already exists, and if not, it proceeds to add the student to the database. The function sends appropriate responses based on the success or failure of these operations.

remove student function

const removeStudent = (req, res) => { // Extracting the 'id' parameter from the URI and converting it to an integer const id = parseInt(req.params.id);

// Querying the database to check if the student exists pool.query(queries.removeStudent, [id], (error, results) => { // Checking if there are no rows returned (no student found) const noStudentFound = !results.rows.length; if (noStudentFound) { // If no student is found, send a response indicating it res.send("Student does not exist in the database"); } else { // If the student exists, proceed to remove the student from the database // Querying the database to remove the student pool.query(queries.removeStudent, [id], (error, results) => { // Handling errors that might occur during the database query if (error) { // If there is an error, throw it to stop execution and log the error throw error; }

// If the student is removed successfully, send a 200 OK response res.status(200).send("Student removed successfully"); }); } }); };

Certainly! Let's break down the code for the removeStudent function:

javascriptCopy codeconst removeStudent = (req, res) => {
    // Extracting the 'id' parameter from the URI and converting it to an integer
    const id = parseInt(req.params.id);

    // Querying the database to check if the student exists
    pool.query(queries.removeStudent, [id], (error, results) => {
        // Checking if there are no rows returned (no student found)
        const noStudentFound = !results.rows.length;
        if (noStudentFound) {
            // If no student is found, send a response indicating it
            res.send("Student does not exist in the database");
        } else {
            // If the student exists, proceed to remove the student from the database
            // Querying the database to remove the student
            pool.query(queries.removeStudent, [id], (error, results) => {
                // Handling errors that might occur during the database query
                if (error) {
                    // If there is an error, throw it to stop execution and log the error
                    throw error;
                }

                // If the student is removed successfully, send a 200 OK response
                res.status(200).send("Student removed successfully");
            });
        }
    });
};

Explanation:

1. Extracting 'id' Parameter:

   • const id = parseInt(req.params.id);: This line extracts the id parameter from the URI (Uniform Resource Identifier) using req.params.id. It then uses parseInt to convert the extracted value to an integer.

2. Checking Student Existence:

   • pool.query(queries.removeStudent, [id], (error, results) => { ... });: This line queries the database using the removeStudent query from the queries module, providing the id as a parameter. The callback function handles the results or any errors that may occur during the query.

3. Handling No Student Found:

   • const noStudentFound = !results.rows.length;: This line checks if there are no rows returned from the database query (no student found). If noStudentFound is true, it means that there is no student with the given id.

   • if (noStudentFound) { res.send("Student does not exist in the database"); }: This block sends a response indicating that the student does not exist in the database.

4. Removing Student from Database:

   • If the student exists (based on the previous check), the code proceeds to query the database again to remove the student.

   • pool.query(queries.removeStudent, [id], (error, results) => { ... });: This line queries the database using the removeStudent query with the id as a parameter to remove the student. The callback function handles the results or any errors that may occur during the query.

5. Handling Database Error:

   • if (error) { throw error; }: This block checks if there was an error during the database query. If an error occurs, it is thrown, which would typically stop the execution of the script and log the error details.

6. Sending Success Response:

   • res.status(200).send("Student removed successfully");: If the student is removed from the database successfully, this line sends a 200 OK response with a success message.

In summary, this function is designed to handle an HTTP request for removing a student from the database. It first checks if the student exists by querying the database, and if the student is found, it proceeds to remove the student and sends an appropriate response based on the success or failure of these operations.

update student function

const updateStudent = (req, res) => { // Extracting the 'id' parameter from the URI and converting it to an integer const id = parseInt(req.params.id, 10);

// Destructuring to extract 'name' from req.body const { name } = req.body;

// Checking if 'id' is a valid number if (isNaN(id)) { // If 'id' is not a valid number, return a 400 Bad Request response return res.status(400).json({ error: 'Invalid ID' }); }

// Querying the database to check if the student exists pool.query(queries.getStudentById, [id], (error, results) => { // Checking if there are no rows returned (no student found) const noStudentFound = !results.rows.length; if (noStudentFound) { // If no student is found, send a response indicating it res.send("Student does not exist in the database"); } else { // If the student exists, proceed to update the student in the database // Querying the database to update the student pool.query(queries.updateStudent, [name, id], (error, results) => { // Handling errors that might occur during the database query if (error) { // If there is an error, throw it to stop execution and log the error throw error; }

// If the student is updated successfully, send a 200 OK response res.status(200).send("Student updated successfully"); }); } }); };

Explanation:

1. Extracting 'id' Parameter:

   • const id = parseInt(req.params.id, 10);: This line extracts the id parameter from the URI (Uniform Resource Identifier) using req.params.id. It then uses parseInt to convert the extracted value to an integer with a base of 10.

2. Destructuring req.body:

   • const { name } = req.body;: This line uses destructuring to extract the value of name from the req.body object. The req.body object typically contains data sent in the request body, commonly in a POST or PUT request.

3. Checking 'id' Validity:

   • if (isNaN(id)) { return res.status(400).json({ error: 'Invalid ID' }); }: This block checks if the extracted id is a valid number. If not (isNaN), it returns a 400 Bad Request response with a JSON object containing an error message.

4. Checking Student Existence:

   • pool.query(queries.getStudentById, [id], (error, results) => { ... });: This line queries the database using the getStudentById query from the queries module, providing the id as a parameter. The callback function handles the results or any errors that may occur during the query.

5. Handling No Student Found:

   • const noStudentFound = !results.rows.length;: This line checks if there are no rows returned from the database query (no student found). If noStudentFound is true, it means that there is no student with the given id.

   • if (noStudentFound) { res.send("Student does not exist in the database"); }: This block sends a response indicating that the student does not exist in the database.

6. Updating Student in Database:

   • If the student exists (based on the previous check), the code proceeds to query the database again to update the student.

   • pool.query(queries.updateStudent, [name, id], (error, results) => { ... });: This line queries the database using the updateStudent query with the name and id as parameters to update the student. The callback function handles the results or any errors that may occur during the query.

7. Handling Database Error:

   • if (error) { throw error; }: This block checks if there was an error during the database query. If an error occurs, it is thrown, which would typically stop the execution of the script and log the error details.

8. Sending Success Response:

   • res.status(200).send("Student updated successfully");: If the student is updated in the database successfully, this line sends a 200 OK response with a success message.

In summary, this function is designed to handle an HTTP request for updating a student in the database. It first checks if the id is valid, then checks if the student exists, and if so, it proceeds to update the student. The function sends appropriate responses based on the success or failure of these operations.