License: MIT DB Test

PostgreSQL MySQL SQLite GitHub Actions CI

A hands-on educational toolkit for managing PostgreSQL databases using Python, SQLAlchemy, and psycopg2 – fully terminal-based and modular.

Learn and manage PostgreSQL like a pro – from creating databases to restoring backups, all via CLI.

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

git clone https://github.com/TamerOnLine/postgres_study_project.git
cd postgres_study_project

• Make sure you have Git installed: https://git-scm.com

• 🧠 Modular CLI tools for PostgreSQL admin tasks
• 🧱 SQLAlchemy models for clean DB interaction
• 🔄 Create, drop, and manage databases or tables interactively
• 💾 Backup and restore using pg_dump / psql
• 🧩 Environment configuration with .env and python-dotenv
• 🧪 Multi-database support: SQLite, PostgreSQL, and MySQL

tameronline-postgres_study_project/
├── main.py # CLI launcher for all tools
├── config/ # Environment and session handlers
├── models/ # SQLAlchemy models
├── tools/ # All database tools (PostgreSQL, tester, etc.)
└── requirements.txt 

Create a .env file in the root folder with content like:

POSTGRES_DB_USER=postgres
POSTGRES_DB_PASSWORD=yourpassword
POSTGRES_DB_HOST=localhost
POSTGRES_DB_PORT=5432
POSTGRES_DB_NAME=my_database

You can also configure:

MYSQL_DB_USER=...
SQLITE_DB_NAME=default.db

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

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

pip install -r requirements.txt

python main.py

You'll see a menu to select the tool you want to run interactively.

Each tool is located in tools/db/postgres/ and can be run directly:

Test PostgreSQL, MySQL, and SQLite connectivity with:

python tools/db/connection_tester/main.py

By default, the project uses .env to decide the environment.
To enable protection from dangerous actions in production mode, set:

FLASK_ENV=production

This will block destructive actions like dropping all tables.

Models are defined in models/:

# Example: Product model
class Product(Base):
 id = Column(Integer, primary_key=True)
 name = Column(String(100))
 price = Column(Float)

You can customize and extend these models freely.

Backups are automatically saved under:

backups/YYYY/MM/your_backup_file.sql

You can choose a custom name or use the auto-generated timestamp.

This project was built for:

• Understanding PostgreSQL database operations
• Practicing CLI tools and modular code design
• Building toward future full-stack admin tools

Licensed under the MIT License.
See the LICENSE file for details.

Tamer Hamad Faour
GitHub: @TamerOnLine