Overview
Showtimes NG uses Supabase (PostgreSQL) as its database. The schema consists of three main tables: movies, cinemas, and showtimes.
Database Tables
Movies Table
Stores information about movies currently showing or scheduled to show.
Primary key, auto-incrementing integer
Movie plot or description
Year the movie was released
Movie rating (e.g., IMDb rating)
URL to movie poster image
Rotten Tomatoes score (0-100)
ISO timestamp when record was created
ISO timestamp when record was last updated
Cinemas Table
Stores information about cinema locations across Lagos.
Primary key, auto-incrementing integer
Cinema name (e.g., “Filmhouse IMAX Lekki”)
Short location identifier (e.g., “Lekki”, “VI”)
Full location name (e.g., “Victoria Island”)
ISO timestamp when record was created
ISO timestamp when record was last updated
Showtimes Table
Stores individual movie showings at specific cinemas.
Primary key, auto-incrementing integer
Foreign key referencing movies.id
Foreign key referencing cinemas.id
ISO timestamp when the movie showing starts
Type of screen (e.g., “IMAX”, “Standard”, “3D”, “VIP”)
Link to cinema’s booking page for this showing
ISO timestamp when record was created
ISO timestamp when record was last updated
TypeScript Types
The database schema is represented by TypeScript interfaces in src/lib/queries.ts:
export interface Movie {
id: number;
title: string;
description: string | null;
release_year: number | null;
duration_minutes: number | null;
rating: number | null;
poster_url: string | null;
metacritic_rating: number | null;
rotten_tomatoes_rating: number | null;
created_at: string;
updated_at: string;
}
Database Queries
The application uses Supabase’s JavaScript client for all database operations. Key query functions are defined in src/lib/queries.ts.
Movie Queries
Get Now Showing Movies
Returns all movies with at least one future showtime, sorted by earliest showtime:
export async function getNowShowingMovies() {
const now = new Date().toISOString();
const { data, error } = await supabase
.from('movies')
.select(`
*,
showtimes!inner(id, start_time)
`)
.gte('showtimes.start_time', now);
if (error) throw error;
return data as Movie[];
}
The !inner modifier performs an inner join, ensuring only movies with matching showtimes are returned.
Get All Movies
export async function getAllMovies() {
const { data, error } = await supabase
.from('movies')
.select('*')
.order('title');
if (error) throw error;
return data as Movie[];
}
Get Movie by ID
export async function getMovieById(id: number) {
const { data, error } = await supabase
.from('movies')
.select('*')
.eq('id', id)
.single();
if (error) throw error;
return data as Movie;
}
Cinema Queries
Get All Cinemas
export async function getCinemas() {
const { data, error } = await supabase
.from('cinemas')
.select('*')
.order('name');
if (error) throw error;
return data as Cinema[];
}
Get Cinema by ID
export async function getCinemaById(id: number) {
const { data, error } = await supabase
.from('cinemas')
.select('*')
.eq('id', id)
.single();
if (error) throw error;
return data as Cinema;
}
Showtime Queries
Get Showtimes for Movie
Returns all future showtimes for a specific movie, with cinema information:
export async function getShowtimesForMovie(movieId: number) {
const now = new Date().toISOString();
const { data, error } = await supabase
.from('showtimes')
.select(`
*,
cinema:cinemas(*)
`)
.eq('movie_id', movieId)
.gte('start_time', now)
.order('start_time');
if (error) throw error;
return data;
}
Get Showtimes for Cinema
Returns all future showtimes at a specific cinema, with movie information:
export async function getShowtimesForCinema(cinemaId: number) {
const now = new Date().toISOString();
const { data, error } = await supabase
.from('showtimes')
.select(`
*,
movie:movies(*)
`)
.eq('cinema_id', cinemaId)
.gte('start_time', now)
.order('start_time');
if (error) throw error;
return data;
}
Helper Functions
Generate Slug
Converts text to URL-friendly slugs:
export function generateSlug(text: string): string {
return text
.toLowerCase()
.replace(/[^a-z0-9]+/g, '-')
.replace(/(^-|-$)/g, '');
}
generateSlug("The Dark Knight") // "the-dark-knight"
generateSlug("Mission: Impossible") // "mission-impossible"
Get Display Location
Returns the verbose location if available, otherwise falls back to the short location:
export function getDisplayLocation(cinema: Cinema): string | null {
return cinema.verbose_location || cinema.location;
}
Creating the Schema
To create the database schema in your Supabase project:
Open Supabase SQL Editor
Navigate to your Supabase project dashboard and open the SQL Editor.
Create movies table
CREATE TABLE movies (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
release_year INTEGER,
duration_minutes INTEGER,
rating NUMERIC,
poster_url TEXT,
metacritic_rating INTEGER,
rotten_tomatoes_rating INTEGER,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
Create cinemas table
CREATE TABLE cinemas (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
location TEXT,
verbose_location TEXT,
address TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
Create showtimes table
CREATE TABLE showtimes (
id SERIAL PRIMARY KEY,
movie_id INTEGER NOT NULL REFERENCES movies(id) ON DELETE CASCADE,
cinema_id INTEGER NOT NULL REFERENCES cinemas(id) ON DELETE CASCADE,
start_time TIMESTAMPTZ NOT NULL,
screen_type TEXT NOT NULL,
movie_url TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
Create indexes
Add indexes for better query performance:CREATE INDEX idx_showtimes_movie_id ON showtimes(movie_id);
CREATE INDEX idx_showtimes_cinema_id ON showtimes(cinema_id);
CREATE INDEX idx_showtimes_start_time ON showtimes(start_time);
Enable Row Level Security (optional)
For public read access:ALTER TABLE movies ENABLE ROW LEVEL SECURITY;
ALTER TABLE cinemas ENABLE ROW LEVEL SECURITY;
ALTER TABLE showtimes ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Enable read access for all users" ON movies
FOR SELECT USING (true);
CREATE POLICY "Enable read access for all users" ON cinemas
FOR SELECT USING (true);
CREATE POLICY "Enable read access for all users" ON showtimes
FOR SELECT USING (true);
Remember to restrict write access appropriately in production. The above policies only enable read access for all users.
Relationships
- One movie can have many showtimes
- One cinema can host many showtimes
- Each showtime belongs to one movie and one cinema