Link PostgreSQL DB to Server and Create Migration Script

[Dr-Lazarus]

Objective

Establish a connection between the PostgreSQL database and the Go server and create a database migration script to manage the database schema efficiently.

Tasks

1. Install PostgreSQL Go Driver:

   • Install the pq package, which is the PostgreSQL driver for Go, using the command go get github.com/lib/pq.

2. Configure Database Connection:

   • Update the Go server code to include functionality for connecting to the PostgreSQL database using environment variables for database credentials.

3. Environment Variables Setup:

   • Add database connection variables (DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME) to the .env.dev and .env.prod files.

4. Database Connection Testing:

   • Implement a function in Go to test the database connection immediately upon server start-up and log the connection status.

5. Create Migration Script:

   • Write a SQL script to create necessary database tables and relationships. Place this script in a new directory called migrations within the database folder.
   • Consider using a Go-based migration tool such as goose, golang-migrate, or gorm for handling migrations programmatically.

6. Implement Migration Logic in Go:

   • Set up migration logic in the Go application to run the migration script on server start-up or as a separate manual setup step.

7. Documentation:

   • Update the project documentation to include instructions on how to configure the database and run migrations.

Acceptance Criteria

• The server can successfully connect to the PostgreSQL database using credentials stored in environment variables.
• The database schema is correctly set up through a migration script when the server starts.
• The server is capable of handling database reconnections automatically in case of temporary disconnection.
• Documentation accurately describes the process to set up and verify the database connection.

[Dr-Lazarus]

Completed