Kysely Type-Safe SQL Queries

# Kysely Type-Safe SQL Queries for Google Antigravity

Master type-safe SQL queries with Kysely in your Google Antigravity IDE projects. This comprehensive guide covers query building, transactions, migrations, and advanced patterns optimized for Gemini 3 agentic development.

## Database Types

Define your database types for full type inference:

```typescript
// db/types.ts
import { Generated, Insertable, Selectable, Updateable } from 'kysely';

export interface Database {
  users: UsersTable;
  prompts: PromptsTable;
  stars: StarsTable;
  comments: CommentsTable;
}

interface UsersTable {
  id: Generated<string>;
  email: string;
  name: string | null;
  image: string | null;
  role: 'user' | 'admin' | 'moderator';
  subscription_tier: string | null;
  created_at: Generated<Date>;
  updated_at: Generated<Date>;
}

interface PromptsTable {
  id: Generated<string>;
  slug: string;
  title: string;
  description: string;
  content: string;
  tags: string[];
  author_id: string | null;
  is_approved: Generated<boolean>;
  view_count: Generated<number>;
  star_count: Generated<number>;
  created_at: Generated<Date>;
  updated_at: Generated<Date>;
}

interface StarsTable {
  user_id: string;
  prompt_id: string;
  created_at: Generated<Date>;
}

interface CommentsTable {
  id: Generated<string>;
  prompt_id: string;
  user_id: string;
  content: string;
  parent_id: string | null;
  created_at: Generated<Date>;
  updated_at: Generated<Date>;
}

// Type helpers
export type User = Selectable<UsersTable>;
export type NewUser = Insertable<UsersTable>;
export type UserUpdate = Updateable<UsersTable>;

export type Prompt = Selectable<PromptsTable>;
export type NewPrompt = Insertable<PromptsTable>;
export type PromptUpdate = Updateable<PromptsTable>;
```

## Kysely Configuration

Set up Kysely with PostgreSQL:

```typescript
// db/index.ts
import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';
import { Database } from './types';

const dialect = new PostgresDialect({
  pool: new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 10,
  }),
});

export const db = new Kysely<Database>({
  dialect,
  log: process.env.NODE_ENV === 'development' ? ['query', 'error'] : ['error'],
});

// For serverless environments
import { NeonDialect } from 'kysely-neon';
import { neon } from '@neondatabase/serverless';

export const dbServerless = new Kysely<Database>({
  dialect: new NeonDialect({
    connectionString: process.env.DATABASE_URL!,
  }),
});
```

## Query Patterns

Build type-safe queries with full autocompletion:

```typescript
// lib/queries/prompts.ts
import { db } from '@/db';
import { sql, ExpressionBuilder } from 'kysely';
import { Database } from '@/db/types';

// Simple select query
export async function getPromptBySlug(slug: string) {
  return db
    .selectFrom('prompts')
    .where('slug', '=', slug)
    .where('is_approved', '=', true)
    .selectAll()
    .executeTakeFirst();
}

// Join query with type-safe columns
export async function getPromptWithAuthor(slug: string) {
  return db
    .selectFrom('prompts')
    .innerJoin('users', 'users.id', 'prompts.author_id')
    .where('prompts.slug', '=', slug)
    .select([
      'prompts.id',
      'prompts.slug',
      'prompts.title',
      'prompts.description',
      'prompts.content',
      'prompts.tags',
      'prompts.view_count',
      'prompts.star_count',
      'prompts.created_at',
      'users.id as author_id',
      'users.name as author_name',
      'users.image as author_image',
    ])
    .executeTakeFirst();
}

// Dynamic query building
type PromptFilters = {
  search?: string;
  tags?: string[];
  authorId?: string;
  isApproved?: boolean;
};

export async function searchPrompts(filters: PromptFilters, pagination: { page: number; limit: number }) {
  let query = db
    .selectFrom('prompts')
    .leftJoin('users', 'users.id', 'prompts.author_id')
    .select([
      'prompts.id',
      'prompts.slug',
      'prompts.title',
      'prompts.description',
      'prompts.tags',
      'prompts.star_count',
      'prompts.view_count',
      'users.name as author_name',
    ]);

  // Apply filters dynamically
  if (filters.search) {
    query = query.where((eb) =>
      eb.or([
        eb('prompts.title', 'ilike', `%${filters.search}%`),
        eb('prompts.description', 'ilike', `%${filters.search}%`),
      ])
    );
  }

  if (filters.tags && filters.tags.length > 0) {
    query = query.where(
      sql`prompts.tags && ${sql.literal(filters.tags)}::text[]`
    );
  }

  if (filters.authorId) {
    query = query.where('prompts.author_id', '=', filters.authorId);
  }

  if (filters.isApproved !== undefined) {
    query = query.where('prompts.is_approved', '=', filters.isApproved);
  }

  const offset = (pagination.page - 1) * pagination.limit;

  const [results, countResult] = await Promise.all([
    query
      .orderBy('prompts.created_at', 'desc')
      .limit(pagination.limit)
      .offset(offset)
      .execute(),
    db
      .selectFrom('prompts')
      .select(sql<number>`count(*)::int`.as('count'))
      .executeTakeFirst(),
  ]);

  return {
    prompts: results,
    total: countResult?.count ?? 0,
    page: pagination.page,
    limit: pagination.limit,
    totalPages: Math.ceil((countResult?.count ?? 0) / pagination.limit),
  };
}

// Aggregate queries
export async function getPromptStats(promptId: string) {
  return db
    .selectFrom('prompts')
    .where('id', '=', promptId)
    .select((eb) => [
      'id',
      'title',
      'view_count',
      'star_count',
      eb
        .selectFrom('comments')
        .whereRef('comments.prompt_id', '=', 'prompts.id')
        .select(sql<number>`count(*)::int`.as('count'))
        .as('comment_count'),
    ])
    .executeTakeFirst();
}
```

## Transactions

Handle complex operations with transactions:

```typescript
// lib/mutations/prompts.ts
import { db } from '@/db';
import { sql } from 'kysely';

export async function starPrompt(userId: string, promptId: string) {
  return db.transaction().execute(async (trx) => {
    // Check if already starred
    const existing = await trx
      .selectFrom('stars')
      .where('user_id', '=', userId)
      .where('prompt_id', '=', promptId)
      .selectAll()
      .executeTakeFirst();

    if (existing) {
      // Remove star
      await trx
        .deleteFrom('stars')
        .where('user_id', '=', userId)
        .where('prompt_id', '=', promptId)
        .execute();

      await trx
        .updateTable('prompts')
        .set({ star_count: sql`star_count - 1` })
        .where('id', '=', promptId)
        .execute();

      return { starred: false };
    }

    // Add star
    await trx
      .insertInto('stars')
      .values({ user_id: userId, prompt_id: promptId })
      .execute();

    await trx
      .updateTable('prompts')
      .set({ star_count: sql`star_count + 1` })
      .where('id', '=', promptId)
      .execute();

    return { starred: true };
  });
}

export async function createPromptWithTags(
  data: NewPrompt,
  userId: string
) {
  return db.transaction().execute(async (trx) => {
    // Insert prompt
    const prompt = await trx
      .insertInto('prompts')
      .values({
        ...data,
        author_id: userId,
        slug: generateSlug(data.title),
      })
      .returningAll()
      .executeTakeFirstOrThrow();

    // Update user prompt count
    await trx
      .updateTable('users')
      .set({ prompt_count: sql`prompt_count + 1` })
      .where('id', '=', userId)
      .execute();

    return prompt;
  });
}
```

## Migrations

Create type-safe migrations:

```typescript
// migrations/001_initial.ts
import { Kysely, sql } from 'kysely';

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('users')
    .addColumn('id', 'uuid', (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`)
    )
    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('name', 'varchar(255)')
    .addColumn('image', 'text')
    .addColumn('role', 'varchar(50)', (col) => col.notNull().defaultTo('user'))
    .addColumn('created_at', 'timestamptz', (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .addColumn('updated_at', 'timestamptz', (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .execute();

  await db.schema
    .createTable('prompts')
    .addColumn('id', 'uuid', (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`)
    )
    .addColumn('slug', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('title', 'varchar(255)', (col) => col.notNull())
    .addColumn('description', 'text', (col) => col.notNull())
    .addColumn('content', 'text', (col) => col.notNull())
    .addColumn('tags', sql`text[]`, (col) => col.notNull().defaultTo(sql`'{}'`))
    .addColumn('author_id', 'uuid', (col) =>
      col.references('users.id').onDelete('set null')
    )
    .addColumn('is_approved', 'boolean', (col) => col.notNull().defaultTo(false))
    .addColumn('view_count', 'integer', (col) => col.notNull().defaultTo(0))
    .addColumn('star_count', 'integer', (col) => col.notNull().defaultTo(0))
    .addColumn('created_at', 'timestamptz', (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .addColumn('updated_at', 'timestamptz', (col) =>
      col.notNull().defaultTo(sql`now()`)
    )
    .execute();

  // Create indexes
  await db.schema
    .createIndex('prompts_slug_idx')
    .on('prompts')
    .column('slug')
    .execute();

  await db.schema
    .createIndex('prompts_author_idx')
    .on('prompts')
    .column('author_id')
    .execute();

  await db.schema
    .createIndex('prompts_tags_idx')
    .on('prompts')
    .using('gin')
    .column('tags')
    .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('prompts').execute();
  await db.schema.dropTable('users').execute();
}
```

## Best Practices

1. **Define database types** for full type inference
2. **Use transactions** for multi-step operations
3. **Create reusable query builders** for common patterns
4. **Leverage dynamic query building** for filters
5. **Use migrations** for schema changes
6. **Add proper indexes** for query performance
7. **Handle errors gracefully** with try-catch