DataGrip is a powerful tool for working with databases that supports PostgreSQL, MySQL, SQL Server, Oracle, MongoDB, and many other databases and DDL data sources.

  • It provides context-sensitive code completion, helping you to write SQL code faster.
  • Completion is aware of the tables structure, foreign keys, and even database objects created in code you’re editing.
  • DataGrip correctly resolves all references in your SQL code and helps you refactor them.
  • When you rename a variable or an alias, it will update their usages throughout the entire file.
  • The actual table names in the database are updated when you rename references to them from your queries.

DataGrip’s features and functionalities are excellent; they make our work easier and faster. You can manage several databases at the same time.

Install DataGrip

Jetbrains Toolbox is a tool provided by Jetbrains that allows users to manage their Jetbrains software installations easily. It provides a central location to download, install, and manage Jetbrains tools, including their IDEs.

I highly recommend you use Jetbrains Toolbox to install Jetbrains products, here are some reasons why:

  1. Easy Installation and Updates: Jetbrains Toolbox simplifies the installation and updates process by providing a streamlined user interface to download and install Jetbrains software. It also ensures that you have the latest version of the software and that updates are applied automatically.
  2. Multiple Version Support: Jetbrains Toolbox allows users to install multiple versions of Jetbrains software side by side, which can be useful when working with different projects or clients that require specific versions of the software.
  3. Customized Installations: Jetbrains Toolbox allows users to customize their installations by selecting the components they need and excluding the ones they don’t. This can save disk space and reduce clutter in the user interface.
  4. License Management: Jetbrains Toolbox makes it easy to manage your Jetbrains software licenses, allowing you to activate or deactivate licenses for different products and versions. This can be especially useful when working with a team or switching between projects that require different licenses.
  5. Centralized Management: Jetbrains Toolbox provides a centralized location to manage all your Jetbrains software installations, which can be especially useful for users who use multiple Jetbrains products. It also allows you to synchronize your settings and configurations across different installations, making it easier to switch between different projects.

Connect to PostgreSQL Database

Connecting to a PostgreSQL database in Jetbrains DataGrip is a straightforward process that can be completed in just a few steps. With its intuitive interface and powerful features, DataGrip makes managing your PostgreSQL databases a breeze.

Set you project path

Open DataGrip once you have installed DataGrip via Toolbox, it should prompt you to select a project path when you open it for the first time, this can be any folder of your device, and it will create a .idea folder within that project path. DO NOT delete this folder. The .idea folder is important because it stores all the project-specific settings such as compiler settings, run configurations, code style settings, version control settings, and more.

Create a New Database Connection

I would recommend you to try out their new UI theme that has similar style as VS Code. Once you have DataGrip open, you need to create a new database connection. To do this, click to the “Database Explorer” icon on the left-hand side bar and select the + button. Then, choose Data Source and select PostgreSQL.


Connect to a Remote Server

Next, it will ask you to configure your connection settings like below image.

First go to SSH/SSL tab. Check the box Use SSH tunnel and click on the ... button.

Enter your ssh info

  • host name (i.e., linux.cs.pdx.edu),
  • post (default 22 is fine),
  • username (i.e., MCECS account username),
  • password (i.e., MCECS account password),

and check the box Parse config file ~/.ssh/config to save your info into local .ssh config file such that you don’t have to enter it every time.

Then click on the button Test Connection, it should prompt to you that you have successfully connected to you remote server.

Click on OK button twice to exist SSH configuration window, it should then return to the connection windows with you MCECS account selected, if not click on the drop down icon to select your account.

Connect to a PostgreSQL Server

Return to your general tab, enter the details of your PostgreSQL server, including

  • Name (optional, give any name you want this database to be called),
  • Host (i.e., dbclass.cs.pdx.edu),
  • Port (default 5432 is fine),
  • Authentication: select User & Password,
  • User (i.e., s23adbxxxx or s23bdbxxxx)
  • Password
  • Database (which should be the same as your User)

Test your connection after you have fill out all empty field to make sure you have entered them correctly and is able to connect to the database, then click OK, you should now see your database showing in the Database Explorer.

If you wish to see all your schemas, right click on the database select Properties, go to Schemas tabs, and check All schemas box under Default Database (your MCECS usrname), and select OK, you should see all your schemas. If you don’t see it, right click on the database, and select Refresh to refresh your database.

Start Working with Your PostgreSQL Database

Now that you have successfully connected to your PostgreSQL database in DataGrip, you can start working with it. DataGrip offers a range of powerful features for working with databases, including the ability to run SQL queries, manage database schemas, and view data in tables and graphs.

Features of DataGrip

  1. SQL Editor: DataGrip comes with a powerful SQL editor that provides syntax highlighting, code completion, and other helpful features that make it easy to write and execute SQL queries.
  2. Schema Management: DataGrip makes it easy to manage database schemas by allowing you to view and edit database objects such as tables, views, and stored procedures.
  3. Data Visualization: DataGrip provides various ways to visualize your data, including tables, charts, and graphs. This can be especially helpful when trying to analyze large amounts of data.
  4. Query Optimization: DataGrip comes with built-in query optimization tools that can help you optimize your SQL queries for better performance.
  5. Integration with Other Tools: DataGrip integrates seamlessly with other Jetbrains tools such as IntelliJ IDEA and WebStorm, making it easy to work with databases in the context of your overall development workflow.

Command Palette can be accessed by pressing Command + Shift + A (Mac) or Ctrl + Shift + A (Windows/Linux) or Ctrl + Ctrl (Windows/Linux) to find anything you need, I also recommend installing One Dark Pro theme that you can find in Jetbrains Plugin Market.

  1. Tables can be viewed immediately with a double click that runs the SELECT * FROM table_name behind the scene.
  2. Table can be added locally by right click on the database and select Import/Export from/to local drive, wide range of exportation file format is support by DataGrip, including sql insert, sql update, json, csv, xlsx, tsv, xml, HTML, Markdown, and pretty.
  3. Table can be modified by double click on the table by select a row + or - to add/delete new row values, then click on the up arrow to submit changes to the database. Again all of these this is running behind the scene which you can see by clicking on the Service icon.
  4. Tables are updated immediately every time you perform CUD (Create, Update, Delete) operations on the database. Table columns can be ordered with a click of button.
  5. Table diagram can be easily accessed to see the relationship between other tables by right click on the schema and select Diagram > Show Diagram button.
  6. You can also see the table within a query windows or a sql file by click on the icon In-Editor Results next to Playground on the right side panel.

Conclusion

Connecting to a PostgreSQL database in Jetbrains’ DataGrip is a simple process, and once connected, you can take advantage of its many powerful features for managing and working with your data. With its SQL editor, schema management tools, data visualization capabilities, query optimization features, and integration with other tools, DataGrip is a great choice for developers and data analysts who need to work with PostgreSQL databases.