# External Databases

> Use any external database with Aerostack Functions — Neon, PlanetScale, Turso, Supabase, MongoDB, and more. Drizzle ORM is the recommended default.

Every Aerostack Function comes with a built-in database (`env.DB` — SQLite-compatible, zero config). But you can also connect to **any external database** — Neon (Postgres), PlanetScale (MySQL), Turso, Supabase, MongoDB, Redis, and more.

  **When to use `env.DB` vs external:** Use `env.DB` for most use cases — it's in-process, zero latency, and requires no configuration. Use an external database when you need Postgres/MySQL features (JSON columns, full-text search, stored procedures), existing data in an external system, or multi-region writes.

---

## Drizzle ORM (Recommended)

Aerostack uses [Drizzle ORM](https://orm.drizzle.team/) as the default ORM. The CLI has built-in support for Drizzle schema generation and migrations.

### Why Drizzle?

- **Edge-native** — designed to run on Cloudflare Workers (no Node.js-only dependencies)
- **Type-safe** — full TypeScript inference from your schema
- **Lightweight** — zero runtime overhead, compiles to raw SQL
- **Multi-database** — same API for SQLite (D1), Postgres (Neon), MySQL (PlanetScale)

### Using Drizzle with `env.DB` (Built-in)

1. **Install Drizzle**

   ```bash
   npm install drizzle-orm
   npm install -D drizzle-kit
   ```

2. **Define your schema**

   Create `src/schema.ts`:

   ```typescript
   import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'

   export const users = sqliteTable('users', {
     id: integer('id').primaryKey({ autoIncrement: true }),
     email: text('email').notNull().unique(),
     name: text('name').notNull(),
     createdAt: text('created_at').default('(datetime(\'now\'))'),
   })

   export const posts = sqliteTable('posts', {
     id: integer('id').primaryKey({ autoIncrement: true }),
     userId: integer('user_id').references(() => users.id),
     title: text('title').notNull(),
     content: text('content').notNull(),
     publishedAt: text('published_at'),
   })
   ```

3. **Generate types**

   ```bash
   aerostack generate types
   ```

4. **Use in your function**

   ```typescript
   import { drizzle } from 'drizzle-orm/d1'
   import { eq } from 'drizzle-orm'
   import * as schema from './schema'

   interface Env {
     DB: Database
   }

   export default {
     async fetch(request: Request, env: Env): Promise {
       const db = drizzle(env.DB, { schema })

       // Type-safe queries
       const allUsers = await db.select().from(schema.users)

       const userWithPosts = await db.query.users.findFirst({
         where: eq(schema.users.email, 'alice@example.com'),
         with: { posts: true }
       })

       // Insert
       const newUser = await db.insert(schema.users).values({
         email: 'bob@example.com',
         name: 'Bob'
       }).returning()

       return Response.json({ users: allUsers })
     }
   }
   ```

---

## Neon (Serverless Postgres)

[Neon](https://neon.tech) provides serverless Postgres with a HTTP driver that works on Cloudflare Workers.

1. **Create a Neon database**

   Sign up at [neon.tech](https://neon.tech) and create a project. Copy the connection string.

2. **Install dependencies**

   ```bash
   npm install drizzle-orm @neondatabase/serverless
   npm install -D drizzle-kit
   ```

3. **Define your schema**

   Create `src/schema.ts`:

   ```typescript
   import { pgTable, serial, text, timestamp, integer } from 'drizzle-orm/pg-core'

   export const users = pgTable('users', {
     id: serial('id').primaryKey(),
     email: text('email').notNull().unique(),
     name: text('name').notNull(),
     createdAt: timestamp('created_at').defaultNow(),
   })

   export const posts = pgTable('posts', {
     id: serial('id').primaryKey(),
     userId: integer('user_id').references(() => users.id),
     title: text('title').notNull(),
     content: text('content').notNull(),
   })
   ```

4. **Set your connection string**

   ```bash
   # Local development
   echo "DATABASE_URL=postgres://user:pass@ep-xxx.us-east-2.aws.neon.tech/mydb" >> .dev.vars

   # Production
   aerostack secrets set DATABASE_URL
   ```

5. **Use in your function**

   ```typescript
   import { neon } from '@neondatabase/serverless'
   import { drizzle } from 'drizzle-orm/neon-http'
   import * as schema from './schema'

   interface Env {
     DATABASE_URL: string
     DB: Database  // You can still use the built-in DB alongside Neon
   }

   export default {
     async fetch(request: Request, env: Env): Promise {
       const sql = neon(env.DATABASE_URL)
       const db = drizzle(sql, { schema })

       const users = await db.select().from(schema.users)

       return Response.json({ users })
     }
   }
   ```

  External database calls add network latency (20-100ms per query). Use `env.CACHE` to cache frequent reads and minimize round trips.

---

## Turso (Distributed SQLite)

[Turso](https://turso.tech) provides distributed SQLite with edge replicas — similar to `env.DB` but with multi-region write support.

```bash
npm install @libsql/client drizzle-orm
```

```typescript

interface Env {
  TURSO_URL: string
  TURSO_AUTH_TOKEN: string
}

  async fetch(request: Request, env: Env): Promise {
    const client = createClient({
      url: env.TURSO_URL,
      authToken: env.TURSO_AUTH_TOKEN,
    })

    const db = drizzle(client, { schema })
    const users = await db.select().from(schema.users)

    return Response.json({ users })
  }
}
```

---

## Supabase (Postgres + Realtime)

```bash
npm install @supabase/supabase-js
```

```typescript

interface Env {
  SUPABASE_URL: string
  SUPABASE_ANON_KEY: string
}

  async fetch(request: Request, env: Env): Promise {
    const supabase = createClient(env.SUPABASE_URL, env.SUPABASE_ANON_KEY)

    const { data: users, error } = await supabase
      .from('users')
      .select('*')
      .eq('active', true)

    if (error) return Response.json({ error: error.message }, { status: 500 })
    return Response.json({ users })
  }
}
```

---

## MongoDB Atlas

```bash
npm install mongodb
```

```typescript

interface Env {
  MONGODB_URI: string
}

  async fetch(request: Request, env: Env): Promise {
    const client = new MongoClient(env.MONGODB_URI)

    try {
      const db = client.db('myapp')
      const users = await db.collection('users').find({ active: true }).toArray()
      return Response.json({ users })
    } finally {
      await client.close()
    }
  }
}
```

  MongoDB's serverless driver works on Cloudflare Workers. Use the Data API if the native driver has compatibility issues.

---

## Upstash Redis

For caching and real-time data beyond what `env.CACHE` provides (pub/sub, sorted sets, streams):

```bash
npm install @upstash/redis
```

```typescript

interface Env {
  UPSTASH_REDIS_REST_URL: string
  UPSTASH_REDIS_REST_TOKEN: string
}

  async fetch(request: Request, env: Env): Promise {
    const redis = new Redis({
      url: env.UPSTASH_REDIS_REST_URL,
      token: env.UPSTASH_REDIS_REST_TOKEN,
    })

    // Sorted set for leaderboard
    await redis.zadd('leaderboard', { score: 100, member: 'alice' })
    const top10 = await redis.zrange('leaderboard', 0, 9, { rev: true, withScores: true })

    return Response.json({ leaderboard: top10 })
  }
}
```

---

## Best Practices

### Combine built-in + external

You don't have to choose one or the other. Use `env.DB` for fast, local data and an external database for specialized needs:

```typescript

  async fetch(request: Request, env: Env): Promise {
    const sql = neon(env.DATABASE_URL)
    const pgDb = drizzle(sql)

    // Fast: session lookup from built-in DB
    const session = await env.DB
      .prepare('SELECT * FROM sessions WHERE token = ?')
      .bind(token)
      .first()

    // Specialized: complex Postgres query for analytics
    const analytics = await pgDb.execute(
      sql`SELECT date_trunc('day', created_at) as day, count(*) FROM events GROUP BY 1`
    )

    // Cache the result
    await env.CACHE.put('analytics:daily', JSON.stringify(analytics), { expirationTtl: 3600 })

    return Response.json({ session, analytics })
  }
}
```

### Cache external queries

External database calls are 50-200x slower than `env.DB`. Always cache frequent reads:

```typescript
async function getUser(env: Env, userId: string) {
  // Check cache first
  const cached = await env.CACHE.get(`user:${userId}`, 'json')
  if (cached) return cached

  // External DB query
  const sql = neon(env.DATABASE_URL)
  const result = await sql`SELECT * FROM users WHERE id = ${userId}`
  const user = result[0]

  // Cache for 5 minutes
  if (user) {
    await env.CACHE.put(`user:${userId}`, JSON.stringify(user), { expirationTtl: 300 })
  }

  return user
}
```

### Set secrets properly

Never put database credentials in `aerostack.toml` or source code:

```bash
# Local development
echo "DATABASE_URL=postgres://..." >> .dev.vars

# Production
aerostack secrets set DATABASE_URL
```

Add `.dev.vars` to `.gitignore`.

---

## Database Compatibility Matrix

| Database | Package | Edge Compatible | Drizzle Support |
|---|---|---|---|
| Built-in (`env.DB`) | None needed | Native | Yes (d1) |
| Neon (Postgres) | `@neondatabase/serverless` | Yes | Yes (neon-http) |
| Turso (SQLite) | `@libsql/client` | Yes | Yes (libsql) |
| PlanetScale (MySQL) | `@planetscale/database` | Yes | Yes (planetscale) |
| Supabase (Postgres) | `@supabase/supabase-js` | Yes | Via Neon driver |
| MongoDB Atlas | `mongodb` | Partial | No |
| Upstash Redis | `@upstash/redis` | Yes | No (not SQL) |
| CockroachDB | `@neondatabase/serverless` | Yes | Via pg driver |