Skip to main content
KommtKevinOnline uses PostgreSQL as its database, with Drizzle ORM for type-safe database operations. The application stores VOD transcripts, metadata, and alert configurations.

​
Database setup

1

Install PostgreSQL

You can install PostgreSQL locally or use Docker:
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
2

Create the database

If you installed PostgreSQL locally and didn’t create a database yet:
sudo -u postgres psql
Then in the PostgreSQL prompt:
CREATE DATABASE kommtkevinonline;
CREATE USER youruser WITH PASSWORD 'yourpassword';
GRANT ALL PRIVILEGES ON DATABASE kommtkevinonline TO youruser;
\q
3

Create the schema

The application uses Drizzle ORM with the schema defined in db/schema.ts. You’ll need to create the tables manually or use Drizzle Kit to generate and run migrations.Since the project includes drizzle-kit as a dev dependency, you can push the schema directly:
# Install dependencies if not already done
pnpm install

# Push schema to database
pnpm drizzle-kit push:pg
Alternatively, create the tables manually using SQL:
CREATE TABLE vods (
  vodid VARCHAR(256) PRIMARY KEY DEFAULT '',
  title TEXT DEFAULT '',
  date TIMESTAMP,
  url VARCHAR(256) DEFAULT '',
  thumbnail VARCHAR(256) DEFAULT '',
  view_count INTEGER DEFAULT 0,
  online_intend_date VARCHAR(256) DEFAULT '',
  duration INTEGER,
  transcript JSON NOT NULL
);

CREATE TABLE alerts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(256) DEFAULT '',
  text TEXT DEFAULT '',
  active BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW()
);
4

Configure the connection string

Set the NUXT_APP_POSTGRES_URL environment variable with your database connection string:
NUXT_APP_POSTGRES_URL=postgresql://username:password@localhost:5432/kommtkevinonline
See the Environment variables page for more details.

​
Database schema

The application uses two main tables:

​
VODs table

Stores Twitch VOD information and transcripts for search functionality.
​
vodid
varchar(256)
required
Primary key. The Twitch VOD identifier.
​
title
text
The title of the VOD stream.
​
date
timestamp
When the stream occurred.
​
url
varchar(256)
URL to the Twitch VOD.
​
thumbnail
varchar(256)
URL to the VOD thumbnail image.
​
view_count
integer
Number of views the VOD has received.
​
online_intend_date
varchar(256)
Intended or scheduled online date for the stream.
​
duration
integer
Duration of the VOD in seconds.
​
transcript
json
required
JSON transcript data for search and indexing purposes.

​
Alerts table

Stores site-wide alerts and notifications.
​
id
serial
required
Primary key. Auto-incrementing identifier.
​
title
varchar(256)
The alert title or heading.
​
text
text
The alert message content.
​
active
boolean
Whether the alert is currently active and should be displayed.
​
created_at
timestamp
When the alert was created. Defaults to current timestamp.

​
Database connection

The application uses Drizzle ORM with the Postgres.js driver. The connection is configured in server/db.ts: 
import postgres from "postgres";
import { drizzle } from 'drizzle-orm/postgres-js'
import * as schema from '../db/schema'

const runtimeConfig = useRuntimeConfig();

export const queryClient = postgres(runtimeConfig.app.postgresUrl);
export const db = drizzle(queryClient, { schema });
Using Docker for PostgreSQL is recommended for development environments. It provides isolation, easy cleanup, and consistent versions across team members.
Ensure your PostgreSQL connection string is kept secure and never committed to version control. Always use environment variables for sensitive credentials.

Build docs developers (and LLMs) love

Get started for freeTalk to us