Database Setup

This guide covers setting up the main VizBoard database using Prisma ORM and populating test databases with sample data for widget development.

Prerequisites

Before proceeding, ensure you have:

Docker and Docker Compose installed
Environment variables configured
PostgreSQL databases running (via docker-compose up -d)

Database Schema Overview

VizBoard uses Prisma ORM to manage the main application database (vizboard_main). The schema includes the following models:

Core Models

Model	Description
User	User accounts with authentication information
Account	OAuth provider accounts (for NextAuth.js)
Project	User projects containing dashboards and widgets
DbConnection	External database connections with encrypted credentials
Widget	Dashboard widgets (tables, charts) with configuration

Schema Highlights

model User {
  id            String    @id @default(uuid())
  firstName     String?
  lastName      String?
  email         String    @unique
  password      String?
  name          String
  emailVerified DateTime?
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  image         String?
  accounts      Account[]
  projects      Project[]
}

model Project {
  id                  String         @id @default(uuid())
  userId              String
  title               String
  description         String?
  isPublic            Boolean        @default(false)
  idPublic            String?        @unique
  orderedWidgetIds    String[]
  createdAt           DateTime       @default(now())
  updatedAt           DateTime       @updatedAt
  lastIntrospectionAt DateTime?
  user                User           @relation(fields: [userId], references: [id], onDelete: Cascade)
  dbconnections       DbConnection[]
  widgets             Widget[]
}

model DbConnection {
  id                  String    @id @default(uuid())
  projectId           String
  title               String
  dbAccess            Json      // Encrypted credentials
  dbSchema            Json?     // Introspected schema
  isValid             Boolean?
  validationError     String?
  lastIntrospectionAt DateTime?
  createdAt           DateTime  @default(now())
  updatedAt           DateTime  @updatedAt
  project             Project   @relation(fields: [projectId], references: [id], onDelete: Cascade)
}

model Widget {
  id          String   @id @default(uuid())
  projectId   String
  title       String
  description String?
  type        String   // 'table', 'chart'
  subtype     String?  // 'bar', 'line', 'area'
  configs     Json?    // Widget-specific configuration
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  project     Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
}

Prisma Migrations

Install Dependencies

Ensure all Node.js dependencies are installed:

npm install

This installs Prisma CLI and the Prisma Client.

Run Migrations

Apply all pending migrations to create the database schema:

npm run db:migrate

This command:

Applies all migrations in prisma/migrations/
Generates the Prisma Client
Creates all tables, indexes, and constraints

During development, Prisma will prompt you to name new migrations. This is normal and helps track schema changes.

Verify Schema Creation

Confirm your database schema is correct using Prisma Studio:

npm run db:studio

This opens a web interface at http://localhost:5555 where you can:

Browse all database tables
View and edit records
Verify relationships between models

Migration Commands Reference

Command	Description
`npm run db:migrate`	Apply pending migrations to the database
`npm run db:reset`	Reset database, delete all data, and reapply migrations
`npm run db:studio`	Open Prisma Studio for database exploration
`npx prisma migrate dev --name <name>`	Create a new migration (after schema changes)
`npx prisma generate`	Regenerate Prisma Client (after schema changes)

Production Migrations: Use npx prisma migrate deploy in production environments instead of migrate dev. This command applies migrations without prompting for input.

Reset Database

If you need to start fresh:

npm run db:reset

This command:

Drops all tables
Deletes migration history
Reapplies all migrations from scratch
Runs the seed script (if configured)

Data Loss: This command permanently deletes all data in the database. Only use in development.

Seeding Test Data

VizBoard includes seed scripts to populate external test databases (vizboard_test1, vizboard_test2, vizboard_test3) with sample data for widget development and testing.

Test Database Overview

Database	Port	Purpose	Tables
`vizboard_test1`	5433	E-commerce store data	`users`, `products`, `sales`, `reviews`
`vizboard_test2`	5434	E-commerce store data	`users`, `products`, `sales`, `reviews`
`vizboard_test3`	5435	Business data	`clients`, `commandes`, `apirequestevent`

Seed All Test Databases

Start Test Databases

Ensure all test databases are running:

docker-compose up -d
docker ps  # Verify all containers are running

Run All Seed Scripts

Populate all test databases with sample data:

npm run seed:test-dbs

This command runs all seed scripts sequentially:

seed:store - Seeds store data to vizboard_test1 and vizboard_test2
seed:clients - Seeds client data to vizboard_test3
seed:commandes - Seeds order data to vizboard_test3
seed:apirequestevent - Seeds API event logs to vizboard_test3

Seeding all databases takes 30-60 seconds depending on your system. Each script creates 60-80 records with realistic data using Faker.js.

Verify Seed Data

Connect to a test database to verify the data:

docker exec -it vizboard-db_test1-1 psql -U postgres -d vizboard_test1

# Inside psql:
\dt  # List all tables
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM sales;

You should see ~60 users, ~60 products, ~80 sales, and ~80 reviews.

Individual Seed Scripts

You can run seed scripts individually for specific databases:

# Seeds e-commerce data to both test1 and test2 databases
npm run seed:store

# Tables created:
# - users: Customer accounts with addresses
# - products: Product catalog with categories and prices
# - sales: Transaction records linking users and products
# - reviews: Product reviews with ratings and comments

Seed Data Characteristics

Realistic Test Data

All seed scripts use @faker-js/faker to generate realistic data:

User names, emails, addresses (localized)
Product names, descriptions, categories
Monetary values with proper decimal formatting
Dates and timestamps
Random ratings and quantities

Default Record Counts:

60 users per database
60 products per database
80 sales transactions
80 product reviews

Custom Seed Configuration

To modify seed data, edit the scripts in scripts/ directory:

scripts/seed_store.js - Store data configuration
scripts/seed_clients.js - Client data from JSON
scripts/seed_commandes.js - Order data from JSON
scripts/seed_apirequestevent.js - API event logs from JSON

Main Database Seeding

The main application database (vizboard_main) includes a basic seed script in prisma/seed.ts:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const user1 = await prisma.user.upsert({
    where: {email: '[email protected]'},
    update: {},
    create: {
      name: 'Admin Vizboard',
      firstName: 'Admin',
      lastName: 'Vizboard',
      email: '[email protected]',
      password: 'admin1234',
    },
  })
  console.log({ user1 })
}

This creates a default admin user for testing. Run it with:

npx prisma db seed

Password Hashing: The seed script stores passwords in plain text. In production, passwords should be hashed using bcrypt before storage.

Troubleshooting

Migration Failed

If migrations fail:

Check that your database is running and accessible
Verify DATABASE_URL in .env is correct
Ensure PostgreSQL user has CREATE TABLE permissions
Try resetting: npm run db:reset

Seed Script Connection Error

If seed scripts fail to connect:

Verify test databases are running: docker ps
Check database ports (5433, 5434, 5435) are not in use
Ensure Docker containers are on the same network
Restart Docker Compose: docker-compose restart

Cannot Find Module ‘pg’

Install missing dependencies:

npm install pg

Prisma Client Not Generated

Regenerate Prisma Client:

npx prisma generate

Next Steps

Start Development

With your databases set up, you’re ready to start the development server:

npm run dev

Visit http://localhost:3000 to access VizBoard.

Connect Test Databases

In VizBoard:

Create a new project
Add a database connection with these credentials:
- Host: localhost
- Port: 5433 (or 5434, 5435)
- Database: vizboard_test1 (or test2, test3)
- User: postgres
- Password: testdbpass1234
Create widgets to visualize your test data

Overview

Setup

Security

Prerequisites

Database Schema Overview

Core Models

Schema Highlights

Prisma Migrations

Migration Commands Reference

Reset Database

Seeding Test Data

Test Database Overview

Seed All Test Databases

Individual Seed Scripts

Seed Data Characteristics

Realistic Test Data

Custom Seed Configuration

Main Database Seeding

Troubleshooting

Migration Failed

Seed Script Connection Error

Cannot Find Module ‘pg’

Prisma Client Not Generated

Next Steps

Start Development

Connect Test Databases

Build docs developers (and LLMs) love

Overview

Setup

Security

​Prerequisites

​Database Schema Overview

​Core Models

​Schema Highlights

​Prisma Migrations

​Migration Commands Reference

​Reset Database

​Seeding Test Data

​Test Database Overview

​Seed All Test Databases

​Individual Seed Scripts

​Seed Data Characteristics

Realistic Test Data

​Custom Seed Configuration

​Main Database Seeding

​Troubleshooting

​Migration Failed

​Seed Script Connection Error

​Cannot Find Module ‘pg’

​Prisma Client Not Generated

​Next Steps

Start Development

​Connect Test Databases

Build docs developers (and LLMs) love

Prerequisites

Database Schema Overview

Core Models

Schema Highlights

Prisma Migrations

Migration Commands Reference

Reset Database

Seeding Test Data

Test Database Overview

Seed All Test Databases

Individual Seed Scripts

Seed Data Characteristics

Custom Seed Configuration

Main Database Seeding

Troubleshooting

Migration Failed

Seed Script Connection Error

Cannot Find Module ‘pg’

Prisma Client Not Generated

Next Steps

Connect Test Databases