When working on database projects, you often find yourself managing and executing multiple SQL files. Whether these files are for creating schemas, seeding data, or running migrations, orchestrating them efficiently can save you time and reduce errors. In this post, we’ll explore different ways to orchestrate SQL files, catering to various levels of complexity and toolsets.

---

1. Using a Bash or Batch Script

For quick and straightforward execution, shell scripts can be an effective solution. These scripts iterate through a directory of SQL files and execute them sequentially.

Bash Script Example (Linux/MacOS):

#!/bin/bash
# Database configuration
DB_HOST="localhost"
DB_USER="username"
DB_NAME="database"
DB_PASS="password"

# Iterate over SQL files
for file in /path/to/sql/files/*.sql; do
    echo "Executing $file"
    psql -h $DB_HOST -U $DB_USER -d $DB_NAME -f "$file"
done

Batch Script Example (Windows):

@echo off
SET DB_HOST=localhost
SET DB_USER=username
SET DB_NAME=database

FOR %%f IN (C:\path\to\sql\files\*.sql) DO (
    echo Executing %%f
    sqlcmd -S %DB_HOST% -d %DB_NAME% -U %DB_USER% -i %%f
)

These scripts are easy to customize for any database engine by replacing psql or sqlcmd with your preferred database CLI tool.

---

2. Leveraging Python for Greater Flexibility

Python provides more control and flexibility, especially when you need error handling or conditional execution.

Python Example Using psycopg2 (PostgreSQL):

import os
import psycopg2

# Database configuration
conn = psycopg2.connect(
    host="localhost",
    database="database",
    user="username",
    password="password"
)
cursor = conn.cursor()

# Directory containing SQL files
sql_folder = "/path/to/sql/files"

# Execute each SQL file
for sql_file in os.listdir(sql_folder):
    if sql_file.endswith(".sql"):
        with open(os.path.join(sql_folder, sql_file), 'r') as file:
            query = file.read()
            print(f"Executing {sql_file}")
            cursor.execute(query)
            conn.commit()

cursor.close()
conn.close()

This approach allows you to add logging, manage dependencies, or even parallelize execution.

---

3. Automating with Apache Airflow

For more complex workflows, a task orchestration tool like Apache Airflow can manage the execution of SQL files. Airflow’s Directed Acyclic Graphs (DAGs) allow for task dependencies and detailed monitoring.

Airflow Example:

from airflow import DAG
from airflow.operators.postgres_operator import PostgresOperator
from datetime import datetime

default_args = {
    'owner': 'airflow',
    'start_date': datetime(2024, 1, 1),
}

with DAG('sql_file_execution', default_args=default_args, schedule_interval=None) as dag:
    sql_files = ["script1.sql", "script2.sql", "script3.sql"]

    for sql_file in sql_files:
        PostgresOperator(
            task_id=f'exec_{sql_file}',
            postgres_conn_id='postgres_default',
            sql=f'/path/to/sql/files/{sql_file}',
        )

Airflow provides extensive capabilities for retry logic, task monitoring, and integration with other systems.

---

4. Integration with CI/CD Pipelines

If your SQL scripts are part of a deployment pipeline, you can integrate them into Continuous Integration/Continuous Deployment (CI/CD) tools like GitHub Actions, Jenkins, or GitLab CI.

GitHub Actions Example:

name: Execute SQL Files

on:
  push:
    branches:
      - main

jobs:
  execute-sql:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v3

      - name: Execute SQL files
        run: |
          for file in ./sql/*.sql; do
            psql -h localhost -U username -d database -f "$file"
          done
        env:
          PGPASSWORD: ${{ secrets.DB_PASSWORD }}

This setup ensures your SQL scripts are executed automatically whenever changes are pushed to your repository.

---

Best Practices

1. Order and Dependencies: Ensure scripts are named or ordered to reflect execution sequence.
2. Error Handling: Implement error logging and rollback mechanisms.
3. Version Control: Store your SQL scripts in a version control system like Git.
4. Parameterization: Use environment variables or configuration files to avoid hardcoding credentials.
5. Testing: Test scripts in a staging environment before running in production.

---

Conclusion

Whether you’re running a handful of scripts or orchestrating complex workflows, there’s a solution to match your needs. Simple shell scripts work for basic tasks, while Python, Airflow, and CI/CD tools offer advanced capabilities for larger projects. Choose the approach that aligns best with your requirements and infrastructure.