Created
April 23, 2021 22:02
-
-
Save kkohrt/f07c64cb7a15e402385f700ea1f43590 to your computer and use it in GitHub Desktop.
Golang DB test app running alongside dockerized Keratin Server, Postgres, & Redis
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--- | |
version: '3.9' | |
volumes: | |
redis_auth_data: | |
networks: | |
backend: | |
services: | |
myhost: | |
image: ubuntu | |
command: ["sleep", "infinity"] | |
networks: | |
backend: | |
keratin_auth: | |
image: keratin/authn-server:1.10.4 | |
# command: ["sleep", "infinity"] | |
ports: | |
- 8080:3000 | |
environment: | |
- AUTHN_URL=http://localhost:8080 | |
- APP_DOMAINS=localhost | |
- REDIS_URL=redis://redis_auth.docker:6379/1 | |
- DATABASE_URL=postgresql://keratin:SuperSecret!@mypostgres.docker:5432/keratin_authn?sslmode=disable | |
- SECRET_KEY_BASE=changeme2something4wesome | |
- HTTP_AUTH_USERNAME=hello | |
- HTTP_AUTH_PASSWORD=world | |
networks: | |
backend: | |
depends_on: | |
redis: | |
condition: service_started | |
postgres: | |
condition: service_healthy | |
redis: | |
image: redis | |
command: ['redis-server', '--appendonly', 'yes'] | |
ports: | |
- 6379:6379 | |
volumes: | |
- redis_auth_data:/data | |
networks: | |
backend: | |
aliases: | |
- redis_auth.docker | |
postgres: | |
image: postgres:12.6 | |
ports: | |
- 5432:5432 | |
environment: | |
- POSTGRES_PASSWORD=SuperSecret! | |
- POSTGRES_USER=keratin | |
- POSTGRES_DB=keratin_authn | |
healthcheck: | |
test: ["CMD", "pg_isready", "-U", "keratin", "-d", "keratin_authn"] | |
interval: 10s | |
timeout: 5s | |
retries: 5 | |
networks: | |
backend: | |
aliases: | |
- mypostgres.docker |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package main | |
import ( | |
"database/sql" | |
"fmt" | |
"log" | |
"net/url" | |
"os" | |
_ "github.com/lib/pq" | |
"github.com/jmoiron/sqlx" | |
) | |
var schema = ` | |
CREATE TABLE person ( | |
first_name text, | |
last_name text, | |
email text | |
); | |
CREATE TABLE place ( | |
country text, | |
city text NULL, | |
telcode integer | |
)` | |
type Person struct { | |
FirstName string `db:"first_name"` | |
LastName string `db:"last_name"` | |
Email string | |
} | |
type Place struct { | |
Country string | |
City sql.NullString | |
TelCode int | |
} | |
func main() { | |
dburl := os.ExpandEnv("$DATABASE_URL") | |
fmt.Printf("DB URL: %s\n\n", dburl) | |
// this Pings the database trying to connect | |
// use sqlx.Open() for sql.Open() semantics | |
db, err := sqlx.Connect("postgres", dburl) | |
if err != nil { | |
log.Fatalln(err) | |
} | |
the_url,err := url.Parse(dburl) | |
if err != nil { | |
log.Fatalln(err) | |
} | |
fmt.Printf("\n-----------\nParsed HOST: %s\n", the_url.Hostname()) | |
fmt.Printf("URL back to String: %s\n\n", the_url.String()) | |
// exec the schema or fail; multi-statement Exec behavior varies between | |
// database drivers; pq will exec them all, sqlite3 won't, ymmv | |
db.MustExec(schema) | |
tx := db.MustBegin() | |
tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason3", "Moiron3", "jmoiron3@jmoiron.net") | |
tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John3", "Doe3", "johndoeDNE3@gmail.net") | |
tx.MustExec("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New Jersey", "1") | |
tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Memphis", "853") | |
tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Taipai", "651") | |
// Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person | |
tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane3", "Citizen", "jane.citzen3@example.com"}) | |
tx.Commit() | |
// Query the database, storing results in a []Person (wrapped in []interface{}) | |
people := []Person{} | |
db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC") | |
jason, john := people[0], people[1] | |
fmt.Printf("%#v\n%#v", jason, john) | |
// Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"} | |
// Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@gmail.net"} | |
// You can also get a single result, a la QueryRow | |
jason = Person{} | |
err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason") | |
fmt.Printf("%#v\n", jason) | |
// Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"} | |
// if you have null fields and use SELECT *, you must use sql.Null* in your struct | |
places := []Place{} | |
err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC") | |
if err != nil { | |
fmt.Println(err) | |
return | |
} | |
usa, singsing, honkers := places[0], places[1], places[2] | |
fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers) | |
// Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1} | |
// Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65} | |
// Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852} | |
// Loop through rows using only one struct | |
place := Place{} | |
rows, err := db.Queryx("SELECT * FROM place") | |
for rows.Next() { | |
err := rows.StructScan(&place) | |
if err != nil { | |
log.Fatalln(err) | |
} | |
fmt.Printf("%#v\n", place) | |
} | |
// Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1} | |
// Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852} | |
// Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65} | |
// Named queries, using `:name` as the bindvar. Automatic bindvar support | |
// which takes into account the dbtype based on the driverName on sqlx.Open/Connect | |
_, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, | |
map[string]interface{}{ | |
"first": "Bin", | |
"last": "Smuth", | |
"email": "bensmith@allblacks.nz", | |
}) | |
// Selects Mr. Smith from the database | |
rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "Bin"}) | |
// Named queries can also use structs. Their bind names follow the same rules | |
// as the name -> db mapping, so struct fields are lowercased and the `db` tag | |
// is taken into consideration. | |
rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:first_name`, jason) | |
// batch insert | |
// batch insert with structs | |
personStructs := []Person{ | |
{FirstName: "Ardie", LastName: "Savea", Email: "asavea@ab.co.nz"}, | |
{FirstName: "Sonny Bill", LastName: "Williams", Email: "sbw@ab.co.nz"}, | |
{FirstName: "Ngani", LastName: "Laumape", Email: "nlaumape@ab.co.nz"}, | |
} | |
_, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email) | |
VALUES (:first_name, :last_name, :email)`, personStructs) | |
// batch insert with maps | |
personMaps := []map[string]interface{}{ | |
{"first_name": "Ardie", "last_name": "Savea", "email": "asavea@ab.co.nz"}, | |
{"first_name": "Sonny Bill", "last_name": "Williams", "email": "sbw@ab.co.nz"}, | |
{"first_name": "Ngani", "last_name": "Laumape", "email": "nlaumape@ab.co.nz"}, | |
} | |
_, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email) | |
VALUES (:first_name, :last_name, :email)`, personMaps) | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
The above works great on a Mac, but on an EC2 instance, not so much. | |
keratin_auth_1 | ~*~ Keratin AuthN v1.10.3 ~*~ | |
keratin_auth_1 | data.NewDB: dial tcp: lookup db: device or resource busy | |
docker_keratin_auth_1 exited with code 0 | |
The result was repeated with both mysql and posrgres docker containers as the target; | |
as well as an independent Postgres RDS instance. | |
In short: we can reproduce reliably in the cloud. | |
This seemed to point to something in the keratin container. We thought maybe the go | |
libraries, so we did additional testing to install go, git, and the sql libraries | |
used by keratin | |
go get github.com/lib/pq | |
go get github.com/jmoiron/sqlx | |
We were then able to get the demo sript from sqlx (https://github.com/jmoiron/sqlx#usage) | |
to run from both the independent ubuntu container, and even from the keratin container | |
itself, by replacing the connection parameters with the full postgres URL from the | |
docker-compose-keratin.yml file. | |
Then, to really check it out on the keratin box, we used the same DATABASE_URL environment | |
variable used by keratin, and added println statements to show that not only was it | |
pulled into the go app, but the libraries to parse the URL and use it to talk to postgres | |
also worked. | |
That test script is also attached. | |
Install process was basically | |
* launch the docker containers | |
=> keep keratin running using the commented out: command: ["sleep", "infinity"] | |
=> docker-compose -f docker-compose-keratin.yml up | |
* exec into the ash shell in the keratin container | |
=> docker ps | |
=> docker exec -it 712a68c5cc98 "/bin/ash" | |
* install go, git, and libraries | |
=> apk add go | |
=> apk add git | |
=> go get github.com/lib/pq | |
=> go get github.com/jmoiron/sqlx | |
* create the attached sqlxtest.go file | |
* run it | |
=> go run sqlxtest.go | |
Everthing seemed to work fine. So why couldn't ketarin find the postgres server? | |
Bonus: | |
Also tried to run the keratin server directlt, and got the "device or resource busy" | |
/app # ./authn migrate | |
Running migrations. | |
dial tcp: lookup mypostgres.docker: device or resource busy | |
/app # ./authn server | |
~*~ Keratin AuthN v1.10.4 ~*~ | |
data.NewDB: dial tcp: lookup mypostgres.docker: device or resource busy |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment