Connecting to PostgreSQL and Running Queries with IntelliJ IDEA Ultimate’s Database Tools

What you can do

IntelliJ IDEA Ultimate ships a built‑in Database tool window that turns any JDBC‑compatible database into a first‑class IDE resource. You can browse schemas, execute ad‑hoc SQL, inspect results in a sortable grid, export data, and even generate Java POJOs or DAO stubs from a result set—all without leaving the editor.

Worked example: PostgreSQL data source

1. Open the Database tool window

Choose View → Tool Windows → Database (or press Alt+1 on Windows/Linux, Cmd+1 on macOS). The pane appears on the right side of the IDE.

2. Add a data source

1. Click the + icon → Data Source → PostgreSQL.
2. In the General tab fill the connection fields:
   • Host: localhost (or your server address)
   • Port: 5432
   • Database: myapp
   • User: dev_user
   • Password: ••••••
3. The JDBC URL is assembled automatically, but you can verify it:

   jdbc:postgresql://localhost:5432/myapp

4. Press Test Connection. If the driver is missing, IntelliJ will prompt to download the PostgreSQL JDBC driver; accept the download.
5. Click OK. The new data source appears in the tree, expanding to show schemas, tables, views, and routines.

3. Run a query

1. Right‑click the data source (or a specific schema) and choose New → Console.
2. In the console editor type a simple SELECT:

   SELECT id, name, email FROM users WHERE active = true LIMIT 10;

3. Click the green Run button (or press Ctrl+Enter). A Result tab opens with a grid that supports column sorting, filtering, and reordering.

4. Export the result set

In the result toolbar click the Export Data icon (a table with an arrow). Choose CSV, pick a destination file, and confirm. The CSV will contain exactly the rows shown in the grid, respecting any applied filters.

5. Transaction control and code generation

• The console toolbar shows Auto‑commit (on by default). Toggle it off to run multiple DML statements in a single transaction.
• After an INSERT or UPDATE, use the Commit or Rollback buttons to finalize or discard changes.
• Right‑click any row in the result grid → Generate → POJOs (or DAO) to scaffold Java classes mapped to the selected columns.

Limits and common mistakes

Edition restriction

The full Database tool window, SQL console, and data‑source management are only available in IntelliJ IDEA Ultimate. The Community edition shows a read‑only view of existing data sources but cannot create new ones or run queries.

JDBC driver requirement

Every target database needs a compatible JDBC driver JAR. IntelliJ can download the official driver for popular engines (PostgreSQL, MySQL, Oracle, SQL Server, etc.). For proprietary or older versions you must add the JAR manually via the Driver tab of the data‑source dialog. A mismatched driver version often manifests as a “No suitable driver” error even when the URL is correct.

Large result sets

Results are streamed into the grid. Very large tables (hundreds of thousands of rows) can cause UI slowdown or truncation. Use Run in Background (the clock icon) for long‑running queries, or add a LIMIT clause. The grid also does not render BLOB/CLOB columns inline; export the column to a file or open it with the built‑in LOB viewer.

Connection lifecycle

Connections stay open while the data source node is expanded. If you keep many data sources open across sessions, you may hit the database’s max‑connection limit. Close unused data sources (right‑click → Close) or enable Auto‑disconnect after in the data‑source options.

Forgetting to commit

When auto‑commit is disabled, DML changes remain invisible to other sessions until you press Commit. A common oversight is to run an INSERT, see the row in the same console, then close the IDE—leaving the transaction rolled back.

Quick verification checklist

1. Open View → Tool Windows → Database and confirm the new PostgreSQL node appears.
2. Run SELECT 1; in a console; a single‑row result grid should display.
3. Export that grid to CSV and open the file—verify the single cell contains 1.
4. Toggle auto‑commit off, execute INSERT INTO users (name) VALUES ('test');, then click Commit. Query the table again to see the new row.
5. Right‑click the result row → Generate → POJOs and ensure a Java class file is created in the project.

When to use this workflow

Use the built‑in Database tools for day‑to‑day schema exploration, quick data fixes, and prototype code generation. For heavy ETL, large‑scale migrations, or production‑grade scripting, prefer dedicated CLI tools (psql, pg_dump, Flyway, Liquibase) that give you better logging, parallelism, and automation hooks.