Astro DB

          
            ## Astro DB Guidelines

1.  Purpose: Astro DB provides a fully managed SQL database solution built on libSQL, integrated with the Astro ecosystem. It simplifies database configuration, local development, and deployment.

2.  Installation: Add Astro DB to your project using the Astro CLI:

    ```bash
    npx astro add db
    ```

3.  Configuration (`db/config.ts`):

    - Define database tables within the `db/config.ts` file using `defineDb` and `defineTable` imported from `astro:db`.
    - Columns: Define table columns using helpers from `astro:db`, specifying the data type (e.g., `column.text()`, `column.number()`, `column.boolean()`, `column.date()`, `column.json()`).
    - Attributes: Configure columns with attributes like `primaryKey`, `unique`, `optional`, `default`, `references`.
    - Relationships: Define foreign key relationships using `column.number({ references: () => OtherTable.columns.id })`.

4.  Local Development:

    - When running `astro dev`, Astro DB automatically creates and manages a local SQLite database file at `.astro/content.db`. No external database or Docker is needed for local development.

5.  Seeding (`db/seed.ts`):

    - Create a `db/seed.ts` file to populate your database with initial data for development.
    - Import `db` and table definitions from `astro:db`.
    - Use `db.insert(MyTable).values([...])` within the default export async function.
    - The seed script runs automatically during `astro dev`.

6.  Querying (`astro:db` & Drizzle ORM):

    - Import the `db` client and table definitions from the `astro:db` module in your `.astro` components or API endpoints.
    - Astro DB uses Drizzle ORM for querying.
    - Common Operations:
      - Select: `await db.select().from(MyTable).where(...)`
      - Insert: `await db.insert(MyTable).values({ ... })`
      - Update: `await db.update(MyTable).set({ ... }).where(...)`
      - Delete: `await db.delete(MyTable).where(...)`
    - Filtering: Import and use Drizzle operators (`eq`, `gt`, `lt`, `like`, `inArray`, etc.) from `astro:db` within `.where()` clauses.
    - Relationships defined in the schema can be queried.
    - Use `db.batch([...])` for atomic transactions.

7.  Production Deployment:

    - Connect to a remote libSQL-compatible database (e.g., Turso).
    - Configure connection using environment variables:
      - `ASTRO_DB_REMOTE_URL`: The URL of your remote database.
      - `ASTRO_DB_AUTH_TOKEN`: The authentication token for the remote database.

8.  Pushing Schema Changes (`astro db push`):

    - Apply schema changes defined in `db/config.ts` to your database.
    - `astro db push`: Updates the local development database.
    - `astro db push --remote`: Updates the configured remote production database.
    - The command checks for potential data loss and prevents destructive changes by default.
    - Force Reset: Use `astro db push --remote --force-reset` to destroy and recreate the remote database schema according to `db/config.ts`. This causes data loss and should be used with extreme caution.
    - Renaming Tables: Requires a safe, multi-step migration process using the `deprecated: true` flag (see docs for details) to avoid data loss.

9.  Executing Scripts (`astro db execute`):

    - Run a `.ts` script file containing `astro:db` queries against a database.
    - Useful for data migrations or seeding production data.
    - `astro db execute path/to/script.ts --remote`: Executes the script against the remote database.

10. Integrations:
    - Astro integrations can define their own DB tables and seed data using the `astro:db:setup` hook and the `extendDb()` method.
    - Use `defineDbIntegration()` and `asDrizzleTable()` helpers for type safety within integrations.

Reference: [Astro DB Docs](https://docs.astro.build/en/guides/astro-db/)