How to run PostgreSQL queries from GoLand - Help

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/JetBrains/docker-env-compose). 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.

Click VCS | Git | Clone.
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/JetBrains/docker-env-compose.git
In the Directory field, click the folder icon () and specify a directory for the repository files.

Step 2. Run a Docker container

For this tutorial, we are going to run a PostgreSQL container for Docker (from the docker-env-compose repository). You can view the full list of available services in docker-compose.yml. The service name is the first line of the service definition (before the JDBC URL). In the following example, the service name is pg11X, the JDBC URL is jdbc:postgresql://localhost:54332/guest?user=guest&password=guest.

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

Open a command line and navigate to the folder with Docker YML files (for example, cd ~/database/docker-env-compose/).
Type the following command to run the container: docker-compose up -d --no-recreate pg11X.
Press Enter.

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 system (DBMS), refer to Connecting to a database.

In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon .
In the Data Sources and Drivers dialog, click the Add icon () and select PostgreSQL.
At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.
In the URL field, copy and paste the JDBC URL: jdbc:postgresql://localhost:54332/guest?user=guest&password=guest. JDBC URL for other DBMS, see in docker-compose.yml. You can open docker-compose.yml in a text editor.
(Optional) In the Name field, delete the current name and type a new name for the connection (for example, PostgreSQL).
To ensure that the connection to the data source is successful, click Test Connection. If you forgot to download JDBC drivers previously, you can click the Download Driver Files button in the notification dialog during the connection test.

Step 4. Attach a directory to the project

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

Click File | Open.
Navigate to the folder that you want to attach. In this tutorial, this folder is dumps that you cloned on Step 1 (Sakila dump files).
Click Open.
In the Open Project dialog, click Attach.

Step 5. Run the dump files

Create the database structure

In the Project (View | Tool Windows | Project) tool window, navigate to the postgres-sakila-db tree node.
Expand postgres-sakila-db tree node.
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.
In the Execution Target dialog, select PostgreSQL and click OK.

Load sample data to the database

In the Project (View | Tool Windows | Project) tool window, navigate to the postgres-sakila-db tree node.
Expand postgres-sakila-db tree node.
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.
In the Execution Target dialog, select PostgreSQL and click OK.

Step 6. Fetch dependencies from the Go code

To issue a query to PostgreSQL, we are going to use the sqlx library that provides a set of extensions for the Go standard database/sql library. For more information about the library, read about sqlx on GitHub.

Create a Go project in GoLand.
Right-click the root folder in the project and navigate to New | Go File.
In the New Go File dialog, type main in the Name field and click OK.
Copy the attached code snippet and paste it to the main.go file.
Place the caret on the "github.com/jmoiron/sqlx" import line, press Alt+Enter and click go get -t github.com/jmoiron/sqlx.

Code snippet

package main

                    import (
                    "fmt"
                    "github.com/jmoiron/sqlx"
                    _ "github.com/lib/pq"
                    "strings"
                    )

                    type User struct {
                    ID int
                    firstName string
                    }

                    const (
                    dsn = "postgres://guest:guest@localhost:54332/guest?sslmode=disable"
                    getUserQuery = "SELECT first_name FROM actor WHERE actor_id = 1")

                    func main() {
                    db, err := sqlx.Open("postgres", dsn)
                    if err != nil {
                    panic(err)
                    }

                    err = db.Ping()
                    if err != nil {
                    panic(err)
                    }


                    firstName := []string{}
                    err = db.Select(&firstName, getUserQuery)
                    if err != nil {
                    panic(err)
                    }

                    fmt.Printf("First name: %#v\n", strings.Join(firstName, ""))
                    }
                

Step 7. Run the configuration

To run the configuration, click the Run icon () in the gutter near the main function and select Run 'go build main.go'.