How to run PostgreSQL queries from GoLand

Last modified: 12 December 2022

note

Prerequisites

To complete this tutorial, ensure that you have installed the following software:

Step 1. Clone files from repositories

For example purposes, we are going to use the following two repositories: Sakila dump files (https://github.com/DataGrip/dumps) and Docker-compose files (https://github.com/DataGrip/datagrip-documentation). Sakila repository includes scripts that generate the structure of the Sakila database and scripts that add data to database objects. Docker repository includes YAML files that define services, networks, and volumes.

Enable version control integration

  1. Click VCS | Enable Version Control Integration on the menu bar.

  2. In the Enable Version Control Integration dialog, ensure that Git is selected in the drop-down list and click OK.

    Enable version control integration

Clone repositories

  1. Click Git | Clone.

  2. In the URL field, copy and paste the following web URL:

    • Sakila dump files: 

      https://github.com/DataGrip/dumps.git

    • Docker-compose files: 

      https://github.com/DataGrip/datagrip-documentation.git

  3. In the Directory field, click the folder icon (The folder icon) and specify a directory for the repository files.

    Clone repositories

Step 2. Run a Docker container

For this tutorial, we are going to run a PostgreSQL container for Docker (from the datagrip-documentation repository). In the Docker directory of the repository, you can find .env and docker-compose.yml files. .env includes credentials for the PostgreSQL service. docker-compose.yml defines how the service is created and configured.

The service name is the first line of the service definition (before the JDBC URL). In our example, the service name is postgresql_12, the JDBC URL is jdbc:postgresql://localhost:54333/guest?user=guest&password=guest.

postgresql_12:
            # jdbc:postgresql://localhost:54333/guest?user=guest&password=guest
            container_name: postgresql_12
            ports:
            - "54333:5432"
            image: postgres:12-alpine
            env_file: .env
            healthcheck:
            test: "exit 0"

You can run the container from a command line by running: 

docker-compose up -d postgresql_12
Note that you must navigate to the directory of the docker-compose.yml file first.

Alternatively, you can enable and use the Docker plugin in GoLand.

Getting started

Enable the Docker plugin

This functionality relies on the Docker plugin, which is bundled and enabled in GoLand by default. If the relevant features are not available, make sure that you did not disable the plugin.

  1. Press Ctrl+Alt+S to open the IDE settings and select Plugins.

  2. Open the Installed tab, find the Docker plugin, and select the checkbox next to the plugin name.

Install and run Docker

For information about installing and running Docker, see the Docker documentation.

tip

GoLand supports alternative Docker daemons: Colima and Rancher Desktop (with the dockerd engine).

    Connect to the Docker daemon

    1. Press Ctrl+Alt+S to open the IDE settings and select Build, Execution, Deployment | Docker.

    2. Click The Add button to add a Docker configuration and specify how to connect to the Docker daemon.

      The connection settings depend on your Docker version and operating system.

      The Connection successful message should appear at the bottom of the dialog.

      The Docker connection settings

      note

      The Path mappings table is used to map local folders to corresponding directories in the Docker virtual machine's file system. Only specified folders will be available for volume binding.

      This table is not available on Linux, because when running Docker on Linux, any folder is available for volume binding.

    3. Open the Services tool window (View | Tool Windows | Services or Alt+8), select the configured Docker connection node Docker node and click The Connect button, or select Connect from the context menu.

      The Docker tool window, connected to Docker

      To edit the Docker connection settings, select the Docker node and click The Edit Configuration button on the toolbar, or select Edit Configuration from the context menu.

      You can also click the Add Service menu and select Docker Connection to add a Docker connection directly from the Services tool window. If you have Docker contexts configured, you can select Docker Connections from Docker Contexts to add the corresponding connections.

    Create the Docker-compose deployment configuration

    1. In the Services tool window (View | Tool Windows | Services), right-click the Docker node and select Deploy.

    2. In the popup window, select Create Docker-compose Deployment.

    3. In the Create Deployment Configuration dialog, click the Browse icon of the Compose file(s) field.

    4. In the Docker Compose Configuration Files dialog, click the Add button and navigate to the compose file. The Compose YAML file defines services, networks, and volumes. In our case, it is docker-compose.yml

    5. In the Docker Compose Configuration Files dialog, click OK.

    6. In the Service(s) field, type postgresql_12.

    7. Click Run.

      As a result, you can see the postgresql_12 running container in the Services tool window.

      https://resources.jetbrains.com/help/img/idea/2022.3/db_create_the_docker_compose_deployment_configuration.png
      Gif

    tip

    To view all running images, use the docker ps command in the command line. Otherwise, use the Docker tool window (View | Tool Windows | Docker) to view and manage Docker containers.

    Step 3. Connect to a data source

    Depending on a database vendor (MySQL, PostgreSQL, Oracle), you need to create a corresponding data source connection. In this tutorial, we will create a PostgreSQL connection. If you want to connect to other database management systems (DBMS), refer to Database connection.

    tip

    If you see the Connecting to <JDBC URL> dialog when you click the Test connection button, specify guest as a password and click OK.

    Step 4. Attach a directory to the project

    To run dump files from the project in GoLand, attach a directory with dump files.

    Step 5. Run the dump files

    Create the database structure

    1. In the Project (View | Tool Windows | Project) tool window, navigate to the postgres-sakila-db tree node.

    2. Expand postgres-sakila-db tree node.

    3. Right-click the postgres-sakila-schema.sql and select Run postgres-sakila-schema .sql. Alternatively, click the postgres-sakila-schema.sql file and press Ctrl+Shift+F10.

    4. In the Edit Configuration dialog, click the Add button and select PostgreSQL.

    5. Press Run.

      https://resources.jetbrains.com/help/img/idea/2022.3/go_tutorial_run_dump_files.png
      Gif

    Load sample data to the database

    1. In the Project (View | Tool Windows | Project) tool window, navigate to the postgres-sakila-db tree node.

    2. Expand postgres-sakila-db tree node.

    3. Right-click the postgres-sakila-insert-data.sql and select Run postgres-sakila-insert-data.sql. Alternatively, click the postgres-sakila-insert-data.sql file and press Ctrl+Shift+F10.

    4. In the Edit Configuration dialog, click the Add button and select PostgreSQL.

    5. Press Run.

    Step 6. Fetch dependencies from the Go code

    tip

    Inject a correct SQL dialect to have a correct syntax highlighting and code completion in your query. For our example, it is PostgreSQL. To apply the PostgreSQL dialect, press Alt+Enter and select Inject language or reference. In the list of languages, select PostgreSQL (SQL).

    tip

    To run just the SQL code, click the SQL part in the code, press Alt+Enter, and select Run query in console.

    Step 7. Run the configuration