# ORM vs Query Builder: Prisma vs Knex

# ORM vs Query Builder: Prisma vs Knex - Database Abstraction

## Problem

Modern applications need to interact with databases, but raw SQL queries create several challenges:

- **SQL Injection Vulnerabilities**: String concatenation with user input risks security breaches
- **Database Portability**: Switching databases (PostgreSQL → MySQL) requires rewriting queries
- **Type Safety**: No compile-time validation of query correctness or result shapes
- **Boilerplate Code**: Repetitive connection management, transaction handling, and result mapping
- **Developer Experience**: Manual query construction is error-prone and verbose
- **Maintenance Burden**: Schema changes ripple through codebase without automated refactoring

Two popular solutions exist: **ORMs** (Prisma) and **Query Builders** (Knex). Each addresses these problems differently.

## Solution Overview

### Query Builder Approach (Knex)

**Knex** is a lightweight query builder that generates SQL programmatically:

```javascript
// Knex generates SQL strings dynamically
const users = await knex('users')
  .where('age', '>', 18)
  .select('id', 'name')
  .orderBy('created_at', 'desc');

// Generated SQL: SELECT id, name FROM users WHERE age > 18 ORDER BY created_at DESC
```

**Advantages:**
- Explicit SQL control with programmatic construction
- Minimal abstraction overhead
- Database-agnostic syntax
- Lightweight and flexible
- Great for complex queries

**Disadvantages:**
- No type safety by default
- Manual relationship handling
- No automatic migrations
- Requires manual result mapping

### ORM Approach (Prisma)

**Prisma** is a modern ORM with a declarative schema and type-safe client:

```typescript
// Prisma schema defines structure
model User {
  id    Int     @id @default(autoincrement())
  name  String
  age   Int
  posts Post[]
}

// Type-safe queries with autocomplete
const users = await prisma.user.findMany({
  where: { age: { gt: 18 } },
  select: { id: true, name: true },
  orderBy: { createdAt: 'desc' }
});
```

**Advantages:**
- Full type safety with TypeScript
- Automatic migrations
- Relationship handling built-in
- Excellent developer experience
- Schema as single source of truth

**Disadvantages:**
- Less control over generated SQL
- Steeper learning curve
- Potential performance overhead
- Vendor lock-in concerns

---

## Code Comparison

### Setup & Configuration

**Knex:**
```javascript
// knexfile.js
module.exports = {
  development: {
    client: 'postgresql',
    connection: {
      host: 'localhost',
      user: 'postgres',
      password: 'password',
      database: 'myapp'
    },
    migrations: { directory: './migrations' }
  }
};

// Initialize
const knex = require('knex')(require('./knexfile').development);
```

**Prisma:**
```env
# .env
DATABASE_URL="postgresql://postgres:password@localhost:5432/myapp"
```

```typescript
// prisma/schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}
```

### Basic CRUD Operations

**Knex - Create:**
```javascript
const userId = await knex('users').insert({
  name: 'Alice',
  email: 'alice@example.com',
  age: 28
});
```

**Prisma - Create:**
```typescript
const user = await prisma.user.create({
  data: {
    name: 'Alice',
    email: 'alice@example.com',
    age: 28
  }
});
```

**Knex - Read:**
```javascript
const user = await knex('users')
  .where('id', userId)
  .first();

const allUsers = await knex('users')
  .where('age', '>=', 18)
  .select('*');
```

**Prisma - Read:**
```typescript
const user = await prisma.user.findUnique({
  where: { id: userId }
});

const allUsers = await prisma.user.findMany({
  where: { age: { gte: 18 } }
});
```

**Knex - Update:**
```javascript
await knex('users')
  .where('id', userId)
  .update({ age: 29, updated_at: knex.fn.now() });
```

**Prisma - Update:**
```typescript
const updated = await prisma.user.update({
  where: { id: userId },
  data: { age: 29 }
});
```

**Knex - Delete:**
```javascript
await knex('users').where('id', userId).del();
```

**Prisma - Delete:**
```typescript
await prisma.user.delete({
  where: { id: userId }
});
```

### Relationships & Joins

**Knex - Join:**
```javascript
const userPosts = await knex('users')
  .join('posts', 'users.id', '=', 'posts.user_id')
  .where('users.id', userId)
  .select('users.name', 'posts.title', 'posts.content');
```

**Prisma - Relationship:**
```typescript
// Schema defines relationship
model User {
  id    Int     @id @default(autoincrement())
  name  String
  posts Post[]
}

model Post {
  id      Int     @id @default(autoincrement())
  title   String
  userId  Int
  user    User    @relation(fields: [userId], references: [id])
}

// Query with automatic join
const userWithPosts = await prisma.user.findUnique({
  where: { id: userId },
  include: { posts: true }
});
```

### Complex Queries

**Knex - Aggregation:**
```javascript
const stats = await knex('posts')
  .where('user_id', userId)
  .count('* as total')
  .sum('views as total_views')
  .avg('rating as avg_rating')
  .groupBy('user_id')
  .first();
```

**Prisma - Aggregation:**
```typescript
const stats = await prisma.post.aggregate({
  where: { userId },
  _count: true,
  _sum: { views: true },
  _avg: { rating: true }
});
```

### Transactions

**Knex - Transaction:**
```javascript
const trx = await knex.transaction();

try {
  const user = await trx('users').insert({ name: 'Bob' });
  await trx('audit_log').insert({ 
    action: 'user_created', 
    user_id: user[0] 
  });
  await trx.commit();
} catch (error) {
  await trx.rollback();
  throw error;
}
```

**Prisma - Transaction:**
```typescript
await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { name: 'Bob' }
  });
  
  await tx.auditLog.create({
    data: { action: 'user_created', userId: user.id }
  });
});
```

### Migrations

**Knex - Migration:**
```bash
npx knex migrate:make create_users_table
```

```javascript
// migrations/001_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id');
    table.string('name').notNullable();
    table.string('email').unique();
    table.integer('age');
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};
```

**Prisma - Migration:**
```bash
npx prisma migrate dev --name create_users
```

```prisma
// prisma/schema.prisma
model User {
  id        Int     @id @default(autoincrement())
  name      String
  email     String  @unique
  age       Int?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}
```

### Type Safety Example

**Knex (No Built-in Types):**
```javascript
// No type checking - runtime errors possible
const user = await knex('users').where('id', 1).first();
console.log(user.nonexistent_field); // undefined, no error
```

**Prisma (Full Type Safety):**
```typescript
const user = await prisma.user.findUnique({
  where: { id: 1 }
});

// TypeScript error: Property 'nonexistent_field' does not exist
console.log(user.nonexistent_field);

// Autocomplete and type hints available
console.log(user.name); // ✓ Valid
```

---

## Decision Matrix

| Criterion | Knex | Prisma |
|-----------|------|--------|
| **Type Safety** | Manual/Optional | Built-in ✓ |
| **Learning Curve** | Gentle | Moderate |
| **SQL Control** | High | Medium |
| **Performance** | Excellent | Good |
| **Migrations** | Manual | Automatic ✓ |
| **Relationships** | Manual | Automatic ✓ |
| **Complex Queries** | Excellent | Good |
| **Database Support** | 10+ | 5+ |
| **Community** | Large | Growing ✓ |
| **Best For** | Complex queries, flexibility | Modern apps, type safety |

---

## Recommendation

**Choose Knex if:**
- Building complex reporting queries
- Need maximum database control
- Working with legacy databases
- Prefer lightweight dependencies
- Team comfortable with SQL

**Choose Prisma if:**
- Building modern TypeScript applications
- Want type safety and DX
- Need rapid development
- Prefer declarative schemas
- Team values developer experience

**Hybrid Approach:**
Many projects use both—Prisma for standard CRUD operations and Knex for complex analytical queries:

```typescript
import { PrismaClient } from '@prisma/client';
import knex from 'knex';

const prisma = new PrismaClient();
const db = knex({ client: 'postgresql', connection: process.env.DATABASE_URL });

// Simple operations with Prisma
const user = await prisma.user.findUnique({ where: { id: 1 } });

// Complex analytics with Knex
const report = await db.raw(`
  SELECT user_id, COUNT(*) as post_count, AVG(views) as avg_views
  FROM posts
  GROUP BY user_id
  HAVING COUNT(*) > ?
`, [10]);
```

This pragmatic approach leverages each tool's strengths while maintaining type safety and code clarity.