-- 0000_create_users.sql
CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY NOT NULL,
    "created_at" timestamp DEFAULT now() NOT NULL
);

-- 0001_add_email.sql
ALTER TABLE "users" ADD COLUMN "email" text NOT NULL;

-- 0002_add_email_unique.sql
ALTER TABLE "users" ADD CONSTRAINT "users_email_unique" UNIQUE("email");

-- 0003_add_name.sql
ALTER TABLE "users" ADD COLUMN "name" text;

-- 0004_add_verified.sql
ALTER TABLE "users" ADD COLUMN "email_verified" boolean DEFAULT false NOT NULL;

-- 0005_add_updated_at.sql
ALTER TABLE "users" ADD COLUMN "updated_at" timestamp DEFAULT now() NOT NULL;

-- 0006_add_indexes.sql
CREATE INDEX IF NOT EXISTS "users_email_idx" ON "users" ("email");
CREATE INDEX IF NOT EXISTS "users_created_at_idx" ON "users" ("created_at");

-- 0000_create_posts.sql
CREATE TABLE IF NOT EXISTS "posts" (
    "id" serial PRIMARY KEY NOT NULL,
    "title" text NOT NULL
);

-- 0001_add_author_id.sql
ALTER TABLE "posts" ADD COLUMN "author_id" integer NOT NULL;

-- 0002_add_foreign_key.sql
ALTER TABLE "posts"
ADD CONSTRAINT "posts_author_id_users_id_fk"
FOREIGN KEY ("author_id") REFERENCES "users"("id")
ON DELETE CASCADE ON UPDATE NO ACTION;

-- 0003_create_comments.sql
CREATE TABLE IF NOT EXISTS "comments" (
    "id" serial PRIMARY KEY NOT NULL,
    "content" text NOT NULL
);

-- 0004_add_post_id.sql
ALTER TABLE "comments" ADD COLUMN "post_id" integer NOT NULL;

-- 0005_add_user_id.sql
ALTER TABLE "comments" ADD COLUMN "user_id" integer NOT NULL;

-- 0006_add_comment_post_fk.sql
ALTER TABLE "comments"
ADD CONSTRAINT "comments_post_id_posts_id_fk"
FOREIGN KEY ("post_id") REFERENCES "posts"("id");

-- 0007_add_comment_user_fk.sql
ALTER TABLE "comments"
ADD CONSTRAINT "comments_user_id_users_id_fk"
FOREIGN KEY ("user_id") REFERENCES "users"("id");

-- 0008_add_timestamps.sql
ALTER TABLE "posts" ADD COLUMN "created_at" timestamp DEFAULT now();
ALTER TABLE "comments" ADD COLUMN "created_at" timestamp DEFAULT now();

// Drizzle schema types → SQL patterns preserved

// Numeric types
integer('count')              → integer
serial('id')                  → serial PRIMARY KEY
bigint('amount', { mode: 'number' }) → bigint

// Text types
text('description')           → text
varchar('name', { length: 255 }) → varchar(255)
char('code', { length: 10 }) → char(10)

// Date/time types
timestamp('created_at')       → timestamp
date('birth_date')            → date
time('appointment_time')      → time

// Boolean
boolean('is_active')          → boolean

// JSON
json('metadata')              → json
jsonb('settings')             → jsonb

// All types recognized and consolidated correctly!

-- Enum type consolidation
DO $$ BEGIN
  CREATE TYPE "role" AS ENUM('user', 'admin');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

-- Multiple enum additions optimized
CREATE TYPE "status" AS ENUM('draft', 'published', 'archived');

-- Custom composite types preserved
CREATE TYPE "address" AS (
    street text,
    city text,
    country text
);

-- Before: Scattered across migrations
CREATE INDEX IF NOT EXISTS "idx_users_email" ON "users"("email");
-- ... later ...
CREATE INDEX IF NOT EXISTS "idx_users_name" ON "users"("name");
-- ... later ...
CREATE INDEX IF NOT EXISTS "idx_users_created" ON "users"("created_at");

-- After: Consolidated intelligently
CREATE INDEX IF NOT EXISTS "idx_users_email" ON "users"("email");
CREATE INDEX IF NOT EXISTS "idx_users_name" ON "users"("name");
CREATE INDEX IF NOT EXISTS "idx_users_created" ON "users"("created_at");

-- Partial indexes preserved
CREATE INDEX IF NOT EXISTS "idx_active_users"
ON "users"("email") WHERE "is_active" = true;

# Good: Descriptive names
pnpm drizzle-kit generate:pg --name add_user_email
pnpm drizzle-kit generate:pg --name create_posts_table
pnpm drizzle-kit generate:pg --name add_post_author_relation

# Drizzle auto-generates timestamps
# 0000_add_user_email.sql
# 0001_create_posts_table.sql
# 0002_add_post_author_relation.sql

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './src/schema.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.NEON_DATABASE_URL!,
  },
} satisfies Config;

// CAPYSQUASH optimizes for Neon's connection pooling!

import { drizzle } from 'drizzle-orm/vercel-postgres';
import { sql } from '@vercel/postgres';
import * as schema from './schema';

const db = drizzle(sql, { schema });

// Optimized migrations = faster edge deployments

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const connectionString = process.env.SUPABASE_DATABASE_URL!;
const client = postgres(connectionString);
const db = drizzle(client);

// CAPYSQUASH detects both Drizzle and Supabase patterns!

// schema.ts - Drizzle relations
import { relations } from 'drizzle-orm';
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

// Generated SQL foreign keys are preserved perfectly!

# Pull current database state
pnpm drizzle-kit introspect:pg

# Generate migration to sync
pnpm drizzle-kit generate:pg --name sync_schema

# Then optimize with CAPYSQUASH

// Make sure to use .references()
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
});

// This generates the foreign key constraint

# Launch Drizzle Studio
pnpm drizzle-kit studio

# Opens at: https://local.drizzle.studio
# All tables, relations, and data visible
# No difference from original migrations!