GoLand 2019.2 Help

How to run PostgreSQL queries from GoLand

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.

  1. Click VCS | 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/JetBrains/docker-env-compose.git

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

The Clone repository dialog

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"
  1. Open a command line and navigate to the folder with Docker YML files (for example, cd ~/database/docker-env-compose/).

  2. Type the following command to run the container: docker-compose up -d --no-recreate pg11X.

  3. Press Enter.

The Docker tool window

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.

  1. In the Database tool window (View | Tool Windows | Database), click the Data Source Properties icon The Data Source Properties icon.

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select PostgreSQL.

  3. 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.

  4. 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.

  5. (Optional) In the Name field, delete the current name and type a new name for the connection (for example, PostgreSQL).

  6. 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.

Integration with PostgreSQL

Step 4. Attach a directory to the project

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

  1. Click File | Open.

  2. 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).

  3. Click Open.

  4. In the Open Project dialog, click Attach.

Attach a directory to the project

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 Execution Target dialog, select PostgreSQL and click OK.

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 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.

  1. Create a Go project in GoLand.

  2. Right-click the root folder in the project and navigate to New | Go File.

  3. In the New Go File dialog, type main in the Name field and click OK.

  4. Copy the attached code snippet and paste it to the main.go file.

  5. 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, "")) }
Fetch dependencies from the Go code

Step 7. Run the configuration

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

Run the configuration
Last modified: 21 August 2019