astro-db-patterns

What this skill does

# Astro DB Patterns Skill

Common patterns and examples for using Astro DB, including schema design, queries, and data seeding.

## When to Use This Skill

- Setting up Astro DB in a project
- Designing database schemas
- Writing queries with Drizzle ORM
- Seeding development data
- Building API endpoints with database access

## Database Setup

### Install Astro DB

```bash
npx astro add db
```

### Configuration File

Create `db/config.ts`:

```typescript
import { defineDb, defineTable, column } from 'astro:db';

const MyTable = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    // ... other columns
  }
});

export default defineDb({
  tables: { MyTable }
});
```

## Column Types

### Text Columns

```typescript
// Basic text
name: column.text()

// Unique text
email: column.text({ unique: true })

// Optional text
bio: column.text({ optional: true })

// Text with default
status: column.text({ default: 'active' })
```

### Number Columns

```typescript
// Basic number
age: column.number()

// Primary key
id: column.number({ primaryKey: true })

// Optional number
rating: column.number({ optional: true })

// Number with default
views: column.number({ default: 0 })

// Unique number
userId: column.number({ unique: true })
```

### Boolean Columns

```typescript
// Basic boolean
published: column.boolean()

// Boolean with default
active: column.boolean({ default: true })
featured: column.boolean({ default: false })

// Optional boolean
verified: column.boolean({ optional: true })
```

### Date Columns

```typescript
// Basic date
createdAt: column.date()

// Optional date
publishedAt: column.date({ optional: true })

// Date with default (requires runtime value)
updatedAt: column.date({ default: new Date() })
```

### JSON Columns

```typescript
// JSON data
metadata: column.json()

// JSON with default
settings: column.json({ default: {} })
options: column.json({ default: [] })

// Optional JSON
extra: column.json({ optional: true })
```

## Common Schema Patterns

### Blog Database

```typescript
import { defineDb, defineTable, column } from 'astro:db';

const Author = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text(),
    email: column.text({ unique: true }),
    bio: column.text({ optional: true }),
    avatar: column.text({ optional: true }),
    website: column.text({ optional: true }),
    createdAt: column.date()
  }
});

const Post = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    title: column.text(),
    slug: column.text({ unique: true }),
    content: column.text(),
    excerpt: column.text({ optional: true }),
    published: column.boolean({ default: false }),
    publishedAt: column.date({ optional: true }),
    updatedAt: column.date({ optional: true }),
    authorId: column.number(),
    views: column.number({ default: 0 }),
    featured: column.boolean({ default: false })
  }
});

const Tag = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text({ unique: true }),
    slug: column.text({ unique: true })
  }
});

const PostTag = defineTable({
  columns: {
    postId: column.number(),
    tagId: column.number()
  }
});

const Comment = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    postId: column.number(),
    author: column.text(),
    email: column.text(),
    content: column.text(),
    approved: column.boolean({ default: false }),
    createdAt: column.date()
  }
});

export default defineDb({
  tables: { Author, Post, Tag, PostTag, Comment }
});
```

### E-commerce Database

```typescript
const Product = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text(),
    slug: column.text({ unique: true }),
    description: column.text(),
    price: column.number(),
    salePrice: column.number({ optional: true }),
    sku: column.text({ unique: true }),
    inStock: column.boolean({ default: true }),
    quantity: column.number({ default: 0 }),
    categoryId: column.number(),
    images: column.json({ default: [] }),
    createdAt: column.date()
  }
});

const Category = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text(),
    slug: column.text({ unique: true }),
    description: column.text({ optional: true }),
    parentId: column.number({ optional: true })
  }
});

const Order = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    orderNumber: column.text({ unique: true }),
    customerId: column.number(),
    total: column.number(),
    status: column.text({ default: 'pending' }),
    createdAt: column.date(),
    completedAt: column.date({ optional: true })
  }
});

const OrderItem = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    orderId: column.number(),
    productId: column.number(),
    quantity: column.number(),
    price: column.number()
  }
});

export default defineDb({
  tables: { Product, Category, Order, OrderItem }
});
```

### User Management Database

```typescript
const User = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    username: column.text({ unique: true }),
    email: column.text({ unique: true }),
    passwordHash: column.text(),
    firstName: column.text(),
    lastName: column.text(),
    role: column.text({ default: 'user' }),
    active: column.boolean({ default: true }),
    emailVerified: column.boolean({ default: false }),
    lastLogin: column.date({ optional: true }),
    createdAt: column.date()
  }
});

const Session = defineTable({
  columns: {
    id: column.text({ primaryKey: true }),
    userId: column.number(),
    expiresAt: column.date(),
    createdAt: column.date()
  }
});

const UserProfile = defineTable({
  columns: {
    userId: column.number({ unique: true }),
    bio: column.text({ optional: true }),
    avatar: column.text({ optional: true }),
    location: column.text({ optional: true }),
    website: column.text({ optional: true }),
    social: column.json({ default: {} })
  }
});

export default defineDb({
  tables: { User, Session, UserProfile }
});
```

## Data Seeding

### Basic Seed File

Create `db/seed.ts`:

```typescript
import { db, Author, Post, Tag } from 'astro:db';

export default async function seed() {
  // Insert authors
  await db.insert(Author).values([
    {
      id: 1,
      name: 'John Doe',
      email: '[email protected]',
      bio: 'Tech enthusiast and blogger',
      createdAt: new Date('2024-01-01')
    },
    {
      id: 2,
      name: 'Jane Smith',
      email: '[email protected]',
      bio: 'Software developer',
      createdAt: new Date('2024-01-01')
    }
  ]);

  // Insert tags
  await db.insert(Tag).values([
    { id: 1, name: 'Astro', slug: 'astro' },
    { id: 2, name: 'JavaScript', slug: 'javascript' },
    { id: 3, name: 'Tutorial', slug: 'tutorial' }
  ]);

  // Insert posts
  await db.insert(Post).values([
    {
      id: 1,
      title: 'Getting Started with Astro',
      slug: 'getting-started-astro',
      content: 'Full post content here...',
      excerpt: 'Learn the basics of Astro',
      published: true,
      publishedAt: new Date('2024-01-15'),
      authorId: 1,
      views: 150,
      featured: true
    },
    {
      id: 2,
      title: 'Advanced Astro Patterns',
      slug: 'advanced-astro',
      content: 'Advanced content...',
      published: false,
      authorId: 2,
      views: 0
    }
  ]);
}
```

### Seed with Relationships

```typescript
import { db, Post, Tag, PostTag, Comment } from 'astro:db';

export default async function seed() {
  // ... insert posts and tags ...

  // Link posts to tags (many-to-many)
  await db.insert(PostTag).values([
    { postId: 1, tagId: 1 },  // Post 1 -> Astro
    { postId: 1, tagId: 3 },  // Post 1 -> Tutorial
    { postId: 2, tagId: 1 },  // Post 2 -> Astro
    { postId: 2, tagId: 2 }   // Post 2 -> JavaScript
  ]);

  // Add comments
  await db.insert(