3.2 KiB

Raw Permalink Blame History

PostgreSQL Database Tester

This is a Python-based testing tool for PostgreSQL databases. It uses SQLAlchemy for ORM and Alembic for database migrations.

Features

Test PostgreSQL database connection
Create database schema using SQLAlchemy models
Run Alembic migrations
Insert test data
Query and display database content

Setup

Make sure you have a PostgreSQL server running (locally or via Docker)

Install dependencies:

# Using uv
uv init
uv add alembic sqlalchemy-mixins psycopg2-binary requests

# Activate the virtual environment
source .venv/bin/activate

Set up environment variables (optional):

export DATABASE_URL="postgresql://postgres:password@localhost:5432/postgres"

Project Structure

./
├── alembic/              # Alembic migration files
│   ├── versions/         # Migration version files
│   ├── env.py            # Alembic environment configuration
│   └── script.py.mako    # Migration script template
├── app/                  # Application package
│   ├── models/           # SQLAlchemy models
│   │   ├── __init__.py   # Models package initialization
│   │   ├── user.py       # User model
│   │   └── post.py       # Post model
│   └── __init__.py       # Application initialization
├── alembic.ini           # Alembic configuration
├── db_tester.py          # Database testing script
└── README.md             # This file

Usage

Test Database Connection

python db_tester.py --connection

Run Alembic Migration

python db_tester.py --migration

Create Database Tables

python db_tester.py --create-tables

Insert Test Data

python db_tester.py --insert-data

Query Test Data

python db_tester.py --query-data

Run Stress Test with 100 Concurrent Connections

python db_tester.py --stress-test

You can also specify a custom number of threads:

python db_tester.py --stress-test --threads 200

Example stress test results with 200 connections:

📊 Stress test results:
  - Total connections: 200
  - Successful connections: 200
  - Failed connections: 0
  - Duration: 1.46 seconds
  - Connections per second: 136.80

These results demonstrate excellent performance with the optimized PostgreSQL configuration (4 CPUs, 16GB RAM).

Run All Tests

python db_tester.py --all

Connecting to PostgreSQL Container

If you're using the PostgreSQL Docker container from the parent project, you can connect to it by setting the appropriate DATABASE_URL:

export DATABASE_URL="postgresql://postgres:password@localhost:5432/postgres"

Replace password with the actual password from your .env file.

Customizing Models

You can modify the SQLAlchemy models in the app/models/ directory to match your specific database schema requirements.

Troubleshooting

Connection Issues: Make sure your PostgreSQL server is running and accessible
Migration Errors: Check the Alembic version files for any issues
Model Errors: Ensure your SQLAlchemy models are correctly defined

3.2 KiB Raw Permalink Blame History