Add database-related boilterplate code

[joel-alvarado]

Task: Add Database-Related Boilerplate Code

Revised: This task does not consider heavily consider error handling. Error handling must be the subject of a separate task.

Objective

The goal of this task is to set up the foundational database-related code that will allow interaction with the existing schema, which includes users, admins, reports, and alerts tables. This code will act as the boilerplate for connecting, querying, and managing the database for the SafeRUM app.

Task Description

This task involves creating boilerplate code for database management in the SafeRUM app. The focus will be on setting up a database connection, creating basic functions for interacting with users, admins, reports, and alerts tables. This code will serve as the base for all future interactions with the database and should follow best practices for security, scalability, and performance.

The boilerplate will include functions for basic CRUD (Create, Read, Update, Delete) operations on each table, as well as setting up configurations for connecting to the database (e.g., credentials, environment settings, and connection pooling).

Implementation

• Set up the connection to the database (e.g., using a library like SQLAlchemy, or native SQL drivers).
• Create functions to handle CRUD operations for the user, admin, report, and alert tables.
• Ensure the database code is modular and can be easily extended for future schema updates.
• Configure connection pooling and secure access to the database credentials using environment variables.
• Provide the ability to test queries and interact with the database programmatically.

Subtasks

1. Create database connection code and test the connection with the existing schema.
2. Implement boilerplate CRUD functions for user and admin tables.
3. Add CRUD operations for the users, admins, reports, and alerts tables.
4. Set up a configuration file for database credentials and connection settings.

Testing and Debugging:

• Ensure that the database connection is established correctly using environment-based credentials.
• Test all CRUD operations by running them against the existing tables (users, admins, reports, and alerts).
• Use database logging to capture any errors or exceptions in queries.
• Set up tests to validate each function works properly and that errors are handled gracefully.

Deadline: (Revised: This task carried on to M2 from M2.) The CRUD operations for the admins, users, alerts and reports table will be completed by

[joel-alvarado]

@evnd04 Re-opening because I accidentally added the completed label and it's supposed to be assigned to @shenied-maldonado

[shenied-maldonado]

After having discussed this with our DB manager, we agreed to implement CRUD operations as they become necessary. For this first milestone, we deemed user registration as the main focus for developing CRUD functions. A separate issue was opened for this and others will follow, leaving this as a parent task.