Guide on Connecting IntelliJ to Postgres

[Mythicaeda]

It is often useful to be able to view the contents of the database when debugging or writing new DB code. Having a connection to the database in IntelliJ is a convenient alternative to the Hasura console, especially for untracked tables or for DB Tests.

How to Connect

1. Expand the Database tab in the right upper-sidebar. From there, click the + button -> Data Source -> PostgreSQL.

2. Set up the connection information. By default, it will correctly set the Host and Port fields to localhost and 5432 respectively. You will need to provide the User and Password fields. While this can be any user in the database, I recommend setting them to your AERIE_USER and AERIE_PASSWORD variables. Set the Database field to aerie. Additionally, while you can proceed with the default Name, I recommend changing it to something meaningful for you. For instance, I have multiple configurations for when I have to connect as a different database user (ie, as the merlin service), so I prefer to include that in the name of the configuration.

3. Before hitting OK you should test your configuration with the Test Connection button on the bottom left. If you see the prompt Download missing driver files, do so before hitting Test Connection. Below is an example of what that prompt looks like. If you forget, the prompt will appear when you right click on the Connection in the Database tab.

4. Select the 1 of 3 from besides the connection's name, then expand the entry aerie (if aerie is not selected, select it now). If you know which schemas are relevant to you, select them, otherwise check Select All.

5. Go into Settings (CMD + ,). Under "Languages & Frameworks", navigate to "SQL Dialects". Set your Project SQL Dialect to PostgresSQL.

Useful Tips:

Refreshing the Database

If the database structure changes between connections (ie, you compose up a new version of Aerie that's at a higher migration), you will need to "introspect" the database for it to find the changes. Introspection can be done by selecting the aerie database and either pressing CMD+R or clicking the refresh button in the top bar of the Database tab. If you're ever not seeing tables or columns that you know should be there, you probably need to refresh.

Attaching a Session to a File

Once you have a made a connection to the database, whenever you have code blocks tagged as SQL statements (ie by using the @Language("SQL") annotation) or are working in a .sql file, you can attach a DB session to the file. This is extremely useful for creating and debugging SQL code.

This is done by right-clicking on the code block and pressing Attach Session. From there you can choose to reuse an existing session or make a new one.

The Query Console

The query console is a good tool when you want to run a quick SQL statement. To open the query console, select the icon of a window with QL in it, then press Open Default Console. The query console can also be found by searching console in the Search Everywhere window. Query consoles are excluded from git, so the contents will persist between branches.

Viewing and Changing a Table

It is possible to view and alter the contents of tables (add, update, delete). If you know what the table is called and the schema it is in, the quickest way to pull this up is to run select * from <SCHEMA>.<TABLE>. Otherwise, you can use the explorer in the Database tab to drill down to the table and double-click it.

Executing Part of a SQL File

When working with a SQL File with multiple statements, you can choose to only run part of it. If you put your cursor in the statement you want to run, then press the execute button (Green Play button in the top left or CMD+Enter) will prompt you to pick how much you want to execute: the specific statement or the entire file. If you highlight some SQL code and press the execute button, this prompt will be skipped and exactly what you have highlighted will be executed. This means if you have only part of a statement highlighted, only that part of the statement will be run.

Cleaning up after Canceling E2E Tests

If you accidentally cancel an e2e-test, you can generally clean up your database by executing the following two statements:

truncate merlin.mission_model cascade;
truncate merlin.plan cascade;

This will remove ALL mission models and plans from the database, as well as any data that was connected to them (ie activity directives, simulations). Note that for certain tests you may need to truncate additional tables.

Debugging DB Tests

If you need to review the contents of the database during a DB Test, you can do the following:

1. Place and reach a breakpoint during the test. This is critical, as our DB Tests make temporary databases rather than editing the aerie DB
2. Introspect the temporary database. Select your DB Configuration and refresh it. Then, select the 1 of X text to the right of the Configuration name and select the temporary database (Note, this text may instead state the number of connections instead of 1 of X). Additionally, expand it and select its schemas. You can now inspect this database the way you would the aerie database.