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

3 files changed, 57 insertions, 0 deletions

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