Database

By default, we use Drizzle as our database layer to handle schema, migrations, etc.

We’ve also chosen to go with Neon.

You can use any PostgreSQL database or migrate to any other database provider.

Database Schema

You can find the current schema file in src/db/schema.ts, you can add new tables or modify existing tables easily following this guide from Drizzle.

Once you’ve made changes you can follow these steps to generate migrations and apply them:

Generate database migrations
Terminal window
```
pnpm run db:generate
```
Run database migration
Terminal window
```
pnpm run db:migrate
```

Querying Data

To query data, you can use Drizzle, for example using our included notes sample app to query notes for a user:

//...
const session = Astro.locals.session;
const userId = session.get("userId"); // Get the session data

import { db } from "@/db";
import { notes } from "@/db/schema";
import { eql } from "drizzle-orm";

//...
let myNotes = await db
    .select()
    .from(notes)
    .where(eq(notes.userId, user.id))
    .execute();
//...

This tells the app to query notes belonging to the logged in user and return them, since we’re using the notes schema it will follow what we’ve set as fields.

Saving New Data

Saving new data is easy, using our example notes again:

//...
const session = Astro.locals.session;
const userId = session.get("userId"); // Get the session data

import { db } from "@/db";
import { notes } from "@/db/schema";
import { eql } from "drizzle-orm";

//...
await db.insert(notes).values({ title, content, userId: userId });
//...

Updating Data

Similarly, updating data is just as easy:

//...
const session = Astro.locals.session;
const userId = session.get("userId"); // Get the session data

import { db } from "@/db";
import { notes } from "@/db/schema";
import { eql } from "drizzle-orm";

//...
await db.update(notes).set({ title, content, updatedAt: new Date() }).where(
            and(
                eq(notes.id, id),
                eq(notes.userId, userId)
            )
        );
//...

This tells it to update the title, content and the updateAt fields for the note that matches id and belongs to the user as denoted by userId.

Deleting Data

Finally, to delete data:

//...
const session = Astro.locals.session;
const userId = session.get("userId"); // Get the session data

import { db } from "@/db";
import { notes } from "@/db/schema";
import { eql } from "drizzle-orm";

//...
await db.delete(notes).where(
        and(
            eq(notes.id, id),
            eq(notes.userId, userId)
        )
    );
//...

This tells it to delete the note that matches id and belongs to the user as denoted by userId.

Drizzle brings an extensive ORM to our starter that lets you do a number of things without too much code, so I recommend diving into it and learning what you can do, we’ve kept what we do with Drizzle mostly simple to get started.