PostgreSQL To VSC Connection

Introduction

While the pgAdmin tool works when interacting with the remote database server, it can speed up development and testing if SQL can be tested within VSC. This document explains the setup process.

SQLTools Extension

In order to begin, you will need to install the SQLTools extension and the SQLTools PostgreSQL Driver.

1. Click the VSC extensions tool in the side bar.
2. Type "SQLTools" into the search bar at the top of the extensions panel.
3. The correct extension is by Matheus Teixeira. Click "Install".
4. If required, reload VSC to activate the extension.
5. A new icon will appear in the tools side bar:
   

The Connection String

Hopefully you will recall that when you were connecting pgAdmin to the remote database, you had to copy and then manipulate the connection string provided to you in the Render.com database connections information. You are going to need that connection string again.

1. Login to Render.com using your GitHub credentials.
2. Click the PostgreSQL service in the Dashboard.
3. Scroll down to the "Connections" area of the Info screen.
4. Click the "Copy to clipboard" icon for the "External Database URL".
5. Leave this browser window open as you'll need it again shortly.

Add New Connection

1. Return to VSC.
2. Click the SQLTools icon.
3. In the new panel, click the "Add New Connection" button.
4. A new settings tab will appear and should list connection drivers to different databases.
5. If the PostgreSQL driver icon is present, click it:
   
6. If the icon is NOT present, click the "Get more drivers" link.
   • The extension's panel will open.
   • Locate the "SQLTools PostgreSQL/Cockroach Driver" extension and click it.
   • Install this driver.
   • Once the driver is installed, click the PostgreSQL driver icon.
7. The second "connection assistant" setting window will load:
   
8. Give the connection a meaningful name (e.g. render.com).
9. Change the "Connect Using:" option to "Connection String".
10. Paste the copied External Database URL connection string into the "Connection String" text box.
11. Change the "SSL" setting to "Enabled".
12. In the SSL option list, turn on the checkbox to the right of the "rejectUnauthorized" option.
13. Scroll to the bottom of the page and click the "Test Connection" button (on the right side).
14. Hopefully, a green "Successfully connected" message will appear on the left, above the "Save Connection" button.
15. If the connection was successful, click the "Save Connection" button.
16. A confirmation screen will appear showing the data that you entered that is being used for the connection.
17. Click "Connect Now".
18. The command palette will open, and you will have to enter the password again, then press "Enter".
19. You are now connected to the database server and a new SQL tab should open where you can write SQL queries to interact with the database.

Using the Connection

1. Click the SQLTools extension icon.
2. The active connection should appear at the top of the panel.
3. Click the icons to expand the items until the three tables are visible.
4. Right-click either the "classification" or "inventory" table and select "Show Table Records".
5. A new tab should open showing you the field names and all records of data from the table.
6. Click the "+" sign next to one of the tables.
7. A new INSERT query will appear in the SQL tab. This is a generic query, where you will need to provide the actual data, but the field names and SQL structure are present.
8. Feel free to play around, but be careful to not change the database or table structure.
9. When done, hover over the render.com connection and click the "Disconnect" icon on the right.
10. Close the sql tabs that you had open.