MYSQL with express js

Introduction

MySQL and Express.js are two powerful technologies commonly used together to build web applications. MySQL is a popular relational database management system (RDBMS), while Express.js is a flexible web application framework for Node.js. When combined, they provide a comprehensive solution for storing, retrieving, and manipulating structured data in web applications.

MySQL serves as the backend database for an Express.js application, handling data storage and retrieval tasks. It excels in managing structured data in tables with defined relationships. Express.js, on the other hand, acts as the server-side framework, providing a robust and scalable platform for building web applications and APIs.

By integrating MySQL with Express js, developers can leverage the benefits of both technologies. MySQL provides a reliable and efficient way to store and manage data, while Express.js offers a flexible and intuitive framework for building server-side logic and handling HTTP requests.

What is MySQL?

MySQL is a popular open-source relational database management system (RDBMS) that is widely used for storing, managing, and retrieving structured data. It is a software application that allows users to create, modify, and interact with databases.

MySQL was first developed by Michael Widenius and David Axmark in the mid-1990s and has since become one of the most widely used RDBMSs in the world. It is now maintained and owned by Oracle Corporation.

As an RDBMS, MySQL follows the relational model for data storage, which means data is organized into tables consisting of rows and columns. It supports SQL (Structured Query Language) as the standard language for interacting with the database, enabling users to perform various operations such as creating tables, inserting, updating, and deleting data, as well as executing complex queries to retrieve and analyze data. By integrating MySQL with Express js, developers can leverage the benefits of both technologies.

MySQL offers a range of features and capabilities that make it a popular choice for developers and organizations:

• Flexibility: MySQL supports various data types, including numeric, string, date, time, and more, allowing for the storage of diverse types of information.
• Scalability: MySQL can handle high volumes of data and supports replication, sharding, and partitioning techniques to distribute workload and improve performance as data grows.
• Performance: MySQL is known for its efficient query execution, indexing mechanisms, caching strategies, and optimization techniques, which contribute to fast and responsive data retrieval.
• Security: MySQL provides robust security features, including user authentication and authorization mechanisms to control access privileges at different levels. It also supports encryption for securing data transmission and storage, helping to protect sensitive information.
• High Availability: MySQL supports replication, allowing for the creation of multiple copies of databases across different servers. This ensures data redundancy and enables failover in case of server failures, improving the overall availability of the system.

Setting up Express.js for our RestAPI

To set up Express.js for your REST API development, the below steps should be followed:

• Install Node.js and npm Download and install the npm and node.js packages.

• Create a New Project Directory: Create a new directory for the project and navigate to it using the command line or terminal.

• Initialise a Node.js Project: Initialise a new Node.js project by running the following command in the project directory:

The above command will create a package.json file that will keep track of your project's dependencies and configurations.

• Install Express.js: Now, Install Express.js as a dependency by running the following command:

• Create an Express.js Server File: Create the new JavaScript file (e.g., server.js) in your project directory. This file will serve as the entry point for the Express.js application.

Now inside server.js, require the Express module and create an instance for the Express application then add basic server configuration and routing as needed.

Below is an example to do so:

• Start the Express.js Server: In the command line or terminal, run the following command to start the Express.js server:

Now in the console of the IDE the message Server is running on port 3000 will be displayed which ensures that the server is running.

• Test the REST API: Open up the web browser or use tools like Postman to test the REST API then access the defined routes and verify that the expected responses are received.

RestAPI Project Structure

When we create a REST API project using MySQL with Express js, it's important to establish a well-organised project structure that promotes scalability, maintainability and code reusability. While there isn't a strict standard structure, here's a commonly used project structure for building REST APIs with Express.js:

Let's take a closer look at each directory and file within the project structure:

1. src/: This directory contains the main source code of the application.

2. controllers/: This directory holds the controller functions that handle the logic for each route or endpoint. Each controller file typically corresponds to a specific resource or entity in the API.

3. models/: This directory contains the database models or schemas that define the structure and behaviour of the data. Each model file typically represents a specific entity or collection in the database.

4. routes/: This directory houses the route definitions for the API endpoints. Each route file defines the API routes, associates them with the appropriate controller functions, and specifies the HTTP methods and URL paths.

5. middlewares/: This directory holds custom middleware functions that can be applied to specific routes or globally to handle request processing tasks such as authentication, validation, logging, error handling, etc.

6. services/: This directory contains additional services or modules that encapsulate reusable business logic, data manipulation, or integrations with external systems. Services can be used by multiple controllers or middleware.

7. utils/: This directory includes utility functions or modules that provide commonly used functionalities such as data validation, error handling, date formatting, encryption, etc.

8. app.js: This file serves as the entry point of your application. It initialises and configures the Express.js application, sets up middleware, connects to the database, and defines any global application-level settings.

9. package.json: This file keeps track of the project's dependencies, scripts, and other metadata. It is generated when running npm init and gets updated when we install new packages.

10. .env: This file is used for storing environment-specific configuration variables such as database connection details, API keys, or other sensitive information. It is not committed to version control and should be kept secure.

Let’s build a database which can be used for the get route, post route, put route and delete route. Below are the steps by which we will create the sample database:

This creates a table named users with auto-incrementing primary key column “id” and a name column of type VARCHAR to store user names.

GET Route

A GET route in Express.js is used to handle HTTP GET requests sent to a specific URL or endpoint. It is commonly used to retrieve or fetch data from the server.

Here's an example of how to define a GET route in Express.js:

In this example, when we access the /api/users endpoint in your browser or send a GET request to it, the server will fetch the users from the MySQL users table and send them as a JSON response.

POST Route

A POST route in Express.js is used to handle HTTP POST requests sent to a specific URL or endpoint. It is commonly used to submit or create new data on the server. Here's an example of how to define a POST route in Express.js:

In this example, we have added a POST route at /api/users. When we send a POST request to this endpoint with a JSON payload containing the name field, it will insert the new user with the provided name into the MySQL database.

PUT Route

A PUT route in Express.js is used to handle HTTP PUT requests sent to a specific URL or endpoint. It is commonly used to update existing data on the server. Here's an example of how to define a PUT route in Express.js:

In the example, we have added a PUT route at /api/users/:id. When we send a PUT request to this endpoint with a JSON payload containing the name field, it will update the user with the provided id in a MySQL database.

DELETE Route

A DELETE route in Express.js is used to handle HTTP DELETE requests sent to a specific URL or endpoint. It is commonly used to delete existing data on the server. Here's an example of how to define a DELETE route in Express.js:

In the example, we have added a DELETE route at /api/users/:id. When you send a DELETE request to this endpoint with the specific id parameter in a URL, it will delete the corresponding user from the MySQL database.

Testing our APIs

We can test all our API routes. To do so we will create a new route, we can do it with either Postman or any other HTTP client:

The code will generate the output as:

Similarly, we can perform other routing operations like delete, post, put.

Further Considerations

When testing the APIs, several additional considerations can help improve the effectiveness and reliability of your tests.

Here are a few considerations to keep in mind:

• Test Coverage: Aim for comprehensive test coverage by testing various scenarios, edge cases, and input validations. Consider different HTTP methods (GET, POST, PUT, DELETE), authentication and authorization scenarios, error handling, and boundary values for input fields.
• Environment Configuration: Use different environments for testing, such as development, staging, and production-like environments, to mimic real-world scenarios. This helps uncover environment-specific issues and ensures your APIs work correctly in different settings.
• Data Seeding: Use data seeding techniques to set up consistent test data in your testing environment. This ensures predictable results and avoids dependency on external data sources. Tools like Faker.js or factory functions can help generate realistic test data.
• Mocking External Dependencies: When testing API endpoints that rely on external services or dependencies, consider using mocking techniques to isolate and control the behaviour of those dependencies. This helps create repeatable and reliable tests without relying on the availability or state of external systems.
• Asynchronous Testing: If the API involves asynchronous operations, such as database queries or external API calls, make sure your tests handle those operations correctly. Use techniques like promises, async/await, or testing frameworks built-in mechanisms (e.g., Mocha's done() function or Jest's async/await support) to handle asynchronous code and ensure proper testing.