Although I use Jetbrain’s Datagrip as my personal SQL IDE, I know it’s not everyone’s cup of tea and the main steam SQL IDEs like Navicat, SQLGate, and Datagrip are expensive, free SQL IDEs like DBeaver, HeidiSQL and OmniDB are good options but have different drawbacks, either being too bulky and slow, too buggy, or too lightweight. PostgreSQL’s official software Pgadmin probably has the most features, but it may be too much for this class, as I am expecting doing only simple query work.

PostgreSQL is a popular open-source relational database management system (RDBMS) that is widely used in web applications. Visual Studio Code (VS Code) is a powerful code editor that provides a wide range of functionalities, including support for various programming languages and extensions. In this article, I will guide you on how to use PostgreSQL in VS Code, including instructions on how to install the Remote SSH and PostgreSQL extensions, and how to use these 2 extensions.

Step 1: Install Remote SSH Extension

The first step in using PostgreSQL in VS Code is to install the Remote SSH extension. This extension allows you to connect to a remote server or a virtual machine (VM) using SSH. To install the Remote SSH extension, follow the steps below:

  1. Open VS Code.
  2. Click on the Extensions icon in the left-hand sidebar.
  3. Type Remote SSH in the search bar and press Enter.
  4. Click on the Install button next to the Remote SSH extension.

Once you have installed the Remote SSH extension, you can use it to connect to a remote server or a VM that has PostgreSQL installed.

Step 2: Connect to the Remote Server

To connect to the remote server, follow the steps below:

  1. Open the Command Palette by pressing Ctrl + Shift + P (Windows/Linux) or Cmd + Shift + P (Mac).
  2. Type Remote-SSH: Connect to Host in the search bar and press Enter.
  3. Click on + Add New SSH Host.
  4. Enter you MCECS account user name (i.e., [email protected]) and hit enter to save into your local SSH config file.

You now should have added the SSH host like the below image, hover onto the linux.cs.pdx.edu on the left side panel click on the left arrow to open in current windows.

After we have entered in the remote SSH host, it should prompt you to enter your password for your MCECS account like below image.

It should then prompt you to select the platform of your remote host, which you want to select Linux.

You have now successfully connected into our school server!

Step 3: Install PostgreSQL Extension

The next step is to install the PostgreSQL extension. This extension provides support for PostgreSQL, including syntax highlighting and database exploration. To install the PostgreSQL extension, follow the steps below:

  1. Click on the Extensions icon in the left-hand sidebar.
  2. Type PostgreSQL in the search bar and press Enter.
  3. Click on the Install button next to the PostgreSQL extension.

Make sure you’re installing the extension made by Chris Kolkman. Once you have installed the PostgreSQL extension, you can connect to a database and start using PostgreSQL in VS Code.

You may also want to install CodeSnap extension can used to export your code to an image file or copy the code snippet image into your clipboard. The Genie AI extension is a useful extension for coding using ChatGPT. The One Dark Pro extension offers a nice dark theme with rich syntax highlighting for various languages.

Step 4: Connect to the PostgreSQL Database

To connect to the school PostgreSQL database, follow the steps below:

  1. Open the Command Palette by pressing Ctrl + Shift + P (Windows/Linux) or Cmd + Shift + P (Mac).
  2. Type PostgreSQL: Add Connection in the search bar and press Enter. Or click on the + button next to POSTGRESQL EXPLORER: POSGRESQL: like below image.
  3. Enter the connection details for the PostgreSQL database by following the prompt, including
    1. the hostname (i.e., dbclass.cs.pdx.edu),
    2. root/username (i.e., s23bdbxxxx or s23adbxxxx),
    3. password,
    4. port, default port 5432 is fine for local database management,
    5. select Use Secure Connection,
    6. enter your database name, which should be same as your username,
    7. optional, give any name you want this DB to be called.
  4. Once you have entered the connection details, VS Code will connect to the database and display a list of databases in the left side panel.
  5. Restart VSCode for the auto completion to work.

Step 5: Use PostgreSQL in VS Code

Once you have connected to the PostgreSQL database, you can start using it in VS Code. You can create, edit, and execute SQL queries, as well as manage database objects such as tables, views, and indexes.

To execute a SQL query, follow the steps below:

  1. Right click on the database and select New Query.
  2. This should open up a new empty query on the right panel titled Untitled-1, you can now start typing your query works on the right side panel.
  3. Select your query code, right click and click on Run Query, or use its shortcut key.
  4. You can modify the shortcut key by pressing Ctrl + Shift + P (Windows/Linux) or Cmd + Shift + P (Mac) to open up the command Palette, search for Preference: Open Keyboard Shortcuts, change it to your desired keybinding for the Run Query command.

  5. By clicking on the save icon on the top left corner of your table panel, this extension give you the option to export your tables into json, xml, csv format.
  6. Once you’re done, hit Ctrl + S (Windows/Linux) Cmd + S (Mac) to save your query work into .pgsql or .sql file at any location of your device.

Note: Every time you perform CUD (Create, Update, and Delete.) operations on DB, the downside of this extension is it does not show your new table changes immediately. You have to right click on the DB and select Refresh Items.

You can also create a new SQL file in your local drive, follow the steps below:

  1. Click on the File menu in the top-left corner of the screen. Select New File or type Ctrl + N (Windows/Linux) Cmd + N (Mac).
  2. Select the language sql, start your SQL query works in the new file.
  3. Once you’re done, hit Ctrl + S (Windows/Linux) Cmd + S (Mac) to save your work into .pgsql or .sql file at any location of your device.