Database DevOps CI/CD

Traditionally, database changes have been managed separately from application code, often leading to deployment challenges,

inconsistencies, and potential downtime. Modern DevOps practices are now extending to databases through Database DevOps

approaches that bring automation, version control, testing, and continuous delivery to database changes.

In this lesson, you'll learn how to integrate database changes into CI/CD pipelines, implement version control

for database schema and reference data, automate database deployments, and ensure the safety and reliability of database changes

throughout the development lifecycle.

Why Database DevOps Matters

According to the 2023 State of Database DevOps report, organizations that implement Database DevOps practices

experience 63% fewer deployment failures and can recover from incidents 60% faster than those using traditional database

management approaches.

Version Control

CI Practices

CD Deployment

Database Schema Version Control

Version controlling database schema changes is fundamental to Database DevOps. There are several approaches to tracking and applying

database changes:

Migration-Based Approaches

Migration scripts are sequential, versioned SQL scripts that transform a database schema from one version to another.

Each script represents a specific change to the database, and they are applied in order.

Popular migration tools include:

: Java-based, version-controlled database migrations

: XML, JSON, YAML, or SQL-based database schema changes with rollback support

: Database-agnostic change management with dependencies

ORM Migrations

: Migration systems in ORMs like Django Migrations, Rails Migrations, or Prisma Migrate

Migration scripts are typically named with a version number, description, and .sql extension:

State-Based Approaches

State-based database version control defines the desired end state of the database schema rather than the steps

to get there. The tool compares the current state with the desired state and generates the necessary changes.

Popular state-based tools include:

SQL Server Data Tools (SSDT)

: Microsoft's state-based schema version control

Redgate SQL Compare

: Compares and synchronizes SQL Server database schemas

: PostgreSQL database modeler and schema generator

schemaSync

: Schema comparison and synchronization tools

State vs. Migrations

While migration-based approaches are more common in Database DevOps and provide greater control over how changes are applied,

state-based approaches can be simpler for developers to understand and maintain. Many teams use a hybrid approach, using

migrations for production deployment while maintaining a reference state schema.

Reference Data Version Control

Reference data (lookup tables, configuration data, etc.) should also be version-controlled. This ensures consistency

across environments and allows for controlled updates to critical data.

Approaches for versioning reference data include:

Migration scripts

: Including data INSERT/UPDATE operations in migrations

Seed scripts

: Separate scripts specifically for reference data

Data format files

: YAML, JSON, or CSV files that are loaded during deployment

Database-specific tools

: pg_dump/pg_restore with data-only options

Version Control Best Practices

Keep migrations idempotent

Migrations should be designed to be rerunnable without causing errors or inconsistencies. Use constructs like

"IF NOT EXISTS" and "ON CONFLICT" to make scripts idempotent.

Implement backward compatibility

Where possible, structure changes to be backward compatible with the previous application version to support zero-downtime deployments.

Use techniques like "expand and contract" patterns.

Include rollback scripts

For each migration, create a corresponding rollback script that can undo the changes. This provides a safety net for failed deployments.

Implement version tracking

Use a version or migration tracking table within the database itself to record which migrations have been applied.

Store database code in the application repository

Keep database migrations with the application code to ensure changes are synchronized. This supports the principle that

database changes should be coupled with their corresponding application changes.

Security Note

Never store database credentials, sensitive configuration, or personally identifiable information (PII)

in your version control system. Use environment variables or secure credential management solutions.

1# Basic Flyway migration example
2flyway migrate \\
3  -url=jdbc:postgresql://db.example.com:5432/myapp \\
4  -user=dbuser \\
5  -password=dbpass

1-- V1__Create_customers_table.sql
2CREATE TABLE customers (
3    id SERIAL PRIMARY KEY,
4    name VARCHAR(255) NOT NULL,
5    email VARCHAR(255) UNIQUE NOT NULL,
6    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
7);
8
9-- V2__Add_status_to_customers.sql
10ALTER TABLE customers
11ADD COLUMN status VARCHAR(50) DEFAULT 'active';

1-- Example reference data migration
2INSERT INTO countries (code, name) VALUES
3('US', 'United States'),
4('CA', 'Canada'),
5('GB', 'United Kingdom'),
6('AU', 'Australia')
7ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name;

CI Pipeline Components

A comprehensive database CI pipeline should include several key components to ensure database changes are properly validated before deployment:

Schema Validation

Schema validation ensures that database changes are syntactically correct and follow best practices.

Key validation checks include:

SQL syntax checking

Naming convention validation

Schema policy compliance (e.g., every table must have a primary key)

Index presence on foreign keys

Schema drift detection between environments

Static Analysis

Static analysis tools examine SQL code to identify potential performance issues, security vulnerabilities, and code smells

without executing the code.

Key static analysis checks:

Query performance anti-patterns

SQL injection vulnerabilities

Excessive index usage

Missing WHERE clauses in UPDATE/DELETE statements

Unnecessary table scans or inefficient joins

Hardcoded credentials or sensitive data

Database Unit Testing

Database unit tests verify the behavior of database objects like stored procedures, functions, triggers, and constraints.

Key database unit testing approaches:

: Testing framework for PostgreSQL

: Unit testing framework for SQL Server

: Unit testing framework for Oracle PL/SQL

: Testing framework for MySQL

Custom test frameworks using database-specific features

Integration Testing

Integration tests verify that database changes work correctly with the application code and other components.

Approaches to database integration testing:

Ephemeral test databases

: Spin up a fresh database instance for each test run

Docker-based testing

: Use Docker containers to create isolated test environments

ORM-based testing

: Use application ORM frameworks to test database interactions

: Test database changes through application APIs

Building a Database CI Pipeline

A complete database CI pipeline integrates the validation steps above into your CI/CD system, such as GitHub Actions,

Jenkins, GitLab CI, or Azure DevOps. Here's an example workflow:

Code Commit & PR Creation

Developer commits database changes with corresponding application code changes and creates a pull request.

These changes include schema migrations, stored procedure updates, or reference data modifications.

Syntax Validation & Linting

CI system validates SQL syntax, checks for linting issues, and ensures migration scripts follow naming conventions

and best practices. This step catches basic errors before more extensive testing.

Build Test Database

The pipeline creates an ephemeral test database and applies all migrations, including the new changes.

This validates that migrations can be applied successfully to a clean database.

Static Analysis & Unit Testing

Run static analysis tools to identify potential performance or security issues. Execute unit tests against

database objects to verify their functionality in isolation.

Integration Testing

Run integration tests that verify the application works correctly with the database changes.

This may include ORM-level tests, API tests, or full application tests connecting to the test database.

Drift & Impact Analysis

Check for schema drift between environments and analyze the potential impact of the changes.

This may include identifying breaking changes or performance implications.

Approval & Merge

Once all tests pass, the pull request can be reviewed, approved, and merged.

The database changes are now ready for deployment via the CD pipeline.

1# Example using SQLFluff for SQL linting
2pip install sqlfluff
3sqlfluff lint --dialect postgres migrations/

1# Example GitHub Actions job for schema validation
2jobs:
3  validate-schema:
4    runs-on: ubuntu-latest
5    steps:
6      - uses: actions/checkout@v3
7
8      - name: Set up Flyway
9        run: |
10          wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/8.5.13/flyway-commandline-8.5.13-linux-x64.tar.gz | tar xvz
11          sudo ln -s \`pwd\`/flyway-8.5.13/flyway /usr/local/bin/flyway
12
13      - name: Validate migrations
14        run: flyway validate -url=jdbc:h2:mem:test -locations=filesystem:./migrations

1# Example using pgTAP for PostgreSQL testing
2pgtap-install
3
4# Create test script
5cat > test_constraints.sql << EOL
6BEGIN;
7SELECT plan(3);
8
9SELECT has_pk('customers', 'Table customers should have a primary key');
10SELECT col_not_null('customers', 'email', 'Email column should be NOT NULL');
11SELECT col_is_unique('customers', 'email', 'Email column should be UNIQUE');
12
13SELECT * FROM finish();
14ROLLBACK;
15EOL
16
17# Run the test
18pg_prove -U postgres test_constraints.sql

1-- Example unit test for a stored procedure using pgTAP
2CREATE OR REPLACE FUNCTION test_calculate_discount()
3RETURNS SETOF TEXT AS $$
4BEGIN
5    -- Test basic discount calculation
6    INSERT INTO orders (id, customer_id, total_amount) VALUES (1, 101, 200.00);
7
8    -- Call the stored procedure
9    CALL calculate_order_discount(1);
10
11    -- Assert the result
12    RETURN NEXT is(
13        (SELECT discount_amount FROM orders WHERE id = 1),
14        20.00,
15        'Discount should be 10% of total amount'
16    );
17
18    -- Clean up test data
19    DELETE FROM orders WHERE id = 1;
20END;
21$$ LANGUAGE plpgsql;

1# Example GitHub Actions workflow for database integration testing
2jobs:
3  integration-tests:
4    runs-on: ubuntu-latest
5    services:
6      postgres:
7        image: postgres:15
8        env:
9          POSTGRES_PASSWORD: postgres
10          POSTGRES_USER: postgres
11          POSTGRES_DB: test_db
12        ports:
13          - 5432:5432
14        options: >-
15          --health-cmd pg_isready
16          --health-interval 10s
17          --health-timeout 5s
18          --health-retries 5
19
20    steps:
21      - uses: actions/checkout@v3
22
23      - name: Set up Flyway
24        run: |
25          wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/8.5.13/flyway-commandline-8.5.13-linux-x64.tar.gz | tar xvz
26          sudo ln -s \`pwd\`/flyway-8.5.13/flyway /usr/local/bin/flyway
27
28      - name: Apply migrations
29        run: |
30          flyway migrate -url=jdbc:postgresql://localhost:5432/test_db \\
31            -user=postgres -password=postgres
32
33      - name: Run integration tests
34        run: npm test

1# Example GitHub Actions workflow for database CI
2name: Database CI Pipeline
3
4on:
5  pull_request:
6    paths:
7      - 'db/migrations/**'
8      - 'db/functions/**'
9      - '.github/workflows/db-ci.yml'
10
11jobs:
12  validate-syntax:
13    runs-on: ubuntu-latest
14    steps:
15      - uses: actions/checkout@v3
16      - name: Install SQLFluff
17        run: pip install sqlfluff
18      - name: Lint SQL files
19        run: sqlfluff lint --dialect postgres db/migrations/
20
21  build-test-db:
22    runs-on: ubuntu-latest
23    needs: validate-syntax
24    services:
25      postgres:
26        image: postgres:15
27        env:
28          POSTGRES_PASSWORD: postgres
29          POSTGRES_USER: postgres
30          POSTGRES_DB: test_db
31        ports: ['5432:5432']
32        options: >-
33          --health-cmd pg_isready
34          --health-interval 10s
35          --health-timeout 5s
36          --health-retries 5
37
38    steps:
39      - uses: actions/checkout@v3
40      - name: Set up Flyway
41        run: |
42          wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/8.5.13/flyway-commandline-8.5.13-linux-x64.tar.gz | tar xvz
43          sudo ln -s \`pwd\`/flyway-8.5.13/flyway /usr/local/bin/flyway
44
45      - name: Apply migrations
46        run: |
47          flyway migrate \\
48            -url=jdbc:postgresql://localhost:5432/test_db \\
49            -user=postgres \\
50            -password=postgres \\
51            -locations=filesystem:./db/migrations
52
53  static-analysis:
54    runs-on: ubuntu-latest
55    needs: build-test-db
56    # Static analysis steps here...
57
58  integration-tests:
59    runs-on: ubuntu-latest
60    needs: static-analysis
61    # Integration test steps here...

Database Deployment Strategies

Database deployments require special care due to their stateful nature. Here are several strategies for safely deploying

database changes in a CD pipeline:

Blue-Green Database Deployments

Blue-green database deployment involves maintaining two identical database environments (blue and green) and switching

between them for deployments. This requires a more complex setup but enables near-zero-downtime deployments.

Implementation steps:

Set up two identical database environments (blue and green)

Keep the "blue" environment active while the "green" is idle

Apply schema changes to the "green" environment

Synchronize data from "blue" to "green" using replication or bulk data transfer

Validate the "green" environment

Switch application connections from "blue" to "green"

The old "blue" becomes the new idle environment for the next deployment

Considerations:

Requires additional infrastructure and data synchronization mechanisms

Schema changes must be backward compatible with the current application version

Works well for systems where writes can be paused briefly during cutover

Not suitable for all types of database changes (e.g., major refactoring)

AWS Database Migration Service

PostgreSQL logical replication

GoldenGate for Oracle

Custom sync scripts with bulk operations

Expand and Contract Pattern

The expand and contract pattern (also known as parallel change or strangler fig pattern) allows for zero-downtime schema changes

by making changes in multiple phases that maintain backward compatibility.

Implementation phases:

Expand phase

: Add new structures (tables, columns) alongside existing ones without removing anything

Transition phase

: Update application to write to both old and new structures, but read from old

Transition phase 2

: Migrate existing data to new structures

Transition phase 3

: Update application to write to both, but read from new

Contract phase

: Remove old structures when they're no longer needed

Example: Renaming a column using expand and contract

Phase 1: Expand - Add the new column

Phase 2: Update application code to use new column

Phase 3: Contract - Remove the old column when safe

Enables zero-downtime deployments for schema changes

Supports gradual rollout and rollback capabilities

Minimizes risk by breaking changes into smaller steps

Works with standard database deployment tools

Feature Flags for Database Changes

Feature flags (or feature toggles) can be used to control when database changes are accessed by the application,

allowing for safer deployments and easier rollbacks.

Implementation approaches:

Application-level feature flags

: Control which database schema version the application uses

Database-specific feature flags

: Store feature flags in the database itself

Database views with feature flag logic

: Create views that dynamically change based on feature flag status

Microservice architecture

: Run multiple versions of services with different database schemas

Decouples deployment from feature activation

Allows for gradual rollout and A/B testing

Provides emergency kill-switch for problematic features

Facilitates canary releases and progressive rollouts

Shadow Tables and Dual Writes

Shadow tables involve creating new table versions alongside existing ones and writing to both simultaneously

to validate changes before fully switching over.

Implementation steps:

Create new table versions with updated schema

Implement dual write logic in the application or using database triggers

Compare data and performance between original and shadow tables

Once validated, switch read operations to the new tables

Remove original tables and rename shadow tables

Validates schema changes with real production data

Allows performance comparison between old and new schemas

Reduces risk by validating changes before full cutover

Works well for major structural changes and data migrations

Building a Database CD Pipeline

A robust database continuous deployment pipeline automates the deployment of database changes across environments

while ensuring safety and reliability:

Deployment Planning & Approval

After successful CI, database changes are approved for deployment. This may involve manual approvals

for production deployments or automated approvals for lower environments.

Pre-Deployment Checks

Verify the target environment is ready for deployment, including checking for schema drift,

database connectivity, sufficient storage, and backup status.

Backup & Restore Testing

Create a backup of the target database and verify it can be restored successfully. This provides

a safety net in case the deployment encounters issues.

Deployment Execution

Execute the database changes using a migration tool like Flyway or Liquibase. This should be automated

and idempotent, with detailed logging of all operations.

Post-Deployment Verification

Run validation tests to ensure the database is functioning correctly after deployment. This may include

schema verification, data validation, and smoke tests.

Performance Validation

Monitor database performance after deployment to detect any degradation. This may include query execution times,

resource utilization, and connection pooling metrics.

Deployment Documentation & Notification

Record the deployment details, notify stakeholders of the successful deployment, and update documentation

with any new database features or changes.

Deployment Window Best Practice

For production database deployments, consider implementing deployment windows during low-traffic periods

to minimize impact on users. Always have a clear rollback plan and monitoring in place to quickly detect

and address any issues that arise after deployment.

1-- Add new column without removing the old one
2ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
3
4-- Copy data from old column to new column
5UPDATE users SET email_address = email;
6
7-- Add triggers to keep both columns in sync
8CREATE TRIGGER users_email_sync
9BEFORE INSERT OR UPDATE ON users
10FOR EACH ROW
11EXECUTE FUNCTION sync_email_columns();

1// Update application to read from and write to new column
2// Old code: user.email = '[email protected]';
3// New code: user.emailAddress = '[email protected]';

1-- Remove trigger once all applications are updated
2DROP TRIGGER users_email_sync ON users;
3
4-- Remove old column when no longer needed
5ALTER TABLE users DROP COLUMN email;

1// Example of application-level feature flag for database access
2function getUserData(userId: string) {
3  if (featureFlags.isEnabled('NEW_USER_SCHEMA')) {
4    // Use new schema/tables
5    return db.query('SELECT id, first_name, last_name, email_address FROM users_v2 WHERE id = $1', [userId]);
6  } else {
7    // Use old schema/tables
8    return db.query('SELECT id, name, email FROM users WHERE id = $1', [userId]);
9  }
10}

1-- Create shadow table with new schema
2CREATE TABLE orders_new (
3    id UUID PRIMARY KEY,
4    customer_id UUID NOT NULL REFERENCES customers(id),
5    total_amount DECIMAL(10,2) NOT NULL,
6    status VARCHAR(50) NOT NULL,
7    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
8    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
9);
10
11-- Create trigger for dual writes
12CREATE OR REPLACE FUNCTION sync_orders_insert() RETURNS TRIGGER AS $$
13BEGIN
14    INSERT INTO orders_new (
15        id, customer_id, total_amount, status, created_at, updated_at
16    ) VALUES (
17        NEW.id, NEW.customer_id, NEW.amount, NEW.status, NEW.created_at, NEW.updated_at
18    );
19    RETURN NEW;
20END;
21$$ LANGUAGE plpgsql;
22
23CREATE TRIGGER orders_dual_write
24AFTER INSERT ON orders
25FOR EACH ROW
26EXECUTE FUNCTION sync_orders_insert();

1# Example GitHub Actions workflow for database CD
2name: Database CD Pipeline
3
4on:
5  workflow_dispatch:  # Manual trigger
6  push:
7    branches: [main]  # Auto-deploy from main
8    paths:
9      - 'db/migrations/**'
10
11jobs:
12  deploy-staging:
13    runs-on: ubuntu-latest
14    environment: staging
15    steps:
16      - uses: actions/checkout@v3
17
18      - name: Set up Flyway
19        run: |
20          wget -qO- https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/8.5.13/flyway-commandline-8.5.13-linux-x64.tar.gz | tar xvz
21          sudo ln -s \`pwd\`/flyway-8.5.13/flyway /usr/local/bin/flyway
22
23      - name: Check for schema drift
24        run: |
25          flyway info \\
26            -url=jdbc:postgresql://staging-db.example.com:5432/myapp \\
27            -user=\${{ secrets.DB_USER }} \\
28            -password=\${{ secrets.DB_PASSWORD }}
29
30      - name: Create database backup
31        run: |
32          PGPASSWORD=\${{ secrets.DB_PASSWORD }} pg_dump \\
33            -h staging-db.example.com \\
34            -U \${{ secrets.DB_USER }} \\
35            -d myapp \\
36            -f backup.sql
37
38      - name: Apply database migrations
39        id: migrate
40        run: |
41          flyway migrate \\
42            -url=jdbc:postgresql://staging-db.example.com:5432/myapp \\
43            -user=\${{ secrets.DB_USER }} \\
44            -password=\${{ secrets.DB_PASSWORD }} \\
45            -locations=filesystem:./db/migrations
46
47      - name: Run post-deployment verification
48        run: |
49          # Run validation queries or tests
50          node scripts/validate-db-deployment.js
51
52      - name: Notify team
53        if: steps.migrate.outcome == 'success'
54        run: |
55          curl -X POST \\
56            -H "Content-Type: application/json" \\
57            -d '{"text":"Database migration successfully deployed to staging"}' \\
58            \${{ secrets.SLACK_WEBHOOK_URL }}
59
60  deploy-production:
61    needs: deploy-staging
62    runs-on: ubuntu-latest
63    environment: production
64    # Manual approval required for production
65    # Similar steps to staging deployment
66    # Additional verification and monitoring steps

Comprehensive Database Testing

A complete database testing strategy covers multiple aspects of database changes to ensure reliability, performance,

and data integrity:

Functional Testing

Functional testing verifies that database objects like stored procedures, functions, triggers, and constraints

work as expected. This includes testing with various input values, edge cases, and error scenarios.

Testing approaches:

Black-box testing

: Testing database functionality without knowledge of internal implementation

White-box testing

: Testing with knowledge of the internal implementation

Equivalence partitioning

: Testing representative values from each input class

Boundary value analysis

: Testing at the boundaries of input ranges

Migration Testing

Migration testing verifies that database schema migrations can be applied successfully, idempotently, and

can be rolled back if needed. This type of testing is critical for ensuring smooth deployments.

Key migration tests:

Clean database test

: Apply all migrations to a fresh database

Incremental migration test

: Apply new migrations to an existing database state

Idempotency test

: Verify migrations can be run multiple times without errors

Rollback test

: Verify migrations can be rolled back successfully

Version jumping test

: Test upgrading from much older versions

Performance Testing

Performance testing evaluates how database changes affect query execution time, resource utilization, and overall

system performance. This is especially important for schema changes that might impact indexes or query plans.

Key performance tests:

Benchmark testing

: Compare performance before and after changes

Load testing

: Test database performance under heavy load

Query execution plan analysis

: Verify optimal execution plans are used

Resource utilization testing

: Monitor CPU, memory, and I/O usage

Scalability testing

: Test how performance scales with increased data or connections

Performance testing tools:

: PostgreSQL benchmarking tool

Apache JMeter

: Load testing tool with database support

: Database load testing tool

: Modern load testing tool with database extensions

Security Testing

Security testing evaluates database changes for potential security vulnerabilities, ensuring that

access controls, data protection, and security policies are properly implemented and maintained.

Key security tests:

Authentication testing

: Verify authentication mechanisms work correctly

Authorization testing

: Test role-based access controls (RBAC)

SQL injection testing

: Verify resistance to SQL injection attacks

Sensitive data testing

: Ensure proper protection of sensitive data

Audit testing

: Verify that audit logging is working correctly

Data Testing

Data testing ensures that database changes preserve data integrity, referential integrity, and business rules.

This includes testing constraints, triggers, and validation rules.

Key data tests:

Data migration validation

: Verify data is correctly migrated during schema changes

Constraint testing

: Test unique, foreign key, and check constraints

Business rule validation

: Test business logic implemented in database

Data quality testing

: Verify data meets quality standards

ACID property testing

: Test atomicity, consistency, isolation, and durability

Test Data Management

Effective test data management is essential for reliable database testing. Here are key strategies for

managing test data in your database CI/CD pipeline:

Test Data Generation

Create realistic test data that represents various scenarios and edge cases. Consider using data generation

tools that can create synthetic data matching your schema and constraints.

Faker libraries for generating realistic data

Data anonymization tools for production data

Database-specific generators

Ensure data meets business rules and constraints

Deterministic Test Data

Create test data that produces consistent results across test runs. This helps ensure that test results

are repeatable and reliable.

Set fixed seed values for random data generation

Use hardcoded test data for critical tests

Reset sequences and auto-increments between test runs

Avoid dependencies on current date/time in tests

Test Environments

Create isolated, ephemeral test environments that closely mirror production. This ensures that tests are

representative of real-world conditions.

Containerized test databases (Docker)

Database snapshots or backups for quick restoration

Infrastructure as Code (IaC) for environment creation

Match production settings for realistic testing

Data Subsetting

Create smaller, representative subsets of production data for testing. This reduces resource requirements

while maintaining realistic data patterns.

Extract representative data across all tables

Maintain referential integrity in subsets

Include edge cases and special conditions

Anonymize sensitive information

Test Data Best Practice

Never use actual production data containing personally identifiable information (PII) or sensitive data in test environments

without proper anonymization or pseudonymization. This violates data protection regulations like GDPR and creates unnecessary

security risks. Instead, use data masking, synthetic data generation, or carefully anonymized subsets of production data.

1-- Example: Testing a stored procedure that calculates order discounts
2BEGIN;
3
4-- Set up test data
5INSERT INTO orders (id, customer_id, total_amount)
6VALUES (9001, 5, 100.00);
7
8-- Test case 1: Standard discount (10% for orders between $50 and $200)
9CALL calculate_order_discount(9001);
10SELECT assert_equals(10.00, (SELECT discount_amount FROM orders WHERE id = 9001),
11                    'Standard 10% discount should be applied');
12
13-- Test case 2: Higher discount tier (15% for orders above $200)
14UPDATE orders SET total_amount = 250.00 WHERE id = 9001;
15CALL calculate_order_discount(9001);
16SELECT assert_equals(37.50, (SELECT discount_amount FROM orders WHERE id = 9001),
17                    'Higher tier 15% discount should be applied');
18
19-- Test case 3: No discount (orders under $50)
20UPDATE orders SET total_amount = 45.00 WHERE id = 9001;
21CALL calculate_order_discount(9001);
22SELECT assert_equals(0.00, (SELECT discount_amount FROM orders WHERE id = 9001),
23                    'No discount should be applied for small orders');
24
25-- Clean up test data
26DELETE FROM orders WHERE id = 9001;
27
28ROLLBACK;

1#!/bin/bash
2# Example migration test script
3
4# Set up environment
5PGPASSWORD=postgres
6DB_HOST=localhost
7DB_PORT=5432
8DB_USER=postgres
9DB_NAME=migration_test
10
11# 1. Clean database test
12echo "Running clean database test..."
13dropdb --if-exists -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_NAME
14createdb -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_NAME
15
16flyway -url=jdbc:postgresql://$DB_HOST:$DB_PORT/$DB_NAME \
17       -user=$DB_USER -password=$PGPASSWORD \
18       clean migrate
19
20if [ $? -ne 0 ]; then
21  echo "Clean database migration failed!"
22  exit 1
23fi
24
25# 2. Idempotency test
26echo "Running idempotency test..."
27flyway -url=jdbc:postgresql://$DB_HOST:$DB_PORT/$DB_NAME \
28       -user=$DB_USER -password=$PGPASSWORD \
29       migrate
30
31if [ $? -ne 0 ]; then
32  echo "Idempotency test failed!"
33  exit 1
34fi
35
36# 3. Rollback test
37echo "Running rollback test..."
38latest_version=$(flyway -url=jdbc:postgresql://$DB_HOST:$DB_PORT/$DB_NAME \
39                 -user=$DB_USER -password=$PGPASSWORD \
40                 info -outputType=json | jq -r '.migrations[-1].version')
41
42flyway -url=jdbc:postgresql://$DB_HOST:$DB_PORT/$DB_NAME \
43       -user=$DB_USER -password=$PGPASSWORD \
44       undo -target="\${latest_version}"
45
46if [ $? -ne 0 ]; then
47  echo "Rollback test failed!"
48  exit 1
49fi
50
51echo "All migration tests passed!"

1-- Example: Analyze query execution plan before and after changes
2-- Before change
3EXPLAIN ANALYZE
4SELECT o.id, o.order_date, c.name, o.total_amount
5FROM orders o
6JOIN customers c ON o.customer_id = c.id
7WHERE o.order_date >= '2023-01-01'
8ORDER BY o.order_date DESC
9LIMIT 100;
10
11-- After adding index
12CREATE INDEX idx_orders_order_date ON orders(order_date DESC);
13
14-- After change - should show index scan instead of sequential scan
15EXPLAIN ANALYZE
16SELECT o.id, o.order_date, c.name, o.total_amount
17FROM orders o
18JOIN customers c ON o.customer_id = c.id
19WHERE o.order_date >= '2023-01-01'
20ORDER BY o.order_date DESC
21LIMIT 100;

1-- Example: Testing row-level security policies
2BEGIN;
3
4-- Create test users with different roles
5CREATE ROLE test_manager;
6CREATE ROLE test_employee;
7
8-- Create test data
9CREATE TABLE test_salary (
10    employee_id INT PRIMARY KEY,
11    employee_name TEXT,
12    department TEXT,
13    salary NUMERIC
14);
15
16INSERT INTO test_salary VALUES
17(1, 'Alice', 'Engineering', 90000),
18(2, 'Bob', 'Engineering', 85000),
19(3, 'Charlie', 'Marketing', 75000);
20
21-- Enable row-level security
22ALTER TABLE test_salary ENABLE ROW LEVEL SECURITY;
23
24-- Create policies
25CREATE POLICY manager_policy ON test_salary FOR ALL TO test_manager USING (true);
26CREATE POLICY employee_self_policy ON test_salary FOR SELECT TO test_employee
27    USING (employee_name = current_user);
28
29-- Test manager access
30SET ROLE test_manager;
31SELECT count(*) FROM test_salary;  -- Should return 3
32
33-- Test employee access
34SET ROLE test_employee;
35SELECT count(*) FROM test_salary;  -- Should return 0 (no employee named 'test_employee')
36
37-- Test employee named 'Alice'
38SET ROLE test_employee;
39SELECT count(*) FROM test_salary WHERE employee_name = 'Alice';  -- Should still return 0
40
41-- Create a user matching the data
42CREATE ROLE Alice;
43GRANT test_employee TO Alice;
44SET ROLE Alice;
45SELECT count(*) FROM test_salary;  -- Should return 1 (only Alice's record)
46
47-- Clean up
48RESET ROLE;
49DROP TABLE test_salary;
50DROP ROLE test_manager;
51DROP ROLE test_employee;
52DROP ROLE Alice;
53
54ROLLBACK;

1-- Example: Testing data migration during schema change
2BEGIN;
3
4-- Create original table with data
5CREATE TABLE customers_old (
6    id SERIAL PRIMARY KEY,
7    name VARCHAR(100) NOT NULL,
8    email VARCHAR(100) UNIQUE NOT NULL,
9    phone VARCHAR(20)
10);
11
12INSERT INTO customers_old (name, email, phone) VALUES
13('John Doe', '[email protected]', '555-1234'),
14('Jane Smith', '[email protected]', '555-5678'),
15('Bob Johnson', '[email protected]', NULL);
16
17-- Create new table with modified schema
18CREATE TABLE customers_new (
19    id SERIAL PRIMARY KEY,
20    first_name VARCHAR(50) NOT NULL,
21    last_name VARCHAR(50) NOT NULL,
22    email VARCHAR(100) UNIQUE NOT NULL,
23    phone VARCHAR(20),
24    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
25);
26
27-- Migrate data with transformation
28INSERT INTO customers_new (id, first_name, last_name, email, phone)
29SELECT
30    id,
31    split_part(name, ' ', 1) AS first_name,
32    split_part(name, ' ', 2) AS last_name,
33    email,
34    phone
35FROM customers_old;
36
37-- Validate migration
38SELECT assert_equals(
39    (SELECT count(*) FROM customers_old),
40    (SELECT count(*) FROM customers_new),
41    'Row count should match after migration'
42);
43
44-- Validate specific transformations
45SELECT assert_equals(
46    'John',
47    (SELECT first_name FROM customers_new WHERE email = '[email protected]'),
48    'First name should be correctly split'
49);
50
51SELECT assert_equals(
52    'Doe',
53    (SELECT last_name FROM customers_new WHERE email = '[email protected]'),
54    'Last name should be correctly split'
55);
56
57-- Clean up
58DROP TABLE customers_old;
59DROP TABLE customers_new;
60
61ROLLBACK;

1#!/bin/bash
2# Example script for setting up a test database with seed data
3
4# Configuration
5DB_NAME="test_db"
6DB_USER="postgres"
7DB_PASSWORD="postgres"
8DB_HOST="localhost"
9DB_PORT="5432"
10
11# Create fresh database
12echo "Creating fresh test database..."
13dropdb --if-exists -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_NAME
14createdb -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_NAME
15
16# Apply schema migrations
17echo "Applying migrations..."
18flyway -url=jdbc:postgresql://$DB_HOST:$DB_PORT/$DB_NAME \
19       -user=$DB_USER -password=$DB_PASSWORD \
20       migrate
21
22# Seed with test data
23echo "Seeding test data..."
24PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME << EOF
25-- Reset sequences to ensure deterministic IDs
26ALTER SEQUENCE customers_id_seq RESTART WITH 1;
27ALTER SEQUENCE orders_id_seq RESTART WITH 1;
28ALTER SEQUENCE products_id_seq RESTART WITH 1;
29
30-- Insert test customers
31INSERT INTO customers (name, email, status) VALUES
32('Test Customer 1', '[email protected]', 'active'),
33('Test Customer 2', '[email protected]', 'active'),
34('Test Customer 3', '[email protected]', 'inactive');
35
36-- Insert test products
37INSERT INTO products (name, price, stock) VALUES
38('Test Product A', 99.99, 100),
39('Test Product B', 49.99, 50),
40('Test Product C', 149.99, 25),
41('Test Product D', 19.99, 200);
42
43-- Insert test orders
44INSERT INTO orders (customer_id, order_date, status) VALUES
45(1, '2023-01-15', 'completed'),
46(1, '2023-02-20', 'completed'),
47(2, '2023-02-25', 'processing'),
48(3, '2023-01-10', 'cancelled');
49
50-- Insert order items
51INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
52(1, 1, 2, 99.99),
53(1, 3, 1, 149.99),
54(2, 2, 5, 49.99),
55(3, 4, 10, 19.99),
56(4, 1, 1, 99.99);
57EOF
58
59echo "Test database setup complete!"

Implementing Database DevOps practices and integrating database changes into CI/CD pipelines brings numerous benefits:

Faster, more reliable deployments

: Automate database changes alongside application code

Improved quality

: Catch issues earlier through automated testing and validation

Reduced risk

: Implement safer deployments with proper version control and rollback capabilities

Better collaboration

: Break down silos between development and database administration teams

Consistent environments

: Ensure all environments have the same database schema and reference data

Additional Resources

Database DevOps: The Complete Guide

A comprehensive resource on implementing Database DevOps practices

Sample Database CI/CD Templates

GitHub repository with example CI/CD workflows for database deployments

Database Performance Testing Guide

In-depth guide on performance testing database changes

Ready to Practice?

Head to the interactive lab to practice implementing database CI/CD pipelines with hands-on exercises.

Open Interactive Lab