Database Integration

Pure Dev provides seamless integration with Supabase for database management, combined with Kysely for type-safe query building. This integration offers automatic type generation, real-time query logging, and a powerful development experience.

Overview

Database Navigator

The Database Navigator provides:

  • Visual schema exploration
  • Real-time query logging
  • Type generation status
  • Connection management
  • Database health status

Supabase Integration

Connection Setup

You can connect to Supabase in two ways:

  1. Connect Existing Database

    • Access through SSO authentication
    • Select your existing Supabase project
    • Choose schemas to import (all schemas are imported by default)

  2. Create New Database

    • Create directly from Pure Dev interface
    • Wait for provisioning (may take a few minutes)
    • Reconnect using "existing database" option once created
    • Select your newly created database

Schema Management

  • Configure which schemas to import in the settings navigation
  • All schemas are imported by default
  • Types are automatically generated for selected schemas
  • Real-time schema synchronization

Database Health

  • Connection status indicator in Database Navigator
  • If database is down, check Supabase dashboard
  • Database management should be done through Supabase
  • Pure Dev won't automatically restart your database

Query Engine Setup

Pure Dev handles all the database configuration:

  • Automatic Kysely driver setup
  • Connection pool management
  • Type generation and synchronization
  • Query logging and monitoring

Type-Safe Queries with Kysely

Pure Dev uses Kysely as its query builder, providing:

  • Full TypeScript support
  • Automatic type inference
  • SQL-like query syntax
  • Protection against SQL injection

Basic Query Examples

export async function handler(pure: Pure): Promise<PureResponse> {
  // Select all users
  const users = await pure.db
    .selectFrom('users')
    .selectAll()
    .execute()

  // Insert a new user
  const newUser = await pure.db
    .insertInto('users')
    .values({
      name: 'John Doe',
      email: '[email protected]'
    })
    .returningAll()
    .executeTakeFirstOrThrow()

  // Update with type safety
  const updated = await pure.db
    .updateTable('users')
    .set({ status: 'active' })
    .where('id', '=', userId)
    .returningAll()
    .execute()

  // Join with type inference
  const posts = await pure.db
    .selectFrom('posts')
    .innerJoin('users', 'users.id', 'posts.user_id')
    .select(['posts.id', 'posts.title', 'users.name'])
    .execute()
}

Transaction Support

export async function handler(pure: Pure): Promise<PureResponse> {
  const result = await pure.db.transaction().execute(async (trx) => {
    const user = await trx
      .insertInto('users')
      .values({ name: 'John' })
      .returningAll()
      .executeTakeFirstOrThrow()

    const post = await trx
      .insertInto('posts')
      .values({ 
        user_id: user.id,
        title: 'My First Post'
      })
      .returningAll()
      .executeTakeFirstOrThrow()

    return { user, post }
  })
}

Complex Queries

export async function handler(pure: Pure): Promise<PureResponse> {
  const result = await pure.db
    .selectFrom('users')
    .leftJoin('posts', 'posts.user_id', 'users.id')
    .select(({ fn }) => [
      'users.id',
      'users.name',
      fn.count('posts.id').as('post_count'),
    ])
    .groupBy('users.id')
    .having(({ fn }) => fn.count('posts.id'), '>', 5)
    .execute()
}

Best Practices

  1. Type Safety

    • Let TypeScript infer return types
    • Use executeTakeFirstOrThrow() when expecting one result
    • Leverage Kysely's type system for joins

  2. Performance

    • Use appropriate indexes
    • Monitor query performance in real-time
    • Use transactions for related operations

  3. Error Handling

    • Handle database errors gracefully
    • Use appropriate status codes
    • Provide meaningful error messages

  4. Security

    • Never trust user input
    • Use parameterized queries (automatic with Kysely)
    • Implement proper access control

Common Patterns

Pagination

export async function handler(pure: Pure): Promise<PureResponse> {
  const { page = 1, limit = 10 } = pure.req.queries
  const offset = (page - 1) * limit

  const users = await pure.db
    .selectFrom('users')
    .selectAll()
    .limit(limit)
    .offset(offset)
    .execute()

  const [{ count }] = await pure.db
    .selectFrom('users')
    .select(({ fn }) => [fn.count<string>('id').as('count')])
    .execute()

  return pure.json({
    data: users,
    pagination: {
      page,
      limit,
      total: Number(count)
    }
  })
}

Search and Filter

export async function handler(pure: Pure): Promise<PureResponse> {
  const { search, status } = pure.req.queries

  let query = pure.db
    .selectFrom('users')
    .selectAll()

  if (search) {
    query = query.where('name', 'ilike', `%${search}%`)
  }

  if (status) {
    query = query.where('status', '=', status)
  }

  return pure.json({
    data: await query.execute()
  })
}

Next Steps