Database Manager - Lab Environment

A self-service web application for creating database accounts in a lab environment. Students can create their own MySQL or PostgreSQL databases with individual access credentials.

Features

• Student Self-Service: Students can create their own database accounts
• Multi-Database Support: Supports MySQL and PostgreSQL
• Admin Panel: Administrators can manage all accounts and monitor system status
• Secure Authentication: Password hashing and session management
• Modern UI: Responsive design using Tailwind CSS
• Account Tracking: All accounts stored in SQLite database

Prerequisites

Before running this application, ensure you have:

1. Node.js (version 14 or higher)
2. MySQL Server (if supporting MySQL databases)
3. PostgreSQL Server (if supporting PostgreSQL databases)

Installation

1. Clone or download the project

   cd c:\laragon-new\www\db-manager

2. Install dependencies

   npm install

3. Configure environment variables

   copy .env.example .env

   Edit the .env file with your database server credentials:

   # Server Configuration
   PORT=3000
   SESSION_SECRET=change-this-to-a-secure-secret
   
   # MySQL Configuration
   MYSQL_HOST=localhost
   MYSQL_PORT=3306
   MYSQL_ADMIN_USER=root
   MYSQL_ADMIN_PASSWORD=your_mysql_root_password
   
   # PostgreSQL Configuration
   POSTGRES_HOST=localhost
   POSTGRES_PORT=5432
   POSTGRES_ADMIN_USER=postgres
   POSTGRES_ADMIN_PASSWORD=your_postgres_password

4. Start the application

   # Development mode with auto-restart
   npm run dev
   
   # Production mode
   npm start

5. Access the application

   • Main application: http://localhost:3000
   • Admin panel: http://localhost:3000/admin/login

Default Admin Credentials

• Username: admin
• Password: admin123

⚠️ Important: Change these credentials in production by updating the database directly.

Database Server Setup

MySQL Setup

1. Install MySQL Server
2. Create a root user or dedicated admin user
3. Ensure the MySQL service is running
4. Update the MySQL configuration in .env

PostgreSQL Setup

1. Install PostgreSQL Server
2. Create a superuser or dedicated admin user
3. Ensure the PostgreSQL service is running
4. Update the PostgreSQL configuration in .env

Usage

For Students

1. Visit the main page: http://localhost:3000
2. Select database engine (MySQL or PostgreSQL)
3. Enter desired username and password
4. Click "Create Database Account"
5. Use the provided credentials to connect to your database

For Administrators

1. Visit admin login: http://localhost:3000/admin/login
2. Login with admin credentials
3. View all created accounts
4. Monitor database connection status
5. Delete accounts when needed

Database Schema

The application uses SQLite to track created accounts:

users table

• id: Primary key
• username: Database username
• database_engine: mysql or postgresql
• database_name: Generated database name
• created_at: Account creation timestamp
• status: Account status (active/inactive)

admin_users table

• id: Primary key
• username: Admin username
• password_hash: Bcrypt hashed password
• created_at: Admin creation timestamp

Security Considerations

1. Change Default Credentials: Update admin password in production
2. Secure Database Servers: Ensure MySQL/PostgreSQL are properly configured
3. Network Security: Use firewall rules to restrict database access
4. SSL/TLS: Enable HTTPS in production environments
5. Input Validation: All user inputs are validated and sanitized

Project Structure

db-manager/
├── app.js                 # Main application file
├── package.json           # Dependencies and scripts
├── .env.example          # Environment configuration template
├── config/
│   └── database.js       # SQLite database configuration
├── routes/
│   ├── home.js          # Home page routes
│   ├── admin.js         # Admin authentication routes
│   └── api.js           # API endpoints
├── utils/
│   └── databaseManager.js # MySQL/PostgreSQL management
├── views/
│   ├── index.ejs        # Student home page
│   ├── admin-login.ejs  # Admin login page
│   └── admin-dashboard.ejs # Admin dashboard
└── public/              # Static files (if needed)

API Endpoints

Public Endpoints

• GET / - Student home page
• POST /api/create-account - Create database account

Admin Endpoints

• GET /admin/login - Admin login page
• POST /admin/login - Admin authentication
• GET /admin/dashboard - Admin dashboard
• POST /admin/logout - Admin logout
• GET /api/users - Get all users (admin only)
• DELETE /api/users/:id - Delete user (admin only)
• GET /api/test-connections - Test database connections (admin only)

Troubleshooting

Common Issues

1. Database Connection Failed

   • Check if MySQL/PostgreSQL services are running
   • Verify credentials in .env file
   • Check firewall settings

2. Permission Denied

   • Ensure admin user has CREATE USER and CREATE DATABASE privileges
   • Check MySQL/PostgreSQL user permissions

3. Port Already in Use

   • Change PORT in .env file
   • Kill existing processes using the port

Error Messages

The application provides detailed error messages for common issues:

• Invalid credentials
• Database connection failures
• Duplicate usernames
• Validation errors

License

This project is licensed under the ISC License.

Support

For technical issues or questions, please contact your system administrator or instructor.