codesnips
sql 270 lines · 2 tabs

Database schema migrations and versioning

Maria Garcia Feb 2026
2 tabs 
-- Migration naming convention: V{version}__{description}.sql
-- Example: V001__create_users_table.sql

-- Migration 1: Create initial schema
-- V001__create_users_table.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

-- Migration 2: Add column (backwards compatible)
-- V002__add_users_status.sql
ALTER TABLE users
  ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Migration 3: Populate new column
-- V003__populate_users_status.sql
UPDATE users
SET status = 'active'
WHERE status IS NULL;

-- Migration 4: Add NOT NULL constraint
-- V004__make_status_not_null.sql
ALTER TABLE users
  ALTER COLUMN status SET NOT NULL;

-- Safe column rename (multi-step for zero downtime)

-- Step 1: Add new column
-- V005__add_users_full_name.sql
ALTER TABLE users
  ADD COLUMN full_name VARCHAR(100);

-- Step 2: Copy data
-- V006__populate_full_name.sql
UPDATE users
SET full_name = username
WHERE full_name IS NULL;

-- Step 3: Update application to use full_name
-- Deploy application code

-- Step 4: Drop old column
-- V007__drop_users_username.sql
ALTER TABLE users
  DROP COLUMN username;

-- Idempotent migration (safe to run multiple times)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'users' AND column_name = 'phone'
  ) THEN
    ALTER TABLE users ADD COLUMN phone VARCHAR(20);
  END IF;
END $$;

-- Conditional index creation
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);

-- Transaction-wrapped migration
BEGIN;

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  total DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

COMMIT;
-- If any statement fails, all changes are rolled back

-- Data migration with batching (for large tables)
DO $$
DECLARE
  batch_size INT := 1000;
  affected_rows INT;
BEGIN
  LOOP
    UPDATE users
    SET status = 'verified'
    WHERE id IN (
      SELECT id FROM users
      WHERE status = 'active' AND email_verified = true
      LIMIT batch_size
    );

    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    EXIT WHEN affected_rows = 0;

    -- Commit batch and pause
    COMMIT;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

-- Rollback migration (down migration)
-- V002__add_users_status__rollback.sql
ALTER TABLE users
  DROP COLUMN status;

-- Check migration status
-- Flyway command: flyway info
-- Shows: version, description, type, installed on, state

-- Schema version table (Flyway creates this)
SELECT * FROM flyway_schema_history
ORDER BY installed_rank;
2 files · sql Explain with highlit

Schema migrations evolve database structure safely. I use migration tools like Flyway, Liquibase, or framework migrations. Version-controlled migrations track schema changes. Up migrations apply changes, down migrations revert. Idempotent migrations can run multiple times safely. I avoid destructive changes—rename, don't drop. Backwards-compatible migrations enable zero-downtime deploys. Multi-step migrations: add column, deploy code, backfill data, add constraint. Transaction-wrapped migrations ensure atomicity. Understanding migration order prevents dependency issues. Blue-green deployments need schema compatibility. Testing migrations in staging catches errors. Migration rollback plans minimize downtime. Proper migration strategy enables continuous database evolution without production incidents.

Related snips

sql 
-- Simple function
CREATE OR REPLACE FUNCTION get_full_name(
  first_name VARCHAR,
  last_name VARCHAR
)
RETURNS VARCHAR AS $$

Stored procedures and functions in PostgreSQL

postgresql stored-procedures functions
by Maria Garcia 2 tabs
ruby 
# Vulnerable: user input is concatenated directly into SQL.
email = params[:email]
password = params[:password]

sql = "SELECT * FROM users WHERE email = '#{email}' AND password_hash = '#{password}'"
user = ActiveRecord::Base.connection.execute(sql).first

SQL injection prevention with unsafe and safe query patterns

sql-injection owasp database
by Kai Nakamura 3 tabs
ruby 
class AddSettingsToAccounts < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!

  def change
    add_column :accounts, :settings, :jsonb, null: false, default: {}

Postgres JSONB Partial Index for Feature Flags

rails postgres jsonb
by codesnips 3 tabs
sql 
-- EXPLAIN ANALYZE (actual execution statistics)
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'

Advanced query optimization techniques

database optimization query-performance
by Maria Garcia 2 tabs
python 
from django.db.models import Count, Avg, Sum, Q, F
from django.views.generic import TemplateView
from products.models import Product, Order, OrderItem


class DashboardView(TemplateView):

Django aggregation with annotate for statistics

django python database
by Priya Sharma 1 tab
ruby 
class ReportQuery
  SQL = <<~SQL.freeze
    SELECT date_trunc('day', events.created_at) AS day,
           count(*) AS total,
           count(*) FILTER (WHERE events.kind = 'purchase') AS purchases
    FROM events

Safe Raw SQL with exec_query + Binds

rails activerecord sql
by codesnips 2 tabs

Share this code

Here's the card — post it anywhere.

Database schema migrations and versioning — share card
Link copied