🛠️ PostgreSQL Admin Tools

A lightweight admin interface and CLI toolkit for PostgreSQL, built with Flask, SQLAlchemy, and psycopg2.

Manage your databases and tables with ease – via web or terminal.

---

📥 Clone the Repository

To get started, clone this repository to your local machine using Git:

git clone https://github.com/TamerOnLine/postgres-admin-tools.git
cd postgres-admin-tools

• Make sure you have Git installed.
• You can download it from.

_{📸 Cloning the repository via Git command line}

---

📦 Features

• 🔐 Secure user system with Flask-Login (default admin: admin/12345)
• ⚙️ Create or drop PostgreSQL databases
• 🧱 Manage tables (create / migrate / drop) via SQLAlchemy
• 💾 Full database backup & restore using pg_dump / psql
• 🧩 Easy environment config via .env file
• 🌐 Flask-based web UI ready out of the box

---

🎬 Demo

_{📽️ Quick demo of the PostgreSQL Admin Tools in action}

---

🔧 Tech Stack

Technology	Description
Python	Core programming language
PostgreSQL	Relational database engine
SQLAlchemy	ORM for database modeling
psycopg2	PostgreSQL driver for Python
python-dotenv	Load .env variables into runtime

---

🧱 Project Structure

postgres-admin-tools/
├── myapp.py                  # Flask app with login & DB panel
├── requirements.txt
├── LICENSE
├── README.md
└── models/
    ├── models_definitions.py # SQLAlchemy models
    └── db_postgres/
        ├── create.py         # Create DB if not exists
        ├── drop.py           # Drop DB (safe disconnect)
        ├── drop_table.py     # Drop individual/all tables
        ├── manage_tables.py  # Schema updates
        ├── BACKUP.py         # Backup to SQL file
        ├── RESTORE.py        # Restore from backup
        └── db_config.py      # Load from .env

---

⚙️ Environment Setup

Create a .env file in the root directory:

DATABASE_URL=postgresql://user:password@localhost:5432/your_db
SECRET_KEY=your_secret_here

---

🚀 Quick Start

1. Create Virtual Environment

# Windows
py -3.12 -m venv venv
.\venv\Scripts\Activate

_{📸 Virtual environment activated successfully}

---

# macOS / Linux
python3 -m venv venv
source venv/bin/activate

---

Install Dependencies

pip install -r requirements.txt

_{📸 Installing dependencies using pip}

---

Create Database

py .\models\db_postgres\create.py

_{📸 Creating PostgreSQL database}

---

Drop Database

py .\models\db_postgres\drop.py

_{📸 Dropping the database safely}

---

Drop Tables

py .\models\db_postgres\drop_table.py

_{📸 Dropping selected or all tables}

---

Manage Tables

py .\models\db_postgres\manage_tables.py

_{📸 CLI interface to manage tables (create or sync)}

---

Backup Database

py .\models\db_postgres\BACKUP.py

_{📸 Generating SQL backup file using `pg_dump`}

---

Restore Database

py .\models\db_postgres\RESTORE.py

_{📸 Restoring database from SQL file}

---

🛡️ User System

The system automatically creates a default admin user on first run if no user with username admin is found.

• Username: admin
• Password: 12345

To manually trigger this, run the following command:

py .\models\models_definitions.py

You can modify this logic in models/models_definitions.py.

_{📸 Creating default admin user if not exists}

---

🧪 SQLite Test Mode

If DATABASE_URL is not set in the .env, the system defaults to SQLite (sqlite:///test.db) for quick testing.

Note: Scripts like create.py, BACKUP.py, etc., require PostgreSQL and do not support SQLite.

---

📋 CLI Command Summary

Operation	Script	Mode
🏗️ Create Database	create.py	CLI
❌ Drop Database	drop.py	CLI
🧹 Drop Tables	drop_table.py	Interactive CLI
🧩 Manage Tables	manage_tables.py	Interactive CLI
💾 Backup Database	BACKUP.py	CLI
♻️ Restore from Backup	RESTORE.py	Interactive CLI

---

📄 License

This project is licensed under the MIT License.
See the LICENSE file for details.

---

👨‍💻 Author

Tamer Hamad Faour
GitHub: @TamerOnLine