Hi again,
I recently wrote a small article on making a really easy connection to mongoDB with Go. This article intends to cover the same ground but for postgres because not everyone wants to go with a document model engine.
Let's map it out
We want to achieve a couple of things here:
- separate the DB code into a separate package.
- make clear that this is demo quality and I'll be taking the easy route with usernames and passwords and so on. ie. Do not repeat these shortcuts in production code.
- clearly mark the bits that are whiffy for prod code.
Let's get a Dockerised postgres instance
We want to grab a docker image, my particular one was 10.8 because it matched a requirement I had. You can take a later version without any hassles.
docker pull postgres
# I used
docker pull postgres:10.8
To run this postgres image in a container you can use the following.
docker run --name postgres-local -p 5432:5432 -v ~/.postgres-data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=STRONGPASSWORD -d postgres:10.8
This connects you to the default user. Therefore to setup your database you can psql
into the postgres shell and create a database CREATE DATABASE databasename
and a role/user.
If you don't like the CLI for this sprt of thing you can use postico, or pgadmin apps to have a graphical tool for the ops here.
Lets go
Ideally you want your project to resemble the following structure:
.
├── go.mod
├── go.sum
├── models
│ └── models.go
└── main.go
We'l do most of the database work in the models.go
file and it will look like this. First you'll have to go get the driver package called pq
. This can be achieved with go get github.com/lib/pq
. Easy!
package models
import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)
const (
host = "127.0.0.1"
port = 5432
user = "your_username_here"
password = "make_it_a_good_on"
dbname = "your_database_name_here"
)
func InitDB() (*sql.DB, error) {
var connectionString = fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
host, port, user, password, dbname)
var err error
db, err := sql.Open("postgres", connectionString)
if err != nil {
return nil, err
}
// example of an operation we can take.
stmt, err := db.Prepare("CREATE TABLE IF NOT EXISTS your_table_name(ID SERIAL PRIMARY KEY, ATTR TEXT NOT NULL);")
if err != nil {
return nil, err
}
_, err = stmt.Exec()
if err != nil {
return nil, err
}
return db, nil
}
and to be able to run the demo we have a trivial main.go
, it's as simple as:
package main
import (
"log"
models "github.com/USERNAME/postgres-go-driver/models"
)
func main() {
_, err := models.InitDB()
if err != nil {
log.Println(err)
} else {
log.Println("Database schema successfully initialized")
}
}
Short, hopefully sweet, and a little bit of reference boilerplate for you, (or for me a few months from now) and remember the takeaway about it being demo code, this means you might consider:
- making username and password environment variables
- Not using a magic string to hardcode SQL in your code. You could separate this to a package, or packages for DML and DDL respectively.
- remember to not use
admin
&password
.
Best wishes.
Top comments (0)