The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
import { afterAll, beforeAll, expect, test } from 'vitest'
import pgMeta from '../src/index'
import { cleanupRoot, createTestDatabase } from './db/utils'
beforeAll(async () => {
// Any global setup if needed
})
afterAll(async () => {
await cleanupRoot()
})
const withTestDatabase = (
name: string,
fn: (db: Awaited<ReturnType<typeof createTestDatabase>>) => Promise<void>
) => {
test(name, async () => {
const db = await createTestDatabase()
try {
await fn(db)
} finally {
await db.cleanup()
}
})
}
withTestDatabase('list roles', async ({ executeQuery }) => {
const { sql, zod } = await pgMeta.roles.list()
const res = zod.parse(await executeQuery(sql))
let role = res.find(({ name }) => name === 'postgres')
expect(role).toMatchInlineSnapshot(
{ activeConnections: expect.any(Number), id: expect.any(Number) },
`
{
"activeConnections": Any<Number>,
"canBypassRls": true,
"canCreateDb": true,
"canCreateRole": true,
"canLogin": true,
"config": {},
"connectionLimit": 100,
"id": Any<Number>,
"inheritRole": true,
"isReplicationRole": true,
"isSuperuser": true,
"name": "postgres",
"validUntil": null,
}
`
)
// pg_monitor is a predefined role. `includeDefaultRoles` defaults to false,
// so it shouldn't be included in the result.
role = res.find(({ name }) => name === 'pg_monitor')
expect(role).toMatchInlineSnapshot(`undefined`)
})
withTestDatabase('list roles w/ default roles', async ({ executeQuery }) => {
const { sql, zod } = await pgMeta.roles.list({ includeDefaultRoles: true })
const res = zod.parse(await executeQuery(sql))
const role = res.find(({ name }) => name === 'pg_monitor')
expect(role).toMatchInlineSnapshot(
{
activeConnections: expect.any(Number),
id: expect.any(Number),
},
`
{
"activeConnections": Any<Number>,
"canBypassRls": false,
"canCreateDb": false,
"canCreateRole": false,
"canLogin": false,
"config": {},
"connectionLimit": 100,
"id": Any<Number>,
"inheritRole": true,
"isReplicationRole": false,
"isSuperuser": false,
"name": "pg_monitor",
"validUntil": null,
}
`
)
})
withTestDatabase('retrieve, create, update, delete roles', async ({ executeQuery }) => {
// Create role
const { sql: createSql } = pgMeta.roles.create({
name: 'r1',
isSuperuser: true,
canCreateDb: true,
canCreateRole: true,
inheritRole: false,
canLogin: true,
isReplicationRole: true,
canBypassRls: true,
connectionLimit: 100,
validUntil: '2020-01-01T00:00:00.000Z',
config: { search_path: 'extension, public' },
})
await executeQuery(createSql)
// Retrieve the created role using list
const { sql: listSql, zod: listZod } = await pgMeta.roles.list()
const roles = listZod.parse(await executeQuery(listSql))
const createdRole = roles.find((role) => role.name === 'r1')
expect(createdRole).toMatchInlineSnapshot(
{ id: expect.any(Number), activeConnections: expect.any(Number) },
`
{
"activeConnections": Any<Number>,
"canBypassRls": true,
"canCreateDb": true,
"canCreateRole": true,
"canLogin": true,
"config": {
"search_path": ""extension, public"",
},
"connectionLimit": 100,
"id": Any<Number>,
"inheritRole": false,
"isReplicationRole": true,
"isSuperuser": true,
"name": "r1",
"validUntil": "2020-01-01 00:00:00+00",
}
`
)
// Remove role
const { sql: removeSql } = pgMeta.roles.remove({ id: createdRole!.id })
await executeQuery(removeSql)
// Create a new role for update test
const { sql: createNewSql } = pgMeta.roles.create({
name: 'r1',
})
await executeQuery(createNewSql)
// Get the role ID for update
const { sql: getIdSql, zod: getIdZod } = await pgMeta.roles.list()
const roleForUpdate = getIdZod
.parse(await executeQuery(getIdSql))
.find((role) => role.name === 'r1')
// Update role with ISO string date
const { sql: updateSql } = pgMeta.roles.update(
{ id: roleForUpdate!.id },
{
name: 'rr',
isSuperuser: true,
canCreateDb: true,
canCreateRole: true,
inheritRole: false,
canLogin: true,
isReplicationRole: true,
canBypassRls: true,
connectionLimit: 100,
validUntil: '2020-01-01T00:00:00.000Z',
}
)
await executeQuery(updateSql)
// Verify update using retrieve
const { sql: retrieveUpdatedSql, zod: retrieveZod } = pgMeta.roles.retrieve({
id: roleForUpdate!.id,
})
const updatedRole = retrieveZod.parse((await executeQuery(retrieveUpdatedSql))[0])
expect(updatedRole).toMatchInlineSnapshot(
{ id: expect.any(Number), activeConnections: expect.any(Number) },
`
{
"activeConnections": Any<Number>,
"canBypassRls": true,
"canCreateDb": true,
"canCreateRole": true,
"canLogin": true,
"config": {},
"connectionLimit": 100,
"id": Any<Number>,
"inheritRole": false,
"isReplicationRole": true,
"isSuperuser": true,
"name": "rr",
"validUntil": "2020-01-01 00:00:00+00",
}
`
)
// Create role with config
const { sql: createConfigSql } = pgMeta.roles.create({
name: 'config_role',
config: { search_path: 'public', log_statement: 'all' },
})
await executeQuery(createConfigSql)
// Verify config role using list
const { sql: listConfigSql, zod: listConfigZod } = await pgMeta.roles.list()
const configRole = listConfigZod
.parse(await executeQuery(listConfigSql))
.find((role) => role.name === 'config_role')
expect(configRole).toMatchInlineSnapshot(
{ id: expect.any(Number), activeConnections: expect.any(Number) },
`
{
"activeConnections": Any<Number>,
"canBypassRls": false,
"canCreateDb": false,
"canCreateRole": false,
"canLogin": false,
"config": {
"log_statement": "all",
"search_path": "public",
},
"connectionLimit": 100,
"id": Any<Number>,
"inheritRole": true,
"isReplicationRole": false,
"isSuperuser": false,
"name": "config_role",
"validUntil": null,
}
`
)
// Remove role and verify it's gone
const { sql: finalRemoveSql } = pgMeta.roles.remove({ id: configRole!.id })
await executeQuery(finalRemoveSql)
const { sql: finalListSql, zod: finalListZod } = await pgMeta.roles.list()
const finalRoles = finalListZod.parse(await executeQuery(finalListSql))
expect(finalRoles.find((role) => role.name === 'config_role')).toBeUndefined()
})
withTestDatabase('update and remove a role whose name needs quoting', async ({ executeQuery }) => {
const { sql: createSql } = pgMeta.roles.create({ name: 'quoting_r1' })
await executeQuery(createSql)
const { sql: listSql, zod: listZod } = pgMeta.roles.list()
const findRole = async (name: string) =>
listZod.parse(await executeQuery(listSql)).find((role) => role.name === name)
const { id } = (await findRole('quoting_r1'))!
// The rename and `valid until %L` are separate statements in one `do` block.
await executeQuery(
pgMeta.roles.update(
{ id },
{ name: `it's a "role"`, canLogin: true, validUntil: '2030-01-01T00:00:00.000Z' }
).sql
)
const renamed = (await findRole(`it's a "role"`))!
expect(await findRole('quoting_r1')).toBeUndefined()
expect(renamed.id).toBe(id)
expect(renamed.canLogin).toBe(true)
// The server's time zone decides how the value is printed, so compare instants.
expect(new Date(renamed.validUntil!).toISOString()).toBe('2030-01-01T00:00:00.000Z')
await executeQuery(pgMeta.roles.remove({ id }).sql)
expect(await findRole(`it's a "role"`)).toBeUndefined()
})