As our systems grow, maintaining high-quality, performant database schemas becomes a massive pain point. I’m excited to share a comprehensive open-source package I’ve just completed to automatically audit your existing database design!
Meet db-schema-auditor! This isn't just a linter; it's a powerful tool designed to find the critical issues impacting performance and maintainability, giving you the data (and even the fixes) you need to optimize your architecture.
Key Features at a Glance
- Massive DB Support: Audits schemas for MySQL, MariaDB, MSSQL, Oracle, SQLite, and PostgreSQL.
-
Automatic Fix Generation: Use the
--generate-fixesflag to automatically generate migration files (e.g., Index creation scripts) for suggested fixes. - Model Analysis: Analyzes consistency between Database Foreign Keys and your Model Relationships (ideal for PHP/Laravel developers).
- Flexible Reporting: Get reports in Console (default), JSON, Markdown, CSV, or HTML.
🔗 GitHub Repo: https://github.com/cleaniquecoders/db-schema-auditor
🔍 What Exactly Gets Audited?
The auditor goes deep, focusing on issues that significantly impact performance and integrity:
| Category | Issues Detected |
|---|---|
| Database Structure |
Missing indexes on foreign key columns, use of ambiguous data types (e.g., FLOAT for financial data), missing constraints, and suspicious column patterns. |
| Model Relationships | Checks for missing inverse relationships and ensures a corresponding model relationship exists for every database foreign key (when using the --models flag). |
| Data Integrity | Searches for Orphaned Records in relationships to ensure referential integrity. |
| Fix Generation | Generates scripts for index creation, unique constraints, and suggested foreign keys. |
💡 Usage Examples (CLI)
Since the package is built to be a Composer dependency, running it via the command line is straightforward:
First, install it as a dev dependency and optionally publish migrations/config:
composer require cleaniquecoders/db-schema-auditor --dev
php artisan vendor:publish --tag="db-schema-auditor-migrations"
php artisan migrate
Then, run your audits:
# Basic audit with summary in the console
php artisan db:audit
# Audit, analyze model relationships, and save history to the database
php artisan db:audit --models --save-database
# Full audit, generate fix migrations, and output as a nice Markdown report
php artisan db:audit --models --generate-fixes --format=markdown
# Audit a specific database connection
php artisan db:audit --connection=secondary
Call for Use Cases! 📢
I believe in FOC (Free of Charge) for valuable public packages.
If you have a specific, real-world use case you think this package needs to cover or be tested against (e.g., custom naming conventions, or advanced PostgreSQL features like Partial Index validation), please roger-roger (let me know)! Drop a comment below or open an Issue on GitHub.
I'm dedicated to growing this package based on community needs and real-world scenarios.
Let's build a better tool for better schemas! Happy Coding!
#opensource #database #devops #mysql #postgresql #php #laravel
Photo by Scott Graham on Unsplash
Top comments (0)