A comprehensive benchmark comparing different Drizzle ORM query patterns and strategies using Deno and SQLite via the libSQL driver.

This script benchmarks Drizzle ORM performance through 5 focused comparison groups:

Compare Relational Query API vs Select with Joins vs Raw SQL (same data volume, all columns)

Test the performance impact of selecting all columns vs IDs only vs selected columns in relational queries

Measure the cost of transferring large text fields (with/without content field)

Test how row count affects performance (100, 1K, 10K, 50K rows)

Compare single triple-join vs two-step queries vs subquery approaches

The benchmark uses a simple three-table schema:

• users (id, name, email)
• posts (id, userId, title, content)
• comments (id, postId, userId, content)

Default data: 10,000 users, 100,000 posts (10 per user), 500,000 comments (5 per post)

Deno will automatically fetch dependencies and populate node_modules on first run.

To manually install dependencies:

deno install

Run the benchmark (generates migrations and runs benchmark):

deno task benchmark

Or run individual steps:

# Generate migrations from schema (do this after schema changes)
deno task db:generate

# Run benchmark only (assumes migrations already generated)
deno task benchmark:only

# Apply migrations to a database (standalone)
deno task db:migrate

The script will:

1. Create a fresh SQLite database using libSQL
2. Seed it with test data
3. Run each query strategy 10 times
4. Display a formatted table with results including:
   • Average execution time
   • Min/Max times
   • Winner and performance comparison

The benchmark displays results in separate tables for each comparison group:

🚀 Drizzle Query Benchmark - Deno + SQLite (libSQL)

📦 Initializing database...
Seeding database: 10000 users, 10 posts/user, 5 comments/post...
✓ Database seeded: 10000 users, 100000 posts, 500000 comments

⏱️  Running benchmarks (10 runs each)...

================================================================================
BENCHMARK RESULTS
================================================================================

────────────────────────────────────────────────────────────────────────────────
📊 GROUP 1: Query Strategy Comparison
   Same data volume (150 rows), all columns - Relational vs Joins vs Raw SQL
────────────────────────────────────────────────────────────────────────────────
Test                                           Avg (ms)   Min (ms)   Max (ms)
--------------------------------------------------------------------------------
1c. Raw SQL (All Columns)                         5.23       4.98       6.12
1b. Select with Joins (All Columns)               6.45       6.21       7.23
1a. Relational Query (All Columns)                7.89       7.45       8.67
--------------------------------------------------------------------------------
⚡ Fastest: 1c. Raw SQL (All Columns) (5.23ms)
🐌 Slowest: 1a. Relational Query (All Columns) (7.89ms)
📈 Difference: 1.51x (50.9% slower)

────────────────────────────────────────────────────────────────────────────────
📊 GROUP 2: Column Selection Impact
   Same query (1000 rows) - All columns vs IDs only vs Selected columns
────────────────────────────────────────────────────────────────────────────────
Test                                           Avg (ms)   Min (ms)   Max (ms)
--------------------------------------------------------------------------------
2b. Relational: IDs Only (1000 rows)             12.34      11.89      13.45
2c. Relational: Selected Columns (1000 rows)     18.67      17.89      20.12
2a. Relational: All Columns (1000 rows)          25.89      24.56      28.34
--------------------------------------------------------------------------------
⚡ Fastest: 2b. Relational: IDs Only (1000 rows) (12.34ms)
🐌 Slowest: 2a. Relational: All Columns (1000 rows) (25.89ms)
📈 Difference: 2.10x (109.8% slower)

... (3 more groups)

================================================================================

✨ Benchmark complete!

Each group shows:

• Clear comparison context
• Sorted results (fastest to slowest)
• Performance difference within the group
• Percentage slower for context

You can modify these constants in benchmark.ts:

• BENCHMARK_RUNS: Number of iterations for each test (default: 10)
• USER_COUNT: Number of users to create (default: 10,000)
• POSTS_PER_USER: Posts per user (default: 10)
• COMMENTS_PER_POST: Comments per post (default: 5)

Note: The larger dataset (500k comments) provides more realistic performance measurements and makes optimization differences more apparent.

• benchmark.ts - Main benchmark script with all query strategies
• schema.ts - Drizzle schema definitions with indexes and relations
• utils.ts - Helper functions for benchmarking and seeding (uses drizzle-seed)
• migrate.ts - Database migration runner (optional standalone use)
• drizzle.config.ts - Drizzle Kit configuration
• drizzle/ - Generated SQL migrations (checked into git)
• deno.json - Deno configuration and dependencies
• .gitignore - Excludes database files and migration metadata from git

• The database is recreated on each run for consistent results
• A warm-up run is performed before each benchmark
• Indexes are defined in the schema and applied via migrations
• Results may vary based on system performance
• Migration SQL files are checked into git for reproducibility
• The deno task benchmark command automatically runs db:generate first
• Uses drizzle-seed for deterministic, realistic fake data generation