This blog post covers the topic connect to a PostgreSQL database, create tables, insert data and use a file from a GitHub project and do the implementation with GO. That blog post is also related to my last blog post called Connect to a PostgreSQL database using GO. You find the related code to my new blog post in that GitHub project.

The file to create the example tables and content is a part of the GitHub project called Multi-tenancy assets for IBM clients to build SaaS.

Objective

Connect to a PostgresSQL database, create tables, insert data using a file from a GitHub project.

Basic flow

1. Connect to the database
2. Get a file from a GitHub project with the SQL statements to create tables and insert data
3. Execute the SQL statement
4. Verify one value with a query

Basic programming steps

Each step contains the link to the line in the source code inside the GitHub repository.

1. Create HTTP request using the GitHub API
2. Define HTTP header
3. Create client
4. Invoke HTTP request
5. Verify the request status
6. Get only body from response
7. Convert body to JSON content
8. Extract and decode file content from JSON
9. Connect to a database
10. Create a SQL statement from file content
11. Verify the created tables with a query

Understand the GitHub url format

As I said before, we use an example file from a GitHub project called Multi-tenancy assets for IBM clients to build SaaS. So, we need to get a bit familiar with the GitHub public APIs.

• Example url we use: https://api.github.com/repos/IBM/multi-tenancy/contents/installapp/postgres-config/create-populate-tenant-a.sql

Mapping to the used GitHub API endpoint: https://api.github.com/repos/$NAME/$REPO/contents/$FILENAME

These are the related values of the example GitHub API endpoint above:

• GitHub API: https://api.github.com/repos/
• Name: “IBM/”
• Repo: “multi-tenancy”
• GitHub API: /contents/
• Filename: “installapp/postgres-config/create-populate-tenant-a.sql”

For more details visit the GitHub public APIs documentation.

Some useful resources:

• How to convert an HTTP response body to a string in Go
• Go by Example: Base64 Encoding
• How to get a file via GitHub APIs
• Go by Example: JSON
• Access HTTP response as string in Go
• pgx – PostgreSQL Driver and Toolkit

Run the example application

These are the steps you need to follow to run the example on your local machine.

Note: You need a running PostgresSQL database somewhere

Step 1: Git clone

git clone https://github.com/thomassuedbroecker/go-access-postgres-example.git
cd go-access-postgres-example

Step 2: Verify that the mod file “go.mod”

cd gopostgressql
ls

Step 3: Set the environment variable

export DATABASE_URL="postgres://username:password@localhost:5432/database_name"

Note: Don’t forget to insert your DATABASE_URL.

Step 5: Execute the go program

go run  .

Summary

We touched in that simple example different essentials and useful topics in GO programming I would say (for example: base64, access HTTP endpoints, handle JSON and so on) and that makes it worth to take a note 😉

I hope this was useful for you and let’s see what’s next?

Greetings,

Thomas

#go, #postgressql, #github, #buildlabs4saas