PostgreSQL

import { graphqlPlugin } from '@ultimabase/graphql'
import { httpPlugin } from '@ultimabase/http'
import { postgres } from '@ultimabase/postgres'
import { defineApp } from 'ultimabase'
import { Project, Task } from './models.ts'

export const app = defineApp({
	// Outside production, a start syncs the tables to the models; in
	// production it checks that the migrations deployed match them.
	database: postgres({ url: process.env['DATABASE_URL'] }),
	// Listens on PORT, or 3000.
	plugins: [httpPlugin(), graphqlPlugin()],
	models: [Project, Task],
	// Accepted in production, where it needs 32 characters at least.
	masterKey: { key: process.env['MASTER_KEY'], production: true },
})

export { app as default } from './src/app.ts'

npx ultimabase dev

const synced = await ultimabase('sync', { env: development })
expect(synced.code).toBe(0)
expect(synced.output).toContain("Created the database's tables.")
expect(await ultimabase('sync', { env: development })).toMatchObject({
	code: 0,
	output: 'The database already matches the models.\n',
})

npx ultimabase sync --force

export const Project = defineModel({
	name: 'Project',
	use: [graphql()],
	fields: (f) => ({
		name: f.string({ unique: true }),
		tasks: f.many(() => Task),
	}),
	// Open to every client, to keep the example on the database.
}).with({ access: allow.public })

export const Task = defineModel({
	name: 'Task',
	use: [graphql()],
	fields: (f) => ({
		title: f.string(),
		done: f.boolean({ default: false }),
		// Added after the first migration: the second one adds its column.
		priority: f.number({ integer: true, default: 0 }),
		// A project's tasks are deleted with it.
		project: f.one(() => Project, { onDelete: 'cascade' }),
	}),
}).with({ access: allow.public })

npx ultimabase migrate create add_priority

// After `priority` was added to Task: `migrate create add_priority`.
expect(await lines('20261029230454_add_priority.sql')).toEqual([
	header,
	'',
	'alter table "task" add column "priority" integer not null default 0;',
	'',
	'alter table "task" alter column "priority" drop default;',
	'',
])

// `migrate create first_tasks_urgent`, then the update written in it.
expect(await lines('20261029230505_first_tasks_urgent.sql')).toEqual([
	header,
	'',
	'-- The tasks written before priorities came first.',
	'update "task" set "priority" = 1 where not "done";',
	'',
])

// A statement PostgreSQL runs outside a transaction, alone in its file.
expect(await lines('20261029230507_open_tasks_index.sql')).toEqual([
	header,
	'-- ultimabase no transaction',
	'',
	'-- Built without locking the table against writes.',
	'create index concurrently "task_open_idx" on "task" ("project_id") where not "done";',
	'',
])

npx ultimabase migrate deploy

// Exits 1 while a deploy has something to do.
expect(await ultimabase('migrate status')).toMatchObject({
	code: 1,
	output: [
		'20261026200257_init.sql is pending.',
		'20261029230454_add_priority.sql is pending.',
		'20261029230505_first_tasks_urgent.sql is pending.',
		'20261029230507_open_tasks_index.sql is pending.',
		'',
	].join('\n'),
})

expect(await ultimabase('migrate deploy')).toMatchObject({
	code: 0,
	output: [
		'Applied 20261026200257_init.sql.',
		'Applied 20261029230454_add_priority.sql.',
		'Applied 20261029230505_first_tasks_urgent.sql.',
		'Applied 20261029230507_open_tasks_index.sql.',
		'',
	].join('\n'),
})
expect(await ultimabase('migrate status')).toMatchObject({
	code: 0,
	output: 'The database is up to date.\n',
})

npx ultimabase reset

// A deploy runs only on a database the migrations built.
expect(await ultimabase('migrate deploy', { env: development })).toMatchObject({
	code: 1,
	output: [
		'Development sync changed this database since its last deploy: deploy applies migrations only to a database they built.',
		'Restore the dump you test on, or rebuild this database from the migrations with `npx ultimabase reset`.',
		'',
	].join('\n'),
})

expect(await ultimabase('reset', { env: development })).toMatchObject({
	code: 0,
	output: [
		'Dropped project, task.',
		'Applied 20261026200257_init.sql.',
		'Applied 20261029230454_add_priority.sql.',
		'Applied 20261029230505_first_tasks_urgent.sql.',
		'Applied 20261029230507_open_tasks_index.sql.',
		'',
	].join('\n'),
})

// On the database DATABASE_URL names: the file's own schema, its tables
// emptied before each test, dropped after the last.
testApplication(app, { database: 'declared' })

it('deletes the tasks with their project', async () => {
	const project = await Project.create(
		{ data: { name: 'Launch', tasks: { create: [{ title: 'Write' }] } } },
		{ as: master },
	)
	await Project.delete({ where: { id: project.id } }, { as: master })
	expect(await Task.count({}, { as: master })).toBe(0)
})

it('starts with empty tables', async () => {
	expect(await Project.count({}, { as: master })).toBe(0)
})

const app = defineApp({
	database: postgres({
		url: process.env['DATABASE_URL'],
		// At most 20 queries at once; the next waits up to 10 s for one.
		connections: 20,
		// Words stemmed as English: "running" finds "runs".
		searchLanguage: 'english',
	}),
	models: [Article],
})

await Article.create(
	{ data: { body: 'She runs every morning' } },
	{ as: master },
)
const found = await Article.findMany(
	{ where: { _search: 'running' } },
	{ as: master },
)
expect(found.map((article) => article.body)).toEqual(['She runs every morning'])