Getting Started with the PostgreSQL Extension for VSCode
Introduction
As a Flatiron School student quickly approaching the last few weeks of the program, I am realizing just how much I will miss this incredible, fast-paced learning environment. I love both the software engineering community and the spirit of continual growth and development that those within the community embrace. It is with immense gratitude that I offer my beginner’s perspective on the PostgreSQL Extension for VSCode. In this post, I will outline how to use this extension with a Rails application. It is my hope that this post will not only enhance your own backend development workflow, but also improve your understanding of how PostgreSQL works.
Transitioning from SQLite
During the previous Flatiron School phase (or module), we covered SQL and it’s connection to Ruby/ActiveRecord. I relied heavily on the SQLite VSCode Extension to help visualize the data returned from specific ActiveRecord/SQL queries. Using the extension was quite simple: open the Command Palette (Cmd + Shift + P) once the appropriate migrations had been made and data had been seeded, type “SQLite: Open Database”, choose the database from the db folder, and voila! SQLite Explorer becomes available in the bottom left corner of your screen. From the explorer, simply pick your table. Clicking on the table opens a new VSCode window containing the table data. The PostgreSQL Extension requires a few more steps to produce a nicely formatted table compared to the SQLite Extension. You may be wondering, if SQLite makes data visualization so simple, why use PostgreSQL at all? There are many reasons one might choose to use PostgreSQL over SQLite (see an in-depth comparison of the two here), but for me, the main incentive was deployment. At this stage in my Flatiron experience, I hope to deploy some of the apps I have created, and the recommended platform for deployment is heroku, which requires the use of PostgreSQL for database management.
Visualizing Your PostgreSQL Data in a Few Easy Steps
Before diving into the specifics of the PostgreSQL Extension, make sure to run the following command to configure your Rails app:
rails new backend_api --api --database=postgresqlThe --database=postgresql flag requires that you install PostgreSQL on your machine and create a PostgreSQL database for your app. You can download Postgres here. To create your Postgres database be sure to run rails db:create before running any migration. For alternative ways of installing Postgres and creating a Postgres database (outside of a Rails application context) definitely check out this article. Now, let’s dive into the PostgreSQL Extension itself.
- Install the PostgreSQL Extension for VSCode.
- Navigate to your Rails application created with the above command, and open the Command Palette (Cmd + Shift + P).
- Type ‘PostgreSQL: Add Connection’ and select the command from the dropdown menu
4. Enter the hostname of the database
Because we are logged into the same computer that the Postgres server is running on we use “localhost” or “127.0.0.1” as the hostname.
5. Enter the PostgreSQL user to authenticate as
If you are unsure of your PostgreSQL username run rails db(make sure you are in the directory containing your Rails application and that you have run rails db:create). This will bring you to your PostgreSQL database, as indicated by the following code:
psql (13.3)
Type "help" for help.react_rails_api_project_template_development=#
The last line should show the name of your database. From here, type \conninfo . This will display the user currently connected to the database as well as the port used to establish the connection.
You are connected to database "react_rails_api_project_template_development" as user "ryanhutzley" via socket in "/tmp" at port "5432".For now, we only need the name of the user. Type the name into the Command Palette field and press ‘Enter.’
6. Enter the password of the PostgreSQL user
You can leave this field blank, and press enter. Because we are logged in to the same machine that the Postgres server is running on, this step is not required.
7. Enter the port number to connect to
If we look at the code outputted from our \conninfo command in psql we see that the connection to our database occurs at port “5432” — the default port. Enter “5432” in the Command Palette field and press ‘Enter.’
8. Select ‘Standard Connection’ from the dropdown
Unless you have configured an SSL (or Secure Sockets Layer) connection, choose ‘Standard Connection’ from the dropdown. Configuring an SSL connection requires an understanding of server connections and client certificates (the topic of a future blog post!). For more info on SSL connections and how create them in Postgres, check out the official PostgreSQL docs.
9. Choose your database from the dropdown
10. Enter the display name of the database connection
The extension will automatically populate this field with the hostname of the database, but you are free to choose whatever display name works for you.
11. Ready to display!
After entering the display name, click on the PostgreSQL icon on the left side of your screen. You should see your database in the PostgreSQL Explorer (with your chosen display name). Click on the arrows to expand the contents of the database. The names of the different tables within your database should become visible.
12. Open Command Palette, type: ‘PostgreSQL: New Query’ and select the command from the dropdown menu
This will generate an untitled file for you to run SQL queries.
13. Type out your SQL query in the untitled file, then highlight the query text. Right click on the highlighted text and select ‘Run Query’
This will open a new window in VSCode with a table containing the desired data. Hurray!
If you get this error after running your query…
this could mean a few things; however, the most common reason I have found for this error is because the database itself isn’t selected — meaning it hasn’t been clicked/highlighted before running the query. To fix this, simply click on the display name of the database and run the query again. You should now see your table data.
Here is the table the PostgreSQL Extension generated for me after I ran the following SQL query: SELECT * FROM tracks
Conclusion
I hope you found my instructions useful, and that by using the PostgreSQL Extension you are able to improve your backend development workflow. I look forward to continuing with PostgreSQL and deploying my projects now that I have discovered how to effectively visualize my data. Happy coding!
