The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
import { safeSql } from '@supabase/pg-meta'
import { DatabaseIcon, PlusIcon, SearchIcon } from 'lucide-react'
import { parseAsBoolean, parseAsJson, parseAsString, useQueryState } from 'nuqs'
import { useEffect, useRef, useState } from 'react'
import { toast } from 'sonner'
import {
AiIconAnimation,
Button,
Card,
Table,
TableBody,
TableHead,
TableHeader,
TableRow,
} from 'ui'
import { Input } from 'ui-patterns/DataInputs/Input'
import { EmptyStatePresentational } from 'ui-patterns/EmptyStatePresentational'
import { GenericSkeletonLoader } from 'ui-patterns/ShimmeringLoader'
import { ProtectedSchemaWarning } from '../../ProtectedSchemaWarning'
import { FunctionList } from './FunctionList'
import { useIsInlineEditorEnabled } from '@/components/interfaces/Account/Preferences/useDashboardSettings'
import { CreateFunction } from '@/components/interfaces/Database/Functions/CreateFunction'
import {
ReportsSelectFilter,
selectFilterSchema,
} from '@/components/interfaces/Reports/v2/ReportsSelectFilter'
import { SIDEBAR_KEYS } from '@/components/layouts/ProjectLayout/LayoutSidebar/LayoutSidebarProvider'
import { AlertError } from '@/components/ui/AlertError'
import { ButtonTooltip } from '@/components/ui/ButtonTooltip'
import { SchemaSelector } from '@/components/ui/SchemaSelector'
import { Shortcut } from '@/components/ui/Shortcut'
import { TextConfirmModal } from '@/components/ui/TextConfirmModalWrapper'
import { useDatabaseFunctionDeleteMutation } from '@/data/database-functions/database-functions-delete-mutation'
import type { SavedDatabaseFunction } from '@/data/database-functions/database-functions-query'
import { useDatabaseFunctionsQuery } from '@/data/database-functions/database-functions-query'
import { useSchemasQuery } from '@/data/database/schemas-query'
import { FGA_PERMISSIONS, useAsyncCheckPermissionsV2 } from '@/hooks/misc/useCheckPermissionsV2'
import { useQuerySchemaState } from '@/hooks/misc/useSchemaQueryState'
import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
import { useIsProtectedSchema } from '@/hooks/useProtectedSchemas'
import { onSearchInputEscape } from '@/lib/keyboard'
import { useAiAssistantStateSnapshot } from '@/state/ai-assistant-state'
import { useEditorPanelStateSnapshot } from '@/state/editor-panel-state'
import { SHORTCUT_IDS } from '@/state/shortcuts/registry'
import { useShortcut } from '@/state/shortcuts/useShortcut'
import { useSidebarManagerSnapshot } from '@/state/sidebar-manager-state'
const createFunctionSnippet = safeSql`create function function_name()
returns void
language plpgsql
as $$
begin
-- Write your function logic here
end;
$$;`
export const FunctionsList = () => {
const { data: project } = useSelectedProjectQuery()
const aiSnap = useAiAssistantStateSnapshot()
const { openSidebar } = useSidebarManagerSnapshot()
const { selectedSchema, setSelectedSchema } = useQuerySchemaState()
const isInlineEditorEnabled = useIsInlineEditorEnabled()
const {
setValue: setEditorPanelValue,
setTemplates: setEditorPanelTemplates,
setInitialPrompt: setEditorPanelInitialPrompt,
} = useEditorPanelStateSnapshot()
const createFunction = () => {
setSelectedFunctionIdToDuplicate(null)
if (isInlineEditorEnabled) {
setEditorPanelInitialPrompt('Create a new database function that...')
setEditorPanelValue(createFunctionSnippet)
setEditorPanelTemplates([])
openSidebar(SIDEBAR_KEYS.EDITOR_PANEL)
} else {
setShowCreateFunctionForm(true)
}
}
const duplicateFunction = (fn: SavedDatabaseFunction) => {
if (isInlineEditorEnabled) {
const dupFn = {
...fn,
name: `${fn.name}_duplicate`,
}
setEditorPanelInitialPrompt('Create new database function that...')
setEditorPanelValue(dupFn.complete_statement)
setEditorPanelTemplates([])
openSidebar(SIDEBAR_KEYS.EDITOR_PANEL)
} else {
setSelectedFunctionIdToDuplicate(fn.id.toString())
}
}
const editFunction = (fn: SavedDatabaseFunction) => {
setSelectedFunctionIdToDuplicate(null)
if (isInlineEditorEnabled) {
setEditorPanelValue(fn.complete_statement)
setEditorPanelTemplates([])
openSidebar(SIDEBAR_KEYS.EDITOR_PANEL)
} else {
setSelectedFunctionToEdit(fn.id.toString())
}
}
const [filterString, setFilterString] = useQueryState(
'search',
parseAsString.withDefault('').withOptions({ clearOnDefault: true })
)
// Filters
const [returnTypeFilter, setReturnTypeFilter] = useQueryState(
'return_type',
parseAsJson(selectFilterSchema.parse)
)
const [securityFilter, setSecurityFilter] = useQueryState(
'security',
parseAsJson(selectFilterSchema.parse)
)
const [schemaSelectorOpen, setSchemaSelectorOpen] = useState(false)
const searchInputRef = useRef<HTMLInputElement>(null)
const { can: canCreateFunctions } = useAsyncCheckPermissionsV2(
FGA_PERMISSIONS.PROJECT.DATABASE_WRITE
)
const { isSchemaLocked } = useIsProtectedSchema({ schema: selectedSchema })
useShortcut(
SHORTCUT_IDS.LIST_PAGE_FOCUS_SEARCH,
() => {
searchInputRef.current?.focus()
searchInputRef.current?.select()
},
{ label: 'Search functions' }
)
useShortcut(SHORTCUT_IDS.LIST_PAGE_RESET_FILTERS, () => {
setFilterString('')
setReturnTypeFilter(null)
setSecurityFilter(null)
})
// [Joshen] This is to preload the data for the Schema Selector
useSchemasQuery({
projectRef: project?.ref,
connectionString: project?.connectionString,
})
const {
data: functions = [],
error,
isPending: isLoading,
isError,
isSuccess,
} = useDatabaseFunctionsQuery({
projectRef: project?.ref,
connectionString: project?.connectionString,
schema: selectedSchema,
})
// Get unique return types from functions in the selected schema
const schemaFunctions = functions.filter((fn) => fn.schema === selectedSchema)
const uniqueReturnTypes = Array.from(new Set(schemaFunctions.map((fn) => fn.return_type)))
.filter(Boolean)
.sort()
// Get security options based on what exists in the selected schema
const hasDefiner = schemaFunctions.some((fn) => fn.security_definer)
const hasInvoker = schemaFunctions.some((fn) => !fn.security_definer)
const securityOptions = [
...(hasDefiner ? [{ label: 'Definer', value: 'definer' }] : []),
...(hasInvoker ? [{ label: 'Invoker', value: 'invoker' }] : []),
]
const [showCreateFunctionForm, setShowCreateFunctionForm] = useQueryState(
'new',
parseAsBoolean.withDefault(false).withOptions({ history: 'push', clearOnDefault: true })
)
const [functionIdToEdit, setSelectedFunctionToEdit] = useQueryState('edit', parseAsString)
const functionToEdit = functions.find((fn) => fn.id.toString() === functionIdToEdit)
const [functionIdToDuplicate, setSelectedFunctionIdToDuplicate] = useQueryState(
'duplicate',
parseAsString
)
const functionToDuplicate = functions.find((fn) => fn.id.toString() === functionIdToDuplicate)
const [functionIdToDelete, setSelectedFunctionToDelete] = useQueryState('delete', parseAsString)
const functionToDelete = functions.find((fn) => fn.id.toString() === functionIdToDelete)
const {
mutate: deleteDatabaseFunction,
isPending: isDeletingFunction,
isSuccess: isSuccessDelete,
} = useDatabaseFunctionDeleteMutation({
onSuccess: (_, variables) => {
toast.success(`Successfully removed function ${variables.func.name}`)
setSelectedFunctionToDelete(null)
},
})
const onDeleteFunction = () => {
if (!project) return console.error('Project is required')
if (!functionToDelete) return console.error('Function is required')
deleteDatabaseFunction({
func: functionToDelete,
projectRef: project.ref,
connectionString: project.connectionString,
})
}
useEffect(() => {
if (isSuccess && !!functionIdToEdit && !functionToEdit) {
toast('Function not found')
setSelectedFunctionToEdit(null)
}
}, [functionIdToEdit, functionToEdit, isSuccess, setSelectedFunctionToEdit])
useEffect(() => {
if (isSuccess && !!functionIdToDuplicate && !functionToDuplicate) {
toast('Function not found')
setSelectedFunctionIdToDuplicate(null)
}
}, [functionIdToDuplicate, functionToDuplicate, isSuccess, setSelectedFunctionIdToDuplicate])
useEffect(() => {
if (isSuccess && !!functionIdToDelete && !functionToDelete && !isSuccessDelete) {
toast('Function not found')
setSelectedFunctionToDelete(null)
}
}, [
functionIdToDelete,
functionToDelete,
isSuccess,
isSuccessDelete,
setSelectedFunctionToDelete,
])
if (isLoading) return <GenericSkeletonLoader />
if (isError) return <AlertError error={error} subject="Failed to retrieve database functions" />
return (
<>
<div className="w-full space-y-4">
<div className="flex flex-col lg:flex-row lg:items-center justify-between gap-2 flex-wrap">
<div className="flex flex-col lg:flex-row lg:items-center gap-2">
<Shortcut
id={SHORTCUT_IDS.LIST_PAGE_FOCUS_SCHEMA}
onTrigger={() => setSchemaSelectorOpen(true)}
side="bottom"
tooltipOpen={schemaSelectorOpen ? false : undefined}
>
<SchemaSelector
className="w-full lg:w-[180px]"
size="tiny"
showError={false}
selectedSchemaName={selectedSchema}
onSelectSchema={(schema) => {
setFilterString('')
setSelectedSchema(schema)
}}
open={schemaSelectorOpen}
onOpenChange={setSchemaSelectorOpen}
/>
</Shortcut>
<Input
ref={searchInputRef}
placeholder="Search for a function"
size="tiny"
icon={<SearchIcon />}
value={filterString}
className="w-full lg:w-52"
onChange={(e) => setFilterString(e.target.value)}
onKeyDown={onSearchInputEscape(filterString, setFilterString)}
/>
<ReportsSelectFilter
label="Return Type"
options={uniqueReturnTypes.map((type) => ({
label: type,
value: type,
}))}
value={returnTypeFilter ?? []}
onChange={setReturnTypeFilter}
showSearch
/>
<ReportsSelectFilter
label="Security"
options={securityOptions}
value={securityFilter ?? []}
onChange={setSecurityFilter}
/>
</div>
<div className="flex items-center gap-x-2">
{!isSchemaLocked && (
<>
<CreateFunctionButton createFunction={createFunction} />
<ButtonTooltip
disabled={!canCreateFunctions}
className="px-1 pointer-events-auto"
icon={<AiIconAnimation size={16} />}
onClick={() => {
openSidebar(SIDEBAR_KEYS.AI_ASSISTANT)
aiSnap.newChat({
name: 'Create new function',
initialInput: `Create a new function for the schema ${selectedSchema} that does ...`,
})
}}
tooltip={{
content: {
side: 'bottom',
text: !canCreateFunctions
? 'You need additional permissions to create functions'
: 'Create with Supabase Assistant',
},
}}
/>
</>
)}
</div>
</div>
{isSchemaLocked && <ProtectedSchemaWarning schema={selectedSchema} entity="functions" />}
{functions.length === 0 ? (
<>
<EmptyStatePresentational
icon={DatabaseIcon}
title="Add your first function"
description="PostgreSQL functions are a set of SQL and procedural commands such as declarations, assignments, loops, or flow-of-control."
>
<CreateFunctionButton hideIcon variant="default" createFunction={createFunction} />
</EmptyStatePresentational>
</>
) : (
<Card>
<Table className="table-fixed overflow-x-auto">
<TableHeader>
<TableRow>
<TableHead key="name">Name</TableHead>
<TableHead key="type">Type</TableHead>
<TableHead key="arguments" className="table-cell">
Arguments
</TableHead>
<TableHead key="return_type" className="table-cell">
Return type
</TableHead>
<TableHead key="security" className="table-cell w-[100px]">
Security
</TableHead>
<TableHead key="buttons" className="w-1/6"></TableHead>
</TableRow>
</TableHeader>
<TableBody>
<FunctionList
filterString={filterString}
isLocked={isSchemaLocked}
returnTypeFilter={returnTypeFilter ?? []}
securityFilter={securityFilter ?? []}
duplicateFunction={duplicateFunction}
editFunction={editFunction}
deleteFunction={(fn) => setSelectedFunctionToDelete(fn.id.toString())}
/>
</TableBody>
</Table>
</Card>
)}
</div>
<CreateFunction
func={functionToEdit || functionToDuplicate}
visible={showCreateFunctionForm || !!functionToEdit || !!functionToDuplicate}
onClose={() => {
setShowCreateFunctionForm(false)
setSelectedFunctionToEdit(null)
setSelectedFunctionIdToDuplicate(null)
}}
isDuplicating={!!functionToDuplicate}
/>
<TextConfirmModal
variant="destructive"
visible={!!functionToDelete}
onCancel={() => setSelectedFunctionToDelete(null)}
onConfirm={onDeleteFunction}
title="Delete this function"
loading={isDeletingFunction}
confirmLabel={`Delete function ${functionToDelete?.name}`}
confirmPlaceholder="Type in name of function"
confirmString={functionToDelete?.name ?? 'Unknown'}
text={
<>
<span>This will delete the function</span>{' '}
<span className="text-bold text-foreground">{functionToDelete?.name}</span>{' '}
<span>from the schema</span>{' '}
<span className="text-bold text-foreground">{functionToDelete?.schema}</span>
</>
}
alert={{ title: 'You cannot recover this function once deleted.' }}
/>
</>
)
}
const CreateFunctionButton = ({
variant = 'primary',
hideIcon = false,
createFunction,
}: {
variant?: 'default' | 'primary'
hideIcon?: boolean
createFunction: () => void
}) => {
const { selectedSchema } = useQuerySchemaState()
const { can: canCreateFunctions } = useAsyncCheckPermissionsV2(
FGA_PERMISSIONS.PROJECT.DATABASE_WRITE
)
const { isSchemaLocked } = useIsProtectedSchema({ schema: selectedSchema })
const canAddFunctions = canCreateFunctions && !isSchemaLocked
if (canAddFunctions) {
return (
<Shortcut
id={SHORTCUT_IDS.LIST_PAGE_NEW_ITEM}
label="Create new function"
onTrigger={() => createFunction()}
side="bottom"
>
<Button
variant={variant}
className="grow"
onClick={() => createFunction()}
icon={hideIcon ? null : <PlusIcon />}
>
New function
</Button>
</Shortcut>
)
} else {
return (
<ButtonTooltip
disabled
variant={variant}
className="grow"
icon={hideIcon ? null : <PlusIcon />}
tooltip={{
content: {
side: 'bottom',
text: 'You need additional permissions to create functions',
},
}}
>
New function
</ButtonTooltip>
)
}
}