Learning PostgreSQL on the Fly: Building and Integrating Tables, Models, and Migrations Using Knex

6 min readFeb 7, 2024

By Kendall Trudick | github | linkedin

Introduction

I think it’s safe to say that a lot of ‘newer’ software engineers are daunted at the idea of contributing to open source projects. I understand that, it’s something that I faced as well. However, I came to the point where I realized that there lie so many advantages to doing so and that I needed to just throw myself into it. So, fast forward to now, I can confidently say it truly is not as bad as you may think! So, with all of that being said, I’ll dive a bit into my experience and also touch more on the technical side of things, especially in regards to working with databases.

The Project

The open-source project I took on is one run by ProgramEquity on their Amplify app. This app is created for users to take initiative in the efforts to protect against climate change. Being someone who felt more comfortable with front-end development, I made it my goal to take on more back-end heavy tasks. With that in mind, the first, and arguably more valuable, task/issue I decided to take on was building an admin table using PostgreSQL. This issue was part of the control panel feature of the app. Below are the details of the issue itself:

Now, the challenge this presented me with was that I have never worked with PostgreSQL before, and so I knew there would be a learning curve. But again, I went into this intending to push myself out of my comfort zone. However, I think having worked with other databases provided a solid foundation to be able to comprehend the underlying technology and be able to pick things up.

The Process:

Learning The Basics/Familiarizing Myself

The first thing I did was make sure I looked over and understood the codebase. This involved sifting through the code, especially the backend code, studying the other database tables and models already set up, and connecting the dots. You can learn a lot from preexisting code and this is definitely something that helped me tremendously.

From there, I made sure I understood all of the attributes needed for the admin table, the table I needed to build. These included things like first name, last name, email, password, active status, date created, and date last updated. Having an understanding of what types of attributes the table requires is also incredibly helpful as you dive into the documentation and formulate your solution and approach.

Knex Migration

In addition to Postgres, the Amplify app made use of Knex, which is a tool used for facilitating the database connection using postgres, the migrations, and seed data. Knex handles database migrations, the process of managing changes to a database schema over time. This could involve creating new tables, modifying existing ones, or even deleting tables. This is what I used to build my admin table.

For this project, knex was already set up and initialized. To create the table migration I ran this command:

$ npx knex migrate:make create-admin-table

Doing so created its own separate file in the migrations folder with its relevant timestamp. So, the file name was as follows: migrations/20231204193051_create-admins-table.js. After generating the migration, your file will look something like:

From there, I updated the content in the file to contain my relevant information such as:

Once you’ve filled out the migration file with the information and details you need specific to that table, you can then run:

$ npx knex migrate:latest

Building the Model

So once I created the migration for the admin table, I then needed to create the model. For the models, objection was being used for the other pre-existing models, so this is what I followed. Objection is an object-relational mapping (ORM) library for Node.js that is specifically designed to work with relational databases, like Postgres. It provides an object-oriented interface for interacting with databases, allowing developers to work with JavaScript objects rather than raw SQL queries.

With objection, if you are creating multiple tables and using knex to handle the connections with the tables, it’s best practice to create a Base Model which you can use to build all of your other models. Here is the preexisting base model in the app

Objection Base Model in Amplify App: _base.js

Once the base model is set up, you can easily create new models that are derived from this base model. Here is the model I set up for the admins table:

Building the Seed Data

Lastly, the seed data needed to be put together. Now, I went about this in maybe the ‘lazy’ way, since I just referenced the other existing seed files and modified it to match my admin table. However, this can be done easily with knex. To do so, you can run:

npx knex seed:make seed-admins-table

Running this command creates an admins.js file within a seeds directory, if the seeds directory already exists, which in my case it did. If it doesn’t exist, it will automatically create the directory for you and place the admins.js file within it. Once the file is created, you can go ahead and populate the file with the necessary information. Below is what the seed file looked like for the admins table:

One thing to notice here is how the password itself is being encrypted using the encrypt.js file in the lib directory. For the seed data, we are simply encrypting a password of “password”.

And thats essentially all there is to it! At first glance it all may seem a bit confusing, but I promise as you work through it and take it step by step, you come to find that it is very intuitive.

Final Thoughts

A Note on Collaboration

Open source thrives on collaboration, bringing together diverse skills for collective success. In the context of the Amplify app, collaborative efforts were pivotal in integrating the PostgreSQL admin table.

Collaborate actively by submitting pull requests and seeking feedback. Engaging with the community refines implementations, ensures code quality, and aligns changes with project standards. Embrace feedback to enhance the overall codebase.

Most importantly: it’s OK to ask for help! When dealing with unfamiliar technologies like PostgreSQL and Knex, don’t hesitate to ask for help. Utilize the community’s wealth of knowledge to navigate challenges efficiently. Seeking assistance is a proactive approach to problem-solving and accelerates the learning curve. There are always people to learn from.

In summary, open source success hinges on collaboration. Actively engage with the community, seek feedback, and embrace assistance when needed. This collective effort makes open source a rewarding environment for skill expansion.

Takeaways

Tackling the admin table integration with PostgreSQL in the Amplify app taught me that the best learning happens when you jump into something new. Turns out, dealing with tech challenges, like using Knex, Objection, and Postgres, is not as hard as it may at first seem.

Ultimately, this journey highlights that most things are simpler than we make them out to be. It’s a reminder of how important it is to keep learning and adapting, especially if that involves contributing to open-source projects.

For fellow developers thinking about joining open-source projects, take the leap! It’s a fantastic chance for personal and professional growth in the ever-changing world of tech, and I almost guarantee you wont regret the skills you learn, the insight you gain, and the confidence you attain. Not to mention the people you meet.