Crafting Database Models with Knex.js and PostgreSQL
Discover the magic of building robust models with ease, as we journey through the world of Knex.js, making database modeling and testing fun and foolproof.
In today’s dynamic world of web development, the foundation upon which we build our applications is crucial. At the heart of many modern web applications lies the unsung hero: the database. But how we interact with this foundation — how we query, shape, and manipulate our data — can mean the difference between an efficient, scalable app and one that buckles under pressure.
Enter the formidable trio of Node.js, Knex.js, and PostgreSQL. Node.js, with its event-driven architecture, promises speed and efficiency. Knex.js, a shining gem in the Node ecosystem, simplifies database interactions, making them more intuitive and less error-prone. And then there’s PostgreSQL — a relational database that’s stood the test of time, renowned for its robustness and versatility.
So, why this particular blend of technologies? And how can they be harnessed to craft resilient and reliable database models? Journey with us as we unpack the synergy of Node.js, Knex.js, and PostgreSQL, exploring the myriad ways they can be leveraged to elevate your web development endeavors.
Initial Setup
In a previous article, I delved into the foundational setup and initiation of services using Knex.js and Postgres. However, this article hones in on the intricacies of the model aspect in service development. I won’t be delving into Node.js setups or explaining the intricacies of Knex migrations and seeds in this piece, as all that information is covered in the previous article.
Postgres Connection
Anyway let’s briefly create a database using docker-compose
and in your .env file values for connection:
DB_HOST="localhost"
DB_PORT=5432
DB_NAME="modeldb"
DB_USER="testuser"
DB_PASSWORD="DBPassword"Those environment variables will be used in docker-compose file for launching your Postgres Database. When all values are ready we can start to run it with docker-compose up
Kenx Setup
Before diving into Knex.js setup, we’ll be using Node.js version 18. To begin crafting models, we only need the following dependencies:
"dependencies": {
"dotenv": "^16.3.1",
"express": "^4.18.2",
"knex": "^2.5.1",
"pg": "^8.11.3"
}Create knexfile.ts and add the following content:
Next, in the root directory of your project, create a new folder named database. Within this folder, add a index.ts file. This file will serve as our main database connection handler, utilizing the configurations from knexfile. Here's what the content index.ts should look like:
This setup enables a dynamic database connection based on the current Node environment, ensuring that the right configuration is used whether you’re in a development, test, or production setting.
Within your project directory, navigate to src/@types/index.ts. Here, we'll define a few essential types to represent our data structures. This will help ensure consistent data handling throughout our application. The following code outlines an enumeration of user roles and type definitions for both a user and a post:
These types act as a blueprint, enabling you to define the structure and relationships of your data, making your database interactions more predictable and less prone to errors.
After those setups, you can do migrations and seeds. Run npx knex migrate:make create_users_table
and npx knex migrate:make create_posts_table
After setting things up, proceed by running npx knex migrate:latest to apply the latest migrations. Once this step is complete, you're all set to inspect the database table using your favorite GUI tool:
We are ready for seeding our tables. Run npx knex seed:make 01-users with the following content:
And for posts run: npx knex seed:make 02-posts with the content:
The naming convention we’ve adopted for our seed files, 01-users and 02-posts, is intentional. This sequential naming ensures the proper order of seeding operations. Specifically, it prevents posts from being seeded before users, which is essential to maintain relational integrity in the database.
Models and Tests
As the foundation of our database is now firmly established with migrations and seeds, it’s time to shift our focus to another critical component of database-driven applications: Models. Models act as the backbone of our application, representing the data structures and relationships within our database. They provide an abstraction layer, allowing us to interact with our data in an object-oriented manner. In this section, we’ll delve into the creation and intricacies of Models, ensuring a seamless bridge between our application logic and stored data.
In the src/models/Model/index.ts directory, we'll establish the foundational setup:
To illustrate how to leverage our Model class, let's consider the following example using TestModel
This subclass, TestModel, extends our base Model and specifies the database table it corresponds to as 'test_table'.
To truly harness the potential of our Model class, we need to equip it with methods that can seamlessly interact with our database. These methods would encapsulate common database operations, making our interactions not only more intuitive but also more efficient. Let's delve into and enhance our Model class with some essential methods:
In the class, we’ve added methods to handle the insertion of data (insert), fetch a single entry based on its ID (findOneById), and retrieve all items (findAll). These foundational methods will streamline our database interactions, paving the way for more complex operations as we expand our application.
How should we verify its functionality? By crafting an integration test for our Model. Let's dive into it.
Yes, I'm going to use Jest for integration tests as I have the same tool and for unit tests. Of course, Jest is primarily known as a unit testing framework, but it’s versatile enough to be used for integration tests as well.
Ensure that your Jest configuration aligns with the following:
Within the Model directory, create a file named Model.integration.spec.ts.
In the test, it showcased an ability to seamlessly interact with a database. I've designed a specialized TestModel class that inherits from our foundational, utilizing test_table as its designated test table. Throughout the tests, I'm emphasizing the model's core functions: inserting data and subsequently retrieving it, be it in its entirety or via specific IDs. To maintain a pristine testing environment, I've incorporated mechanisms to set up the table prior to testing, cleanse it post each test, and ultimately dismantle it once all tests are concluded.
Here leveraged the Template Method design pattern. This pattern is characterized by having a base class (often abstract) with defined methods like a template, which can then be overridden or extended by derived classes.
Following the pattern you’ve established with the Model class, we can create a UserModel class to extend and specialize for user-specific behavior.
In our Model change private to protected for reusability in sub classes.
protected static tableName?: string;And then create UserModel in src/models/UserModel/index.ts like we did for the baseModel with the following content:
To conduct rigorous testing, we need a dedicated test database where table migrations and deletions can occur. Recall our configuration in the knexfile, where we utilized the same database name across environments with this line:
export default Object.fromEntries(environments.map((env: string) => [env, commonConfig]));To have both develop and test databases, we must adjust the docker-compose configuration for database creation and ensure the correct connection settings. The necessary connection adjustments should also be made in the knexfile.
// ... configs of knexfile.ts
export default {
development: {
...commonConfig,
},
test: {
...commonConfig,
connection: {
...connection,
database: process.env.DB_NAME_TEST as string,
}
}
}With the connection established, setting process.env.NODE_ENV to "test" ensures that we connect to the appropriate database. Next, let's craft a test for the UserModel.
Initially, this mock user is inserted into the database, after which a retrieval operation ensures that the user was successfully stored, as verified by matching their first name. In another segment of the test, once the mock user finds its way into the database, we perform a retrieval using the user’s ID, further confirming the integrity of our insertion mechanism. Throughout the testing process, it’s crucial to maintain an isolated environment. To this end, before diving into the tests, the database is migrated to the most recent structure. Post each test, the user entries are cleared to avoid any data residue. Finally, as the tests wrap up, a migration rollback cleans the slate, and the database connection gracefully closes.
Using this approach, we can efficiently extend each of our models to handle precise database interactions.
The PostModel specifically targets the 'posts' table in the database, as indicated by the static tableName property. Moreover, the class introduces a unique method, findAllByUserId, designed to fetch all posts associated with a specific user. This method checks the user_id attribute, ensuring posts are only fetched when a valid user ID is provided.
If necessary to have a generic method for updating, we can add an additional method in the base Model:
public static async updateOneById<Payload>(
id: number,
data: Payload
): Promise<{
id: number;
} | null> {
const [result] = await this.table.where({ id }).update(data).returning('id');
return result;
}So, this method updateOneById can be useful for all models.
Conclusion
In wrapping up, it’s evident that a modular approach not only simplifies our development process but also enhances the maintainability and scalability of our applications. By compartmentalizing logic into distinct models, we set a clear path for future growth, ensuring that each module can be refined or expanded upon without causing disruptions elsewhere.
These models aren’t just theoretical constructs — they’re practical tools, effortlessly pluggable into controllers, ensuring streamlined and reusable code structures. So, as we journey through, let’s savor the transformative power of modularity, and see firsthand its pivotal role in shaping forward-thinking applications.
I welcome your feedback and am eager to engage in discussions on any aspect.
