Optimizing SQL Project Structure: Lessons from a Directory Update

Introduction

In the Triangulo-bonaerense project, like many data-intensive applications, SQL scripts are a core component. As our database schema evolved and stored procedures grew, we noticed a common challenge: maintaining a clear, navigable structure for our SQL files. A recent "directory update" was a seemingly minor commit, but it represented a crucial step towards improving the overall maintainability and developer experience for our SQL codebase. This post shares our insights into why explicit organization matters for SQL-heavy projects.

What Worked

Clearer Separation of Concerns

The most immediate benefit was achieving a clearer separation of concerns. By adopting a convention where different types of SQL assets reside in dedicated directories, we made it intuitive to understand the purpose of each script. For instance, DDL (Data Definition Language) for tables and views now lives separately from DML (Data Manipulation Language) or stored procedures.

This is an example of the structure we adopted:

sql/
├── migrations/
│   ├── V1_create_users_table.sql
│   └── V2_add_products_table.sql
├── schema/
│   ├── tables/
│   │   ├── users.sql
│   │   └── products.sql
│   └── views/
│       └── active_users_view.sql
├── procedures/
│   ├── get_order_details.sql
│   └── update_inventory.sql
└── seed_data/
    └── initial_data.sql

Faster Navigation and Collaboration

Developers can now quickly locate relevant scripts, reducing the time spent searching through a flat or inconsistently structured sql/ folder. This also significantly streamlined our code review process. When a pull request involves a new table or an updated stored procedure, reviewers know exactly where to find the affected files, making it easier to track changes and provide targeted feedback.

What Surprised Us

Reduced Onboarding Time

While we anticipated benefits for existing team members, the positive impact on onboarding new developers was a pleasant surprise. New hires could grasp the database-related codebase structure much faster, enabling them to contribute meaningfully to SQL tasks sooner than before. The explicit organization acted as a self-documenting guide.

What We'd Do Differently

1. Earlier Adoption: If starting a new SQL-heavy project, we would implement these organizational conventions from day one. Retrofitting an existing codebase, even a relatively small one, always involves some friction.
2. Standardize Naming Conventions: Beyond directory structure, enforcing consistent naming conventions for files and objects within those directories (e.g., V<version>__<description>.sql for migrations) would further enhance clarity and consistency.

Verdict

Even a seemingly simple "directory update" can pave the way for substantial improvements in project maintainability, developer experience, and team collaboration. For Triangulo-bonaerense, this small step in organizing our SQL assets has yielded significant long-term benefits, proving that order truly does simplify complexity.

Generated with Gitvlg.com