Expressjs and Knex sql query builder

Date: 2022-06-06

Building a Database-Driven Web Application with Express.js and Knex.js

This article details the process of creating a web application using Express.js, a popular Node.js framework, and Knex.js, a SQL query builder, to interact with a PostgreSQL database. We'll cover setting up the environment, configuring the database, and building basic CRUD (Create, Read, Update, Delete) operations.

Setting Up the Development Environment

Before we begin, you'll need to set up your development environment. This involves installing Node.js and npm (Node Package Manager), which are essential for running JavaScript applications on your system. Node.js is downloaded as an installer for your operating system and includes npm. After installation, you can verify the installation by opening a command prompt or terminal and typing node -v and npm -v. Successful installation will display the version numbers for both Node.js and npm.

Next, you’ll need a database. PostgreSQL is used in this example, and setting it up is simplified using Docker. Docker is a containerization technology that packages software and its dependencies into a standardized unit. You will need to install Docker on your system. Once installed, you can use Docker commands to download and run a PostgreSQL image. This involves pulling the PostgreSQL image from a Docker registry and then starting a container. The container will run a PostgreSQL server on a specific port, such as 5433, which you can then connect to using a tool like pgAdmin. During this setup, you'll also create a database and a user account with appropriate permissions for accessing that database.

Project Structure and Configuration

Now let's outline the project structure. We'll create a project folder, and within that folder, several subfolders to organize our application's components. The config folder will contain configuration files; routes will contain files that define the application's API endpoints; and the root directory will house the main application file.

A package.json file is created using the npm init -y command in the project directory. This file acts as a manifest containing project metadata, such as dependencies and scripts. This file is then modified to list the project dependencies, including Express.js and Knex.js. These dependencies are then installed using the npm install command, which downloads and installs the specified packages from npm's registry.

A crucial part of the setup involves two configuration files: default.json and knexfile.js. default.json stores the database connection details, such as the database name, username, password, and the host and port where the database is running. knexfile.js reads the configuration from default.json and configures Knex.js to connect to the database using those settings.

Database Interaction with Knex.js

Knex.js simplifies database interactions by providing an easy-to-use interface for building SQL queries. Instead of writing raw SQL, you use Knex.js functions to construct queries. This approach enhances code readability and maintainability, while also helping prevent SQL injection vulnerabilities. Consider the creation of a users table as an example. Instead of writing a raw SQL CREATE TABLE statement, you could use Knex.js to define the table schema in a more structured manner.

Knex.js allows you to perform all standard database operations – CRUD operations – in a structured manner. For creating data, Knex.js provides methods for inserting new rows into tables. The insert method allows you to specify the data to insert into a table. For retrieving data, Knex.js supports various methods for selecting data based on different criteria, such as filtering or ordering. The select method allows you to retrieve specific columns from a table, possibly applying various conditions using where, orderBy, and limit clauses. For updating data, Knex.js provides the update method, allowing you to modify existing rows based on specified conditions. Finally, for deleting data, Knex.js offers the delete method, enabling you to remove rows based on certain conditions.

Application Logic and Endpoints

Our application will expose several API endpoints that handle requests related to database operations. These endpoints would typically handle requests using HTTP methods such as GET, POST, PUT, and DELETE. Each endpoint in the routes.js file is associated with a specific URL path, like /users, /users/:id, and so forth.

The routes.js file will utilize Knex.js methods to interact with the database. When a request comes in to a particular endpoint, the appropriate Knex.js query is executed, and the results are returned as a response. Error handling should be incorporated to manage situations such as database connection failures or query errors. The responses will likely be formatted as JSON to allow easy consumption by clients.

The main application file, index.js, sets up the Express.js application, registers the routes defined in routes.js, and starts listening on a specific port, for instance, port 5005.

Running the Application

After setting up everything, you can start the application using the npm start command. This command executes the script defined in the package.json file (typically node index.js), starting the server and making the application's API endpoints accessible. You can then use tools like Postman to test the API endpoints by sending various requests (GET, POST, PUT, DELETE) and checking the responses.

Conclusion

This article has described how to create a basic web application with Express.js and Knex.js to manage database interactions. Knex.js streamlines database operations, making it easier to build robust and scalable web applications. The use of Docker simplifies database management, providing a consistent development environment. By combining these technologies, developers can create efficient and maintainable applications with minimal complexity in database interaction. Remember that error handling and security measures, such as input validation, are crucial for building a production-ready application.

Read more