The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
import { query } from './client.js'
/**
* Check if a table exists in the specified schema.
*
* @param schema - The schema name (e.g., 'public')
* @param tableName - The table name to check
* @returns true if the table exists, false otherwise
*/
export async function tableExists(schema: string, tableName: string): Promise<boolean> {
const result = await query<{ exists: boolean }>(
`SELECT EXISTS (
SELECT 1 FROM information_schema.tables
WHERE table_schema = $1 AND table_name = $2
) as exists`,
[schema, tableName]
)
return result[0]?.exists ?? false
}
/**
* Create a table with a single text column and optionally insert initial rows.
*
* @param tableName - The table name to create
* @param columnName - The name of the text column
* @param initialData - Optional array of rows to insert (each row is a key-value map)
*/
export async function createTable(
tableName: string,
columnName: string,
initialData?: Array<Record<string, string>>
) {
await query(
`CREATE TABLE IF NOT EXISTS ${tableName} (
id bigint generated by default as identity primary key,
created_at timestamp with time zone null default now(),
${columnName} text
)`
)
if (initialData && initialData.length > 0) {
const placeholders = initialData.map((_, i) => `($${i + 1})`).join(', ')
const values = initialData.map((row) => row[columnName])
await query(`INSERT INTO ${tableName} (${columnName}) VALUES ${placeholders}`, values)
}
}
/**
* Create a table with a single text column with RLS enabled (as if created through UI) and optionally insert initial rows.
*
* @param tableName - The table name to create
* @param columnName - The name of the text column
* @param initialData - Optional array of rows to insert (each row is a key-value map)
*/
export async function createTableWithRLS(
tableName: string,
columnName: string,
initialData?: Array<Record<string, string>>
) {
await createTable(tableName, columnName, initialData)
await query(`alter table public.${tableName} enable row level security;`)
}
/**
* Drop a table if it exists.
*
* @param tableName - The table name to drop
*/
export async function dropTable(tableName: string) {
await query(`DROP TABLE IF EXISTS ${tableName} CASCADE`)
}
/**
* Create a view in the public schema. Assumes the underlying table already exists.
*
* @param viewName - The view name to create
* @param selectSql - The SELECT statement that defines the view (without trailing semicolon)
*/
export async function createView(viewName: string, selectSql: string) {
await query(`CREATE OR REPLACE VIEW public.${viewName} AS ${selectSql}`)
}
/**
* Drop a view if it exists.
*
* @param viewName - The view name to drop
*/
export async function dropView(viewName: string) {
await query(`DROP VIEW IF EXISTS public.${viewName} CASCADE`)
}
/**
* Create a materialized view in the public schema.
*
* @param viewName - The materialized view name to create
* @param selectSql - The SELECT statement that defines the view (without trailing semicolon)
*/
export async function createMaterializedView(viewName: string, selectSql: string) {
await query(`CREATE MATERIALIZED VIEW IF NOT EXISTS public.${viewName} AS ${selectSql}`)
}
/**
* Drop a materialized view if it exists.
*
* @param viewName - The materialized view name to drop
*/
export async function dropMaterializedView(viewName: string) {
await query(`DROP MATERIALIZED VIEW IF EXISTS public.${viewName} CASCADE`)
}