CONNECT TO A POSTGRESQL DATABASE, CREATE TABLES, INSERT DATA, AND USE A FILE FROM A GITHUB PROJECT WITH GO
Tags: go, postgressql, github
Published: 2022-03-03 15:57:14

---
title: CONNECT TO A POSTGRESQL DATABASE, CREATE TABLES, INSERT DATA, AND USE A FILE FROM A GITHUB PROJECT WITH GO
published: true
description: This blog post covers the topic connect to a [PostgreSQL](https://www.postgresql.org/) database, create tables, insert data and use a file from a GitHub project and do the implementation with [GO](https://go.dev/).
tags: #go, #postgressql, #github
cover_image: https://suedbroecker.files.wordpress.com/2022/02/programmer-custom-3607627_1280-1.jpg?w=1024&h=390&crop=1
---

This blog post covers the topic connect to a [PostgreSQL](https://www.postgresql.org/) database, create tables, insert data and use a file from a GitHub project and do the implementation with [GO](https://go.dev/). That blog post is also related to my last blog post called [Connect to a PostgreSQL database using GO](https://suedbroecker.net/2022/02/22/connect-to-a-postgressql-database-using-go/). You find the related code to my new blog post in that [GitHub project](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data).

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](https://github.com/IBM/multi-tenancy/blob/main/installapp/postgres-config/create-populate-tenant-a.sql).

### 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](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data).

1. [Create HTTP request using the GitHub API](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L37)
2. [Define HTTP header](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L44)
3. [Create client](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L47)
4. [Invoke HTTP request](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L50)
5. [Verify the request status](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L59)
6. [Get only body from response](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L62)
7. [Convert body to JSON content](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L69)
8. [Extract and decode file content from JSON](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L76)
9. [Connect to a database](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L80)
10. [Create a SQL statement from file content](http://create a sql statement from file content/)
11. [Verify the created tables with a query](https://github.com/thomassuedbroecker/go-access-postgres-create-table-insert-data/blob/main/gopostgressql/gopostgressql.go#L102)

### 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](https://github.com/IBM/multi-tenancy/blob/main/installapp/postgres-config/create-populate-tenant-a.sql). So, we need to get a bit familiar with the [GitHub public APIs](https://github.com/public-apis/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](https://github.com/public-apis/public-apis) documentation.

### SOME USEFUL RESOURCES:

- [How to convert an HTTP response body to a string in Go](https://freshman.tech/snippets/go/http-response-to-string/)
- [Go by Example: Base64 Encoding](https://gobyexample.com/base64-encoding)
- [How to get a file via GitHub APIs](https://stackoverflow.com/questions/9272535/how-to-get-a-file-via-github-apis)
- [Go by Example: JSON](https://gobyexample.com/json)
- [Access HTTP response as string in Go](https://stackoverflow.com/questions/38673673/access-http-response-as-string-in-go)
- [pgx – PostgreSQL Driver and Toolkit](https://github.com/jackc/pgx)

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

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

### STEP 2: VERIFY THAT THE [MOD FILE](https://go.dev/doc/modules/gomod-ref) “GO.MOD”

```bash
cd gopostgressql 
ls
```

### STEP 3: SET THE ENVIRONMENT VARIABLE

```bash
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

```bash
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

Source: [www.suedbroecker.net](https://suedbroecker.net/2022/02/22/connect-to-postgresql-database-create-tables-insert-data-and-use-a-file-from-a-github-project-with-go/)

#go, #postgressql, #github, #buildlabs4saas