A comprehensive guide to integrating Kysely, a type-safe SQL query builder, with your Next.js application.

What is Kysely?

Kysely is a type-safe TypeScript SQL query builder that provides excellent autocomplete and compile-time type checking for your database queries. Unlike ORMs, it doesn't abstract SQL - it embraces it while adding type safety.

Prerequisites

- Next.js 13+ with App Router

- PostgreSQL database

- Node.js 18+

Installation

Step 1: Install Dependencies

Package breakdown:

- kysely - The query builder

- pg - PostgreSQL driver

- dotenv - Environment variable loader

- @types/pg - TypeScript types for pg

- kysely-codegen - Auto-generate types from your database

Database Setup

Step 2: Create Your Database

Step 3: Configure Environment Variables

Create .env.local in your project root:

Important Notes:

- Use .env.local for local development (not committed to git)

- Use .env for shared defaults (can be committed)

- Never commit sensitive credentials

Special Characters in Password:

If your password contains special characters, URL-encode them:

- @ → %40

- : → %3A

- / → %2F

- # → %23

- ? → %3F

- & → %26

Example:

Project Structure

Implementation

Step 4: Create Database Connection

Create src/lib/db/index.ts:

Step 5: Create Your Database Schema

Create migrations/001_initial.sql:

Apply the migration:

Step 6: Generate TypeScript Types

Add script to package.json:

Generate types:

This creates src/lib/db/types.ts with your database schema as TypeScript types.

Step 7: Using Kysely in Server Actions

Create src/app/actions/users.ts:

Step 8: Using in React Server Components

Step 9: Using in API Routes

Advanced Queries

Joins

Transactions

Conditional Queries

Aggregations

Subqueries

Migration System (Optional)

Create Migration Runner

Create src/lib/db/migrator.ts:

Create TypeScript Migrations

Create src/lib/db/migrations/001_create_organizations.ts:

Add Migration Scripts

Update package.json:

Run migrations:

Common Issues & Solutions

Issue: "tsx not found"

Solution: Use npx or install globally

Issue: "SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string"

Solutions:

1. Check password is not empty

2. URL-encode special characters in password

3. Remove quotes from DATABASE_URL

4. Ensure no whitespace around = in .env file

Issue: "Cannot find module './types'"

Solution: Generate types first

Issue: ".env not loading"

Solution: Import dotenv at the top of your file

Issue: "Module not found" when importing db

Solution: Check your TypeScript paths in tsconfig.json

Best Practices

1. Always Use Transactions for Multiple Operations

2. Use returningAll() for Insert/Update Operations

3. Handle Errors Gracefully

4. Use Prepared Statements for Repeated Queries

5. Close Database Connections Properly

6. Use Type-Safe Column Selection

7. Regenerate Types After Schema Changes

Performance Tips

1. Use Indexes

2. Limit Result Sets

3. Select Only Required Columns

4. Use Connection Pooling

Already configured in our setup with max: 10 connections.

Testing

Setup Test Database

Resources

- Kysely Documentation

- Kysely GitHub

- Next.js Documentation

- PostgreSQL Documentation

Conclusion

Kysely provides type-safe database queries while keeping you close to SQL. It's an excellent choice for Next.js applications where you want:

- Full type safety

- SQL control

- Great TypeScript autocomplete

- No ORM magic

- Performance optimization

Happy querying! 🚀