added migrations via tern, updated db package to connect to pgsql db, updated config to build db connection string, updated example .newsstand.toml config, new pgx dependency

11 files changed, 148 insertions, 16 deletions

diff --git a/.envrc b/.envrc
new file mode 100644
index 0000000..869cb74
--- /dev/null
+++ b/.envrc
@@ -0,0 +1,2 @@
+export TERN_CONFIG=migrations/tern.conf
+export TERN_MIGRATIONS=migrations
diff --git a/.newsstandrc.toml.example b/.newsstandrc.toml.example
index 0b12f95..7b35ccc 100644
--- a/.newsstandrc.toml.example
+++ b/.newsstandrc.toml.example
@@ -1,7 +1,10 @@
 [database]
 adapter = "postgresql"
+hostname = "localhost"
+port = "5432"
 user = "newsstand"
-pass = "newsstand"
+secret = "newsstand"
+dbname = "newsstand"
 
 [server]
 port = "8080"
diff --git a/container-compose.yml b/container-compose.yml
index 42e2371..ca53ffa 100644
--- a/container-compose.yml
+++ b/container-compose.yml
@@ -16,8 +16,9 @@ services:
     image: postgres:latest
     restart: always
     environment:
-      POSTGRES_USER: newsstand
-      POSTGRES_PASSWORD: newsstand
+      - POSTGRES_USER=newsstand
+      - POSTGRES_PASSWORD=newsstand
+      - POSTGRES_DB=newsstand
     ports: 
       - "9002:5432"
     volumes:
diff --git a/go.mod b/go.mod
index ff596ad..7ed3daf 100644
--- a/go.mod
+++ b/go.mod
@@ -3,6 +3,16 @@ module gitlab.com/alexkavon/newsstand
 go 1.21.3
 
 require (
-	github.com/BurntSushi/toml v1.3.2 // indirect
-	github.com/go-chi/chi/v5 v5.0.10 // indirect
+	github.com/BurntSushi/toml v1.3.2
+	github.com/go-chi/chi/v5 v5.0.10
+	github.com/jackc/pgx/v5 v5.5.0
+)
+
+require (
+	github.com/jackc/pgpassfile v1.0.0 // indirect
+	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
+	github.com/jackc/puddle/v2 v2.2.1 // indirect
+	golang.org/x/crypto v0.14.0 // indirect
+	golang.org/x/sync v0.5.0 // indirect
+	golang.org/x/text v0.14.0 // indirect
 )
diff --git a/go.sum b/go.sum
index 361c46c..edfed08 100644
--- a/go.sum
+++ b/go.sum
@@ -1,4 +1,32 @@
 github.com/BurntSushi/toml v1.3.2 h1:o7IhLm0Msx3BaB+n3Ag7L8EVlByGnpq14C4YWiu/gL8=
 github.com/BurntSushi/toml v1.3.2/go.mod h1:CxXYINrC8qIiEnFrOxCa7Jy5BFHlXnUU2pbicEuybxQ=
+github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
+github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
+github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
 github.com/go-chi/chi/v5 v5.0.10 h1:rLz5avzKpjqxrYwXNfmjkrYYXOyLJd37pz53UFHC6vk=
 github.com/go-chi/chi/v5 v5.0.10/go.mod h1:DslCQbL2OYiznFReuXYUmQ2hGd1aDpCnlMNITLSKoi8=
+github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM=
+github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg=
+github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a h1:bbPeKD0xmW/Y25WS6cokEszi5g+S0QxI/d45PkRi7Nk=
+github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
+github.com/jackc/pgx/v5 v5.5.0 h1:NxstgwndsTRy7eq9/kqYc/BZh5w2hHJV86wjvO+1xPw=
+github.com/jackc/pgx/v5 v5.5.0/go.mod h1:Ig06C2Vu0t5qXC60W8sqIthScaEnFvojjj9dSljmHRA=
+github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk=
+github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
+github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
+github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
+github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
+github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
+github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
+github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk=
+github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4=
+golang.org/x/crypto v0.14.0 h1:wBqGXzWJW6m1XrIKlAH0Hs1JJ7+9KBwnIO8v66Q9cHc=
+golang.org/x/crypto v0.14.0/go.mod h1:MVFd36DqK4CsrnJYDkBA3VC4m2GkXAM0PvzMCn4JQf4=
+golang.org/x/sync v0.5.0 h1:60k92dhOjHxJkrqnwsfl8KuaHbn/5dl0lUPUklKo3qE=
+golang.org/x/sync v0.5.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk=
+golang.org/x/text v0.14.0 h1:ScX5w1eTa3QqT8oi6+ziP7dTV1S2+ALU0bI+0zXKWiQ=
+golang.org/x/text v0.14.0/go.mod h1:18ZOQIKpY8NJVqYksKHtTdi31H5itFRjB5/qKTNYzSU=
+gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
+gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
+gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA=
+gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
diff --git a/migrations/001_create_timestamp_trigger.sql b/migrations/001_create_timestamp_trigger.sql
new file mode 100644
index 0000000..4fa369a
--- /dev/null
+++ b/migrations/001_create_timestamp_trigger.sql
@@ -0,0 +1,7 @@
+CREATE OR REPLACE FUNCTION trigger_set_timestamp()
+RETURNS TRIGGER AS $$
+BEGIN
+    NEW.updated_at = NOW();
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
diff --git a/migrations/002_create_users_table.sql b/migrations/002_create_users_table.sql
new file mode 100644
index 0000000..a561a05
--- /dev/null
+++ b/migrations/002_create_users_table.sql
@@ -0,0 +1,16 @@
+CREATE TABLE users(
+    id SERIAL NOT NULL PRIMARY KEY,
+    username VARCHAR(50) NOT NULL,
+    secret VARCHAR(255) NOT NULL,
+    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TRIGGER set_timestamp
+BEFORE UPDATE ON users
+FOR EACH ROW
+EXECUTE PROCEDURE trigger_set_timestamp();
+
+---- create above / drop below ----
+
+DROP TABLE users;
diff --git a/migrations/tern.conf b/migrations/tern.conf
new file mode 100644
index 0000000..e52b80c
--- /dev/null
+++ b/migrations/tern.conf
@@ -0,0 +1,34 @@
+[database]
+# host is required (network host or path to Unix domain socket)
+host = localhost
+port = 9002
+# database is required
+database = newsstand
+# user defaults to OS user
+user = newsstand
+password = newsstand
+version_table = public.schema_version
+#
+# sslmode generally matches the behavior described in:
+# http://www.postgresql.org/docs/9.4/static/libpq-ssl.html#LIBPQ-SSL-PROTECTION
+#
+# There are only two modes that most users should use:
+# prefer - on trusted networks where security is not required
+# verify-full - require SSL connection
+# sslmode = prefer
+#
+# sslrootcert is generally used with sslmode=verify-full
+# sslrootcert = /path/to/root/ca
+
+# Proxy the above database connection via SSH
+# [ssh-tunnel]
+# host =
+# port = 22
+# user defaults to OS user
+# user =
+# password is not required if using SSH agent authentication
+# password =
+
+[data]
+# Any fields in the data section are available in migration templates
+# prefix = foo
diff --git a/src/conf/conf.go b/src/conf/conf.go
index 7a6dcbf..62ffb57 100644
--- a/src/conf/conf.go
+++ b/src/conf/conf.go
@@ -1,6 +1,7 @@
 package conf
 
 import (
+	"fmt"
 	"log"
 	"os"
 
@@ -18,7 +19,11 @@ type (
 	Db struct {
 		Adapter  string `toml:"adapter"`
 		User     string `toml:"user"`
-		Password string `toml:"pass"`
+		Secret   string `toml:"secret"`
+		Hostname string `toml:"hostname"`
+		Port     string `toml:"port"`
+		DbName   string `toml:"dbname"`
+		Url      string
 	}
 
 	Server struct {
@@ -43,7 +48,9 @@ func NewConf() *Conf {
 	c := Conf{
 		cwd,
 		filepath,
-		Db{},
+		Db{
+			Hostname: "localhost",
+		},
 		Server{
 			UiPath: cwd + "/ui",
 		},
@@ -52,11 +59,24 @@ func NewConf() *Conf {
 	if err != nil {
 		log.Fatalln(err)
 	}
+	c.setDbUrl()
 	log.Printf("Config loaded: %s", c)
 
 	return &c
 }
 
+func (c *Conf) setDbUrl() {
+	c.Db.Url = fmt.Sprintf(
+		"%s://%s:%s@%s:%s/%s",
+		c.Db.Adapter,
+		c.Db.User,
+		c.Db.Secret,
+		c.Db.Hostname,
+		c.Db.Port,
+		c.Db.DbName,
+	)
+}
+
 func (c *Conf) GetCwd() string {
 	return c.cwd
 }
diff --git a/src/db/db.go b/src/db/db.go
index 62218b2..4daf561 100644
--- a/src/db/db.go
+++ b/src/db/db.go
@@ -1,15 +1,26 @@
 package db
 
 import (
-	"database/sql"
+	"context"
+	"log"
+
+	"github.com/jackc/pgx/v5/pgxpool"
 
 	"gitlab.com/alexkavon/newsstand/src/conf"
 )
 
-type Db struct {
-	Db *sql.DB
-}
+func NewDb(config *conf.Conf) *pgxpool.Pool {
+	pool, err := pgxpool.New(context.Background(), config.Db.Url)
+	if err != nil {
+		log.Fatal(err)
+	}
+	defer pool.Close()
 
-func NewDb(config *conf.Conf) *sql.DB {
-	return &sql.DB{}
+	var testquery string
+	err = pool.QueryRow(context.Background(), "select 'Hello, PostgreSQL!'").Scan(&testquery)
+	if err != nil {
+		log.Fatal(err)
+	}
+	log.Println("Database connection pool created.", testquery)
+	return pool
 }
diff --git a/src/server/server.go b/src/server/server.go
index 9ba6104..aa9f7d2 100644
--- a/src/server/server.go
+++ b/src/server/server.go
@@ -1,21 +1,21 @@
 package server
 
 import (
-	"database/sql"
 	"net/http"
 
 	"github.com/go-chi/chi/v5"
+	"github.com/jackc/pgx/v5/pgxpool"
 	"gitlab.com/alexkavon/newsstand/src/conf"
 )
 
 type Server struct {
 	Router *chi.Mux
-	Db     *sql.DB
+	Db     *pgxpool.Pool
 	Config *conf.Conf
 	Ui     Ui
 }
 
-func NewServer(config *conf.Conf, db *sql.DB) *Server {
+func NewServer(config *conf.Conf, db *pgxpool.Pool) *Server {
 	return &Server{
 		Router: NewRouter(config),
 		Db:     db,