Database

DB Setup & Connection

Learn how to use Supersaas Database for your project

Supersaas uses Drizzle ORM to work and interact with the database. Drizzle ORM is a database toolkit that helps you build type-safe database models and queries. Some key features of Drizzle:

Lightweight, Performant
Typesafe
SQL like syntax
Serverless ready
Supports multiple databases - Postgres, SQLite & MySQL

Database Options

Supersaas supports multiple database configurations out of the box:

NuxtHub (Default) - Uses Cloudflare D1 (SQLite) through NuxtHub for serverless deployment
PostgreSQL - For traditional database setup with full SQL capabilities
Turso - Edge database solution based on libSQL

Environment Variables

Depending on your chosen database, you'll need to set different environment variables:

For PostgreSQL

.env

POSTGRES_URL="postgresql://[email protected]:5432/your-database-name"

Turso

.env

TURSO_DB_URL=libsql://XXXX-XXXX-XXXX.turso.io
TURSO_DB_TOKEN=XXXXXXXX

For NuxtHub

No additional environment variables needed as it's handled by NuxtHub automatically.

Database Configuration

Our database is configurred in server/utils/database.ts

NuxtHub configuration

server/utils/database.ts

import { drizzle } from "drizzle-orm/d1";
import * as schema from "../database/schema";

export const tables = schema;

export function useDB() {
  return drizzle(hubDatabase(), { schema });
}

Postgres configuration

server/utils/database.ts

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "../database/schema";

export const tables = schema;

export function useDB() {
  if (!process.env.POSTGRES_URL) {
    throw new Error("Database URL not configured");
  }
  return drizzle(postgres(process.env.POSTGRES_URL), { schema });
}

Turso configuration

server/utils/database.ts

import { drizzle } from "drizzle-orm/libsql";
import * as schema from "../database/schema";
export const tables = schema;

export function useDB() {
  return drizzle({
    connection: {
      url: process.env.TURSO_DB_URL!,
      authToken: process.env.TURSO_DB_TOKEN!,
    },
  });
}

Schema

The database schema is located in the server/database/schema directory. All table definitions are exported from this location and imported as a single schema object.

You can find more information on how to define your schema in the Drizzle ORM documentation.

Example schema:

server/database/schema/users.ts

import { nanoid } from "nanoid";
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
import { relations } from "drizzle-orm";
import { oauthAccounts } from "./auth";
import { teamMembers } from "./teams";

export const users = sqliteTable("users", {
  id: text("id")
    .primaryKey()
    .notNull()
    .$default(() => nanoid()),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  avatarUrl: text("avatarUrl").default(""),
  hashedPassword: text("hashedPassword"),
  emailVerified: integer("emailVerified", { mode: "boolean" })
    .notNull()
    .default(false),
  createdAt: integer("created_at", { mode: "timestamp" }).$default(
    () => new Date()
  ),
  updatedAt: integer("updated_at", { mode: "timestamp" }).$onUpdate(
    () => new Date()
  ),
  lastActive: integer("last_active", { mode: "timestamp" }).$onUpdate(
    () => new Date()
  ),
});

export const usersRelations = relations(users, ({ many }) => ({
  oauthAccounts: many(oauthAccounts),
  teamMembers: many(teamMembers),
}));

Type Definitions

Supersaas provides type-safe database operations using Drizzle's type inference. All database types are defined in types/database.ts and are automatically generated from your schema definitions.

Basic Usage

import type { User, InsertUser } from '@@/types/database'
// Type-safe user object
const user: User = {
id: '123',
email: '[email protected]',
name: 'John Doe'
}

// Type-safe insert operation
const newUser: InsertUser = {
email: '[email protected]',
name: 'New User'

Available Types and definitions

Each table has two associated types:

TableName - Used when reading records (includes all fields)
InsertTableName - Used when creating records (omits auto-generated fields)

We also use drizzle-zod to generate zod schemas from our database schema.

Type Safety in Queries

These types are automatically enforced when using the useDB() utility

const createUser = async (userData: InsertUser) => {
  const user = await useDB()
    .insert(tables.users)
    .values(userData)
    .returning()
    .get();
  return user; // Typed as User
};

const getUser = async (id: string) => {
  const user = await useDB()
    .select()
    .from(tables.users)
    .where(eq(tables.users.id, id))
    .get();
  return user; // Typed as User | undefined
};

Turso/SQLite

Using Turso and AWS S3 with Supersaas

Drizzle ORM & useDB

Learn how to query data Drizzle ORM