npm package: @flightdev/db
Description: Flight Database Abstraction Layer - Agnostic database adapters
Author: Flight Contributors
Latest version: 0.0.3

# @flightdev/db

Database abstraction layer for Flight Framework. Connect to any database with a unified, type-safe API.

## Installation

```bash
npm install @flightdev/db
```

Install the driver for your database:

```bash
# PostgreSQL
npm install pg

# Turso / SQLite
npm install @libsql/client

# Neon Serverless
npm install @neondatabase/serverless

# Supabase
npm install @supabase/supabase-js
```

## Quick Start

```typescript
import { createDb } from '@flightdev/db';
import { postgres } from '@flightdev/db/postgres';

const db = createDb(postgres({
  connectionString: process.env.DATABASE_URL,
}));

// Query with type inference
const users = await db.query<{ id: string; email: string }>(
  'SELECT * FROM users WHERE active = $1',
  [true]
);

console.log(users.rows);
```

## Drivers

### PostgreSQL

Standard PostgreSQL driver using `pg` with connection pooling.

```typescript
import { postgres } from '@flightdev/db/postgres';

const driver = postgres({
  connectionString: 'postgresql://user:pass@localhost:5432/mydb',
  // Or use individual options:
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'user',
  password: 'pass',
  ssl: true,
  poolSize: 10,
  timeout: 30000,
});
```

### Turso / SQLite

Cloud SQLite with Turso or local SQLite files.

```typescript
import { turso } from '@flightdev/db/turso';

// Turso Cloud
const cloud = turso({
  url: 'libsql://your-db.turso.io',
  authToken: process.env.TURSO_AUTH_TOKEN,
});

// Local SQLite
const local = turso({
  url: 'file:local.db',
});
```

### Neon Serverless

Edge-compatible PostgreSQL for serverless environments.

```typescript
import { neon } from '@flightdev/db/neon';

// HTTP mode (recommended for serverless)
const http = neon({
  connectionString: process.env.NEON_DATABASE_URL,
  mode: 'http',
});

// WebSocket mode (for transactions)
const ws = neon({
  connectionString: process.env.NEON_DATABASE_URL,
  mode: 'websocket',
});
```

### Supabase

Direct database access with Supabase's query builder.

```typescript
import { supabase } from '@flightdev/db/supabase';

const driver = supabase({
  url: process.env.SUPABASE_URL,
  key: process.env.SUPABASE_ANON_KEY,
  schema: 'public',
});
```

### Cloudflare D1

Native Cloudflare D1 support for edge SQLite.

```typescript
import { d1 } from '@flightdev/db/d1';

export default {
  async fetch(request, env) {
    const db = createDb(d1({ binding: env.DB }));
    const users = await db.query('SELECT * FROM users');
    return Response.json(users.rows);
  }
};
```

## API Reference

### Database Interface

```typescript
interface Database {
  // Run a query that returns rows
  query<T>(sql: string, params?: unknown[]): Promise<QueryResult<T>>;

  // Execute a statement (INSERT, UPDATE, DELETE)
  execute(sql: string, params?: unknown[]): Promise<ExecuteResult>;

  // Run operations in a transaction
  transaction<T>(fn: (tx: Transaction) => Promise<T>): Promise<T>;

  // Close the connection
  close(): Promise<void>;

  // Check connection health
  ping(): Promise<boolean>;
}
```

### Query Result

```typescript
interface QueryResult<T> {
  rows: T[];
  rowCount: number;
  raw?: unknown;
}
```

### Execute Result

```typescript
interface ExecuteResult {
  rowsAffected: number;
  insertId?: string | number;
  raw?: unknown;
}
```

## Transactions

All drivers support transactions with automatic rollback on error.

```typescript
const result = await db.transaction(async (tx) => {
  const user = await tx.execute(
    'INSERT INTO users (email) VALUES ($1) RETURNING id',
    ['user@example.com']
  );

  await tx.execute(
    'INSERT INTO profiles (user_id, name) VALUES ($1, $2)',
    [user.insertId, 'John Doe']
  );

  return user.insertId;
});
```

## Type Safety

Define your row types for compile-time safety.

```typescript
interface User {
  id: string;
  email: string;
  name: string;
  created_at: Date;
}

const users = await db.query<User>('SELECT * FROM users');
// users.rows is typed as User[]
```

## Best Practices

1. **Use connection pooling** - All drivers support connection pools by default.
2. **Parameterize queries** - Always use parameterized queries to prevent SQL injection.
3. **Handle errors** - Wrap database operations in try-catch blocks.
4. **Close connections** - Call `db.close()` when shutting down your application.

## License

MIT