Whether you’re looking to learn a new tool, practice your database and SQL skills, or integrate something different into your personal project, open-source tools such as PostgreSQL and TablePlus are a great place to start!
Last year, a teammate introduced me to PostgreSQL using TablePlus, and it’s been my favorite setup to use and manage my data for personal projects. I’m excited to share how to get started with it and some of its functionality.
As far as what the tools that we’ll be downloading are:
- PostgreSQL: A reliable open-source database management system
- TablePlus: A database GUI that allows you to easily manage and interact with multiple databases. There is a premium version, but the free version is still very robust and has all the capabilities and features that I’ve ever needed. (https://tableplus.com/)
To make the instructions more interchangeable for Mac and Windows users, I’ll be using the installer. If you are a Mac user, I personally prefer to install PostgreSQL using the Homebrew method – if you want those instructions, feel free to send me a message or follow the instructions on the PostgreSQL website! As a forewarning, some parts of this process can take a while.
Step 1: Install PostgreSQL
The following link will take you to the download page for PostgreSQL. From there, select your operating system and then, once you’re on the page, click on “Download the installer.” It will then take you to the page on the right where you can pick and download the version of PostgreSQL that you want. I used the 16.10 version, but it also works with the most recent version.
Follow the step-by-step installation instructions. I stuck with all default settings and, when the time came, entered a password (remember this password, as it will be used later!).
I also stuck with all the default settings that were recommended after the password stage.
The install screen looks like this.
PostgreSQL has now been installed. If you wanted to launch Stack Builder and go through that process, you are more than welcome to. It didn’t take me long, but it is not necessary for what we are working with.
Step 2: Install TablePlus
Follow this link to go to the download page of TablePlus. Since I’m using a Mac, I clicked “macOS” and moved it to my applications folder once it downloaded.
When TablePlus is launched, you’ll see a screen like this. To get started, either
- right click -> New -> Connection
- Click the “Create connection” button in the bottom left
And then select the PostgreSQL option.
I’m using the basics for this, but as far as what I inputted to connect and make the database, the following was inputted:
- Name: You can name this connection anything that you want
- User: carolinecordes (my Mac username, I’ve also had it just be “postgres” when doing it before)
- Password: Input the password that you created back when you downloaded Postgres
- Database: With every new installation, there comes a default database named postgres, which is what I’ll be using
After that has been inputted, hit “Test.” If it lights up green, that means it worked, and you can now hit “Connect” to set up the connection and get started.
Step 3: Make Tables and Connect to Python
There are a few ways to make a table, but in this case, I’ll simply upload a fake dataset that I downloaded on my computer. Right-click under tables, hit import, and then select “from CSV…”. Since I want to create a new table, I’ll select “Create New Table” and then import.
Now you have a table. Let’s quickly see how we can access this table in Python. For this, I used the psycopg2 package. If you’re following along with what’s been done so far, keep the database = “postgres” and the host = “localhost” since those are the defaults.
Now you’re connected!
Other features
Although there are many ways to use and interact with the software, a feature I wanted to highlight in TablePlus is the diagrams. If you want a way to quickly view the relationships between different tables that you have, you can select the tables that you have, select “generate diagram,” and then “generate diagram from selected items.”
Wrap-up
If you’ve made it this far, thank you so much for following along. If you have any questions or want to share the ways that you’re using the software, feel free to send me a message and let me know!
Helpful Links
- Categories:
