Database

Tools

• Drizzle ORM: Provides a lightweight, type-safe way to interact with PostgreSQL databases.

Setup

1. Sign up / Sign in for a PostgreSQL database at Supabase, Neon, or another provider.
2. Copy the Database URL / Connection String from your provider’s dashboard (e.g., postgres://user:password@host:port/dbname).
3. Set environment variable:
   .env

   DATABASE_URL="your-postgresql-database-url"
   

4. Generate schema migrations:
   Terminal

   npm run db:generate
   

5. Apply migrations to your database:
   Terminal

   npm run db:migrate
   

Usage

• Database Connection: The database is initialized in server/db/init.ts using DATABASE_URL and connects to tables defined in server/db/schema/.
• Query Data: Use Drizzle ORM in server services, e.g., insert records.
  server/service/contact.ts

  import { useDatabase } from '~~/server/db/init';
  import * as schema from '~~/server/db/schema';
  
  export const contactServices = {
      async submit(body: { name: string; email: string; message: string }) {
          const db = useDatabase();
          await db.insert(schema.contact).values({
              id: crypto.randomUUID(),
              name: body.name,
              email: body.email,
              message: body.message,
          });
      },
  };
  

• Manage Schemas: Update server/db/schema/ to add or modify tables. Run npm run db:generate and npm run db:migrate after changes.
• Verify Setup: Check your database provider’s dashboard (e.g., Supabase or Neon) to confirm tables are created.