From 61d5f45189a40621bceeb14c6646031dd15ab6c2 Mon Sep 17 00:00:00 2001 From: Bobby <30593201+luciferreeves@users.noreply.github.com> Date: Tue, 20 Jan 2026 13:35:01 +0530 Subject: Implement database configuration and management with PostgreSQL support --- Makefile | 15 ++++++- config/config.go | 7 ++- config/env.go | 9 ++++ database/database.go | 50 +++++++++++++++++++++ database/migrate.go | 10 +++++ go.mod | 11 +++++ go.sum | 36 +++++++++++++++ models/user.go | 12 +++++ scripts/reset.db.sh | 94 ++++++++++++++++++++++++++++++++++++++++ toolchain/docker-compose.dev.yml | 20 +++++++++ 10 files changed, 262 insertions(+), 2 deletions(-) create mode 100644 database/database.go create mode 100644 database/migrate.go create mode 100644 models/user.go create mode 100644 scripts/reset.db.sh create mode 100644 toolchain/docker-compose.dev.yml diff --git a/Makefile b/Makefile index 9a00c39..261784a 100644 --- a/Makefile +++ b/Makefile @@ -1,8 +1,9 @@ BINARY_NAME=cafe BUILD_PATH=bin/$(BINARY_NAME) MAIN_PATH=$(BINARY_NAME)/main.go +COMPOSE_DEV=toolchain/docker-compose.dev.yml -.PHONY: setup clean tidy build run dev test all +.PHONY: setup clean tidy build run dev test postgres stop reset all setup: @echo "Setting up environment..." @@ -39,6 +40,18 @@ test: @echo "Running tests..." @go test -v ./... || true +postgres: + @echo "Starting database..." + @docker-compose -f $(COMPOSE_DEV) up -d + +stop: + @echo "Stopping database..." + @docker-compose -f $(COMPOSE_DEV) down + +reset: + @echo "Resetting database..." + @./scripts/reset.db.sh + all: setup clean build run .SILENT: \ No newline at end of file diff --git a/config/config.go b/config/config.go index 85f947c..293a135 100644 --- a/config/config.go +++ b/config/config.go @@ -8,7 +8,8 @@ import ( ) var ( - Server server + Server server + Database database ) func init() { @@ -19,4 +20,8 @@ func init() { if err := env.Parse(&Server); err != nil { log.Fatalf("Failed to parse ServerConfig: %v", err) } + + if err := env.Parse(&Database); err != nil { + log.Fatalf("Failed to parse DatabaseConfig: %v", err) + } } diff --git a/config/env.go b/config/env.go index bdac2b4..4e6b50f 100644 --- a/config/env.go +++ b/config/env.go @@ -6,3 +6,12 @@ type server struct { AppSecret string `env:"APP_SECRET" default:"mysecret"` DevMode bool `env:"DEV_MODE" default:"true"` } + +type database struct { + Host string `env:"DB_HOST" default:"localhost"` + Port int `env:"DB_PORT" default:"5432"` + Username string `env:"DB_USER" default:"postgres"` + Password string `env:"DB_PASS" default:""` + Name string `env:"DB_NAME" default:"cafe"` + SSLMode string `env:"DB_SSLMODE" default:"disable"` +} diff --git a/database/database.go b/database/database.go new file mode 100644 index 0000000..500373e --- /dev/null +++ b/database/database.go @@ -0,0 +1,50 @@ +package database + +import ( + "cafe/config" + "fmt" + "log" + + "gorm.io/driver/postgres" + "gorm.io/gorm" + "gorm.io/gorm/logger" +) + +var DB *gorm.DB + +func init() { + var err error + + DSN := fmt.Sprintf("host=%s port=%d user=%s dbname=%s sslmode=%s", + config.Database.Host, + config.Database.Port, + config.Database.Username, + config.Database.Name, + config.Database.SSLMode, + ) + + if config.Database.Password != "" { + DSN += fmt.Sprintf(" password=%s", config.Database.Password) + } + + loglevel := logger.Silent + if config.Server.DevMode { + loglevel = logger.Info + } + + dialector := postgres.Open(DSN) + + DB, err = gorm.Open(dialector, &gorm.Config{ + Logger: logger.Default.LogMode(loglevel), + }) + + if err != nil { + log.Fatalf("failed to connect to database: %v", err) + } + + if err = migrate(); err != nil { + log.Fatalf("failed to migrate database: %v", err) + } + + log.Println("database connection established") +} diff --git a/database/migrate.go b/database/migrate.go new file mode 100644 index 0000000..ad1dbf2 --- /dev/null +++ b/database/migrate.go @@ -0,0 +1,10 @@ +package database + +import "cafe/models" + +func migrate() error { + err := DB.AutoMigrate( + &models.User{}, + ) + return err +} diff --git a/go.mod b/go.mod index dd53fd2..738984a 100644 --- a/go.mod +++ b/go.mod @@ -5,11 +5,19 @@ go 1.25.5 require ( github.com/gofiber/fiber/v2 v2.52.10 github.com/joho/godotenv v1.5.1 + gorm.io/driver/postgres v1.6.0 + gorm.io/gorm v1.31.1 ) require ( github.com/andybalholm/brotli v1.1.0 // indirect github.com/google/uuid v1.6.0 // indirect + github.com/jackc/pgpassfile v1.0.0 // indirect + github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect + github.com/jackc/pgx/v5 v5.6.0 // indirect + github.com/jackc/puddle/v2 v2.2.2 // indirect + github.com/jinzhu/inflection v1.0.0 // indirect + github.com/jinzhu/now v1.1.5 // indirect github.com/klauspost/compress v1.17.9 // indirect github.com/mattn/go-colorable v0.1.13 // indirect github.com/mattn/go-isatty v0.0.20 // indirect @@ -18,5 +26,8 @@ require ( github.com/valyala/bytebufferpool v1.0.0 // indirect github.com/valyala/fasthttp v1.51.0 // indirect github.com/valyala/tcplisten v1.0.0 // indirect + golang.org/x/crypto v0.31.0 // indirect + golang.org/x/sync v0.10.0 // indirect golang.org/x/sys v0.28.0 // indirect + golang.org/x/text v0.21.0 // indirect ) diff --git a/go.sum b/go.sum index 5b70bcc..d99df37 100644 --- a/go.sum +++ b/go.sum @@ -1,9 +1,24 @@ github.com/andybalholm/brotli v1.1.0 h1:eLKJA0d02Lf0mVpIDgYnqXcUn0GqVmEFny3VuID1U3M= github.com/andybalholm/brotli v1.1.0/go.mod h1:sms7XGricyQI9K10gOSf56VKKWS4oLer58Q+mhRPtnY= +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/gofiber/fiber/v2 v2.52.10 h1:jRHROi2BuNti6NYXmZ6gbNSfT3zj/8c0xy94GOU5elY= github.com/gofiber/fiber/v2 v2.52.10/go.mod h1:YEcBbO/FB+5M1IZNBP9FO3J9281zgPAreiI1oqg8nDw= github.com/google/uuid v1.6.0 h1:NIvaJDMOsjHA8n1jAhLSgzrAzy1Hgr+hNrb57e+94F0= github.com/google/uuid v1.6.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo= +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-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo= +github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM= +github.com/jackc/pgx/v5 v5.6.0 h1:SWJzexBzPL5jb0GEsrPMLIsi/3jOo7RHlzTjcAeDrPY= +github.com/jackc/pgx/v5 v5.6.0/go.mod h1:DNZ/vlrUnhWCoFGxHAG8U2ljioxukquj7utPDgtQdTw= +github.com/jackc/puddle/v2 v2.2.2 h1:PR8nw+E/1w0GLuRFSmiioY6UooMp6KJv0/61nB7icHo= +github.com/jackc/puddle/v2 v2.2.2/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4= +github.com/jinzhu/inflection v1.0.0 h1:K317FqzuhWc8YvSVlFMCCUb36O/S9MCKRDI7QkRKD/E= +github.com/jinzhu/inflection v1.0.0/go.mod h1:h+uFLlag+Qp1Va5pdKtLDYj+kHp5pxUVkryuEj+Srlc= +github.com/jinzhu/now v1.1.5 h1:/o9tlHleP7gOFmsnYNz3RGnqzefHA47wQpKrrdTIwXQ= +github.com/jinzhu/now v1.1.5/go.mod h1:d3SSVoowX0Lcu0IBviAWJpolVfI5UJVZZ7cO71lE/z8= github.com/joho/godotenv v1.5.1 h1:7eLL/+HRGLY0ldzfGMeQkb7vMd0as4CfYvUVzLqw0N0= github.com/joho/godotenv v1.5.1/go.mod h1:f4LDr5Voq0i2e/R5DDNOoa2zzDfwtkZa6DnEwAbqwq4= github.com/klauspost/compress v1.17.9 h1:6KIumPrER1LHsvBVuDa0r5xaG0Es51mhhB9BQB2qeMA= @@ -15,15 +30,36 @@ github.com/mattn/go-isatty v0.0.20 h1:xfD0iDuEKnDkl03q4limB+vH+GxLEtL/jb4xVJSWWE github.com/mattn/go-isatty v0.0.20/go.mod h1:W+V8PltTTMOvKvAeJH7IuucS94S2C6jfK/D7dTCTo3Y= github.com/mattn/go-runewidth v0.0.16 h1:E5ScNMtiwvlvB5paMFdw9p4kSQzbXFikJ5SQO6TULQc= github.com/mattn/go-runewidth v0.0.16/go.mod h1:Jdepj2loyihRzMpdS35Xk/zdY8IAYHsh153qUoGf23w= +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/rivo/uniseg v0.2.0 h1:S1pD9weZBuJdFmowNwbpi7BJ8TNftyUImj/0WQi72jY= github.com/rivo/uniseg v0.2.0/go.mod h1:J6wj4VEh+S6ZtnVlnTBMWIodfgj8LQOQFoIToxlJtxc= +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= github.com/valyala/bytebufferpool v1.0.0 h1:GqA5TC/0021Y/b9FG4Oi9Mr3q7XYx6KllzawFIhcdPw= github.com/valyala/bytebufferpool v1.0.0/go.mod h1:6bBcMArwyJ5K/AmCkWv1jt77kVWyCJ6HpOuEn7z0Csc= github.com/valyala/fasthttp v1.51.0 h1:8b30A5JlZ6C7AS81RsWjYMQmrZG6feChmgAolCl1SqA= github.com/valyala/fasthttp v1.51.0/go.mod h1:oI2XroL+lI7vdXyYoQk03bXBThfFl2cVdIA3Xl7cH8g= github.com/valyala/tcplisten v1.0.0 h1:rBHj/Xf+E1tRGZyWIWwJDiRY0zc1Js+CV5DqwacVSA8= github.com/valyala/tcplisten v1.0.0/go.mod h1:T0xQ8SeCZGxckz9qRXTfG43PvQ/mcWh7FwZEA7Ioqkc= +golang.org/x/crypto v0.31.0 h1:ihbySMvVjLAeSH1IbfcRTkD/iNscyz8rGzjF/E5hV6U= +golang.org/x/crypto v0.31.0/go.mod h1:kDsLvtWBEx7MV9tJOj9bnXsPbxwJQ6csT/x4KIN4Ssk= +golang.org/x/sync v0.10.0 h1:3NQrjDixjgGwUOCaF8w2+VYHv0Ve/vGYSbdkTa98gmQ= +golang.org/x/sync v0.10.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk= golang.org/x/sys v0.0.0-20220811171246-fbc7d0a398ab/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg= golang.org/x/sys v0.6.0/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg= golang.org/x/sys v0.28.0 h1:Fksou7UEQUWlKvIdsqzJmUmCX3cZuD2+P3XyyzwMhlA= golang.org/x/sys v0.28.0/go.mod h1:/VUhepiaJMQUp4+oa/7Zr1D23ma6VTLIYjOOTFZPUcA= +golang.org/x/text v0.21.0 h1:zyQAAkrwaneQ066sspRyJaG9VNi/YJ1NfzcGB3hZ/qo= +golang.org/x/text v0.21.0/go.mod h1:4IBbMaMmOPCJ8SecivzSH54+73PCFmPWxNTLm+vZkEQ= +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= +gorm.io/driver/postgres v1.6.0 h1:2dxzU8xJ+ivvqTRph34QX+WrRaJlmfyPqXmoGVjMBa4= +gorm.io/driver/postgres v1.6.0/go.mod h1:vUw0mrGgrTK+uPHEhAdV4sfFELrByKVGnaVRkXDhtWo= +gorm.io/gorm v1.31.1 h1:7CA8FTFz/gRfgqgpeKIBcervUn3xSyPUmr6B2WXJ7kg= +gorm.io/gorm v1.31.1/go.mod h1:XyQVbO2k6YkOis7C2437jSit3SsDK72s7n7rsSHd+Gs= diff --git a/models/user.go b/models/user.go new file mode 100644 index 0000000..d51d5a4 --- /dev/null +++ b/models/user.go @@ -0,0 +1,12 @@ +package models + +import "gorm.io/gorm" + +type User struct { + gorm.Model + OpenID string `gorm:"uniqueIndex;not null"` + Username string `gorm:"uniqueIndex;not null"` + Email string `gorm:"uniqueIndex;not null"` + DisplayName string + IsAdmin bool `gorm:"default:false"` +} diff --git a/scripts/reset.db.sh b/scripts/reset.db.sh new file mode 100644 index 0000000..9c4a366 --- /dev/null +++ b/scripts/reset.db.sh @@ -0,0 +1,94 @@ +#!/usr/bin/env bash +set -e + +DEFAULT_DB="cafe" +DEFAULT_OWNER="$(whoami)" +DEFAULT_HOST="localhost" +DEFAULT_PORT="5432" + +read -p "Enter database name [$DEFAULT_DB]: " DB_NAME +DB_NAME="${DB_NAME:-$DEFAULT_DB}" + +read -p "Enter database owner username [$DEFAULT_OWNER]: " DB_OWNER +DB_OWNER="${DB_OWNER:-$DEFAULT_OWNER}" + +read -s -p "Enter password for $DB_OWNER (optional): " DB_PASSWORD +echo + +PSQL_BASE="psql -d postgres -U $DB_OWNER" + +if [ -n "$DB_PASSWORD" ]; then + export PGPASSWORD="$DB_PASSWORD" +fi + +PSQL_TCP="$PSQL_BASE -h $DEFAULT_HOST -p $DEFAULT_PORT" +PSQL_LOCAL="$PSQL_BASE" + +echo +echo "Detecting PostgreSQL connection mode..." + +if $PSQL_TCP -c "SELECT 1" >/dev/null 2>&1; then + PSQL_CMD="$PSQL_TCP" + CONNECTION_INFO="TCP ($DEFAULT_HOST:$DEFAULT_PORT)" +elif $PSQL_LOCAL -c "SELECT 1" >/dev/null 2>&1; then + PSQL_CMD="$PSQL_LOCAL" + CONNECTION_INFO="Local socket (peer / trust)" +else + echo "Unable to connect to PostgreSQL." + echo "Tried:" + echo " - TCP: $DEFAULT_HOST:$DEFAULT_PORT" + echo " - Local: UNIX socket" + echo + echo "Fix one of the following:" + echo " • Docker Postgres not running" + echo " • Wrong user ($DB_OWNER)" + echo " • Password required but not provided" + echo " • pg_hba.conf blocks access" + exit 1 +fi + +echo "Connected via: $CONNECTION_INFO" + +DB_EXISTS=$($PSQL_CMD -Atc \ + "SELECT 1 FROM pg_database WHERE datname = '$DB_NAME';" \ + || true) + +if [ "$DB_EXISTS" = "1" ]; then + ACTION="reset" +else + ACTION="create" +fi + +echo +echo "=== Database $ACTION ===" +echo "Database: $DB_NAME" +echo "Owner: $DB_OWNER" +echo "Auth: $CONNECTION_INFO" +echo + +read -p "Proceed? [Y/n]: " CONFIRM +if [[ "$CONFIRM" =~ ^[Nn]$ ]]; then + echo "Cancelled." + exit 0 +fi + +echo +echo "Applying changes..." + +$PSQL_CMD -c "DROP DATABASE IF EXISTS \"$DB_NAME\";" +$PSQL_CMD -c "CREATE DATABASE \"$DB_NAME\" OWNER \"$DB_OWNER\";" + +echo +echo "Database '$DB_NAME' ready." + +if [ -n "$DB_PASSWORD" ]; then + DSN="postgresql://$DB_OWNER:$DB_PASSWORD@$DEFAULT_HOST:$DEFAULT_PORT/$DB_NAME?sslmode=disable" +else + DSN="postgresql://$DB_OWNER@$DEFAULT_HOST:$DEFAULT_PORT/$DB_NAME?sslmode=disable" +fi + +echo +echo "DSN:" +echo "$DSN" + +unset PGPASSWORD \ No newline at end of file diff --git a/toolchain/docker-compose.dev.yml b/toolchain/docker-compose.dev.yml new file mode 100644 index 0000000..caba96a --- /dev/null +++ b/toolchain/docker-compose.dev.yml @@ -0,0 +1,20 @@ +services: + postgres: + image: postgres:18-alpine + container_name: cafe-postgres-dev + ports: + - "5432:5432" + environment: + POSTGRES_DB: cafe_dev + POSTGRES_USER: cafe_dev + POSTGRES_PASSWORD: dev_password + volumes: + - postgres_dev_data:/var/lib/postgresql/data + healthcheck: + test: ["CMD-SHELL", "pg_isready -U cafe_dev"] + interval: 5s + timeout: 3s + retries: 5 + +volumes: + postgres_dev_data: \ No newline at end of file -- cgit v1.2.3