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](https://kysely.dev)

- [Kysely GitHub](https://github.com/kysely-org/kysely)

- [Next.js Documentation](https://nextjs.org/docs)

- [PostgreSQL Documentation](https://www.postgresql.org/docs/)

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! 🚀