Learning PostgreSQL on the Fly: Building and Integrating Tables, Models, and Migrations Using Knex
Tags: postgressql, programming, tutorial, beginners
Published: 2024-02-07 18:38:56


By Kendall Trudick | [GitHub](https://github.com/ktrudickm) | [LinkedIn](https://www.linkedin.com/in/kendalltrudick/)

## Introduction

I think it’s safe to say that a lot of ‘newer’ software engineers are daunted at the idea of jumping into and contributing to open source projects. And 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 jumping-in and also touch more on the technical side of things, especially in regards to working with PostgreSQL databases.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ti4o8si2ux087bormjde.png)


## The Project

The open-source project I took on is one run by ProgramEquity on their [Amplify](https://github.com/ProgramEquity/amplify) app. This app was created for users to take the initiative in being part of an actionable step 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:


### Details of the Issue

Now, the challenge this presented me with was due to the fact 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. That being said, I think having worked with other databases provided a solid enough 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. (Again, another tool I have no history working with). To break it down, Knex handles database migrations which is 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 exactly what I used to build my admin table.

For this project, knex was already set up and initialized so all I had to do to create the table migration was run:

```bash
$ 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:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dhkreksvipetp0jdnn74.png)



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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3oiuen3tk5xqgbnush0l.png)


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

```bash
$ 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 in order to handle the connections with all of the tables, it’s a good idea 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:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nf4v7hpzso6kt1msf3x7.png)



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 in admins.js file under the models directory:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/crobhzuiz6jvh6zdafq2.png)



### 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:

```bash
npx knex seed:make seed-admins-table
```

Running this command creates a seed-admins-table.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 seed-admins-table.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:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qbuym6jo37qwqqqn4dnm.png)


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 that's 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

In the world of open-source adventures, 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 won't regret the skills you learn, the insight you gain, and the confidence you attain. Not to mention the people you meet.

## References

- [Postgres Documentation](https://www.postgresql.org/docs/)
- [Knex Documentation](https://knexjs.org/)
- [Objection Documentation](https://vincit.github.io/objection.js/)
- [ProgramEquity, Amplify App](https://github.com/ProgramEquity/amplify)
- [Icon](https://thenounproject.com/icon/bird-1637609/)