Kysely Integration

Pure Dev uses Kysely as its query builder, providing a fully type-safe SQL query interface that integrates seamlessly with your Supabase database.

Automatic Type Generation

When you connect to your Supabase database, Pure Dev automatically:

1. Introspects your Supabase database schema
2. Generates TypeScript interfaces for all tables
3. Creates the necessary Kysely type definitions
4. Provides these types to your code editor for autocomplete and type checking

Advanced Querying Examples

Joins with Type Safety

export async function getPostsWithComments(pure: Pure): Promise<PureResponse> {
  const postsWithComments = await pure.db
    .selectFrom("blog.posts as p")
    .leftJoin("blog.comments as c", "c.post_id", "p.id")
    .select([
      "p.id as post_id",
      "p.title",
      "p.content",
      "c.id as comment_id",
      "c.content as comment_content"
    ])
    .where("p.status", "=", "published")
    .execute();
    
  // Transform into nested structure
  const posts = {};
  postsWithComments.forEach(row => {
    if (!posts[row.post_id]) {
      posts[row.post_id] = {
        id: row.post_id,
        title: row.title,
        content: row.content,
        comments: []
      };
    }
    
    if (row.comment_id) {
      posts[row.post_id].comments.push({
        id: row.comment_id,
        content: row.comment_content
      });
    }
  });
  
  return pure.json(Object.values(posts));
}

Complex Filtering

export async function searchPosts(pure: Pure): Promise<PureResponse> {
  const { query, tags, status } = pure.req.queries;
  
  let postsQuery = pure.db
    .selectFrom("blog.posts")
    .selectAll();
  
  // Add text search if provided
  if (query) {
    postsQuery = postsQuery
      .where(eb => eb.or([
        eb("title", "like", `%${query}%`),
        eb("content", "like", `%${query}%`),
        eb("excerpt", "like", `%${query}%`)
      ]));
  }
  
  // Filter by status if provided
  if (status) {
    postsQuery = postsQuery.where("status", "=", status);
  }
  
  // Filter by tags if provided
  if (tags && tags.length > 0) {
    const tagList = tags.split(",");
    
    postsQuery = postsQuery
      .innerJoin(
        "blog.post_tags as pt", 
        "pt.post_id", 
        "blog.posts.id"
      )
      .innerJoin(
        "blog.tags as t",
        "t.id",
        "pt.tag_id"
      )
      .where("t.name", "in", tagList);
  }
  
  const posts = await postsQuery.execute();
  
  return pure.json(posts);
}

Aggregations

export async function handler(pure: Pure): Promise<PureResponse> {
  const stats = await pure.db
    .selectFrom("blog.posts")
    .select([
      (eb) => eb.fn.count("id").as("total_posts"),
      (eb) => eb.fn.avg("blog.post_views.view_count").as("avg_views"),
      "status"
    ])
    .leftJoin(
      "blog.post_views",
      "blog.post_views.post_id",
      "blog.posts.id"
    )
    .groupBy("status")
    .execute();
    
  return pure.json(stats);
}

Development Experience

Working with Supabase in Pure Dev provides an exceptional development experience:

Interactive Query Logs

See exactly what's happening with your database in real-time:

• View generated SQL queries
• Track execution time
• Inspect query results

Schema Validation

Pure Dev helps ensure your code matches your database schema:

• Warnings for missing columns
• Errors for invalid data types
• Suggestions for query optimization (coming soon)
• Feedback on index usage (coming soon)

Best Practices

When working with Supabase in Pure Dev, we recommend:

Use Repository Pattern

export class UsersRepository {
  constructor(private db: Kysely<Database>) {}
  
  async findByEmail(email: string): Selectable<Tables.Users> {
    return this.db
      .selectFrom('users')
      .where('email', '=', email)
      .selectAll()
      .executeTakeFirst();
  }
  
}

// Using the repository
export async function handler(pure: Pure): Promise<PureResponse> {
  const { email } = pure.req.queries;
  const usersRepo = new UsersRepository(db);
  
  const user = await usersRepo.findByEmail(email);
  
  if (!user) {
    return pure.json({ error: 'User not found' }, 404);
  }
  
  return pure.json(user);
}

Implement Error Handling

export async function handler(pure: Pure): Promise<PureResponse> {
  try {
    const { userId } = pure.req.params;
    
    const user = await pure.db
      .selectFrom('users')
      .where('id', '=', userId)
      .selectAll()
      .executeTakeFirst();
    
    if (!user) {
      return pure.json({ error: 'User not found' }, 404);
    }
    
    return pure.json(user);
  } catch (error) {
    // Handle unexpected errors
    return pure.json({ error: error.message }, 500);
  }
}

Use Transactions for Related Operations

export async function createOrderWithItems(pure: Pure): Promise<PureResponse> {
  const { order, items } = pure.req.body;
  
  return await pure.db.transaction().execute(async (trx) => {
    // Create the order
    const [newOrder] = await trx
      .insertInto('orders')
      .values(order)
      .returning('id')
      .execute();
    
    // Add order ID to each item
    const orderItems = items.map(item => ({
      ...item,
      order_id: newOrder.id
    }));
    
    // Insert all items
    await trx
      .insertInto('order_items')
      .values(orderItems)
      .execute();
    
    return pure.json({ 
      message: 'Order created successfully', 
      orderId: newOrder.id 
    }, 201);
  });
}