Database

Set up a PostgreSQL database in ShipAhead using Drizzle ORM to manage data like users and sessions.

Tools Used

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

Setup & Configuration

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

   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/database/init.js using DATABASE_URL and connects to tables defined in server/database/schema.js.

• Querying Data: Use Drizzle ORM in server routes or APIs. Example:

  javascript

  // server/api/users.get.js
  import { getDatabase } from '~~/server/database/init';
  import { user } from '~~/server/database/schema';
  
  export default defineEventHandler(async () => {
      const db = getDatabase();
      return await db.select().from(user);
  });

• Managing Schemas: Update server/database/schema.js 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. For more details, visit Drizzle ORM Documentation.