In the realm of database management, version control, and deployment are crucial. Efficiently deploying and managing database functions is vital for maintaining the integrity of your data-driven applications. While database migrations, as detailed in Supabase's migration guide, are ideal for long-term projects, there are scenarios, such as prototyping and rapid development, where you need more flexibility.
In this blog post, we'll explore an approach tailored for quick prototyping and agile development—how to easily deploy and rollback PostgreSQL functions using Supabase. Supabase, a powerful open-source alternative to traditional database management systems, simplifies the process of deploying and managing functions in these scenarios.
If you're working on more complex workflows or long-term projects, we highly recommend referring to Supabase's migration guide for optimal version control and deployment practices.
PostgreSQL and Supabase in Modern Web Applications
PostgreSQL, a robust open-source relational database management system (RDBMS), has gained popularity in web development due to its reliability, extensibility, and support for complex data types.
Supabase, an open-source platform, offers various tools and services for modern web applications. It leverages PostgreSQL as its core database engine and provides a user-friendly interface for managing data, authentication, and more.
We'll explore how Supabase complements PostgreSQL by simplifying function deployment and rollback. You can refer to the PostgreSQL Documentation to learn more about PostgreSQL.
Tracking Function History in PostgreSQL
When managing a PostgreSQL database, it's essential to track changes made to functions over time. This historical record allows you to review, audit, and revert to previous versions if needed.
To facilitate this, we'll create an archive.function_history table that stores crucial information about each function, including its name, arguments, return type, source code, and language settings.
Here's the SQL code for creating this table:
CREATE SCHEMA archive;
CREATE TABLE archive.function_history (
id BIGINT GENERATED BY DEFAULT AS IDENTITY,
schema_name text,
function_name text,
args text,
return_type text,
source_code text,
lang_settings text,
updated_at timestampz DEFAULT now(),
version NUMERIC DEFAULT 1,
CONSTRAINT function_history_pkey PRIMARY KEY (id)
);
--Handling version numbers automatically:
CREATE OR REPLACE FUNCTION calculate_version()
RETURNS TRIGGER AS $$
BEGIN
-- Calculate the version number for new rows
SELECT COALESCE(MAX(version), 0) + 1
INTO NEW.version
FROM archive.function_history
WHERE schema_name = NEW.schema_name
AND function_name = NEW.function_name
AND return_type = NEW.return_type
AND args = NEW.args;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER before_insert_function_history
BEFORE INSERT ON archive.function_history
FOR EACH ROW
EXECUTE FUNCTION calculate_version();
Saving Function History
The archive.save_function_history Function
To automate recording function changes, we'll create a PostgreSQL function called archive.save_function_history. This function takes parameters such as the function name, arguments, return type, source code, schema name, and language settings.
Here's the SQL code for creating the archive.save_function_history function:
CREATE OR REPLACE
FUNCTION archive.save_function_history(
function_name text,
args text,
return_type text,
source_code text,
schema_name text default 'public',
lang_settings text default 'plpgsql'
) RETURNS void
SET search_path = public, archive
SECURITY DEFINER
AS
$$
BEGIN
INSERT INTO archive.function_history (
schema_name,
function_name,
args,
return_type,
source_code,
lang_settings)
VALUES (schema_name, function_name, args, return_type, source_code, lang_settings);
END;
$$
LANGUAGE plpgsql;
-- Protecting the function:
REVOKE EXECUTE ON FUNCTION
archive.save_function_history FROM public;
REVOKE EXECUTE ON FUNCTION
archive.save_function_history FROM anon, authenticated;
This function allows us to easily store a snapshot of a function each time it's modified.
Deploying Functions from Source
The create_function_from_source Function
Managing functions often involves deploying them from source code. PostgreSQL requires specific syntax for function creation, and Supabase simplifies this with the create_function_from_source function.
CREATE OR REPLACE FUNCTION
create_function_from_source(
function_text text,
schema_name text default 'public'
) RETURNS text
SECURITY DEFINER
AS $$
DECLARE
function_name text;
argument_types text;
return_type text;
function_source text;
lang_settings text;
BEGIN
-- Execute the function text to create the function
EXECUTE function_text;
-- Extract function name from function text
SELECT (regexp_matches(function_text, 'create (or replace )?function (public\.)?(\w+)', 'i'))[3]
INTO function_name;
-- Get function details from the system catalog
SELECT pg_get_function_result(p.oid),
pg_get_function_arguments(p.oid), p.prosrc, l.lanname
INTO return_type, argument_types, function_source, lang_settings
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l ON l.oid = p.prolang
WHERE n.nspname = schema_name AND p.proname = function_name;
-- Save function history
PERFORM archive.save_function_history(function_name, argument_types, return_type, function_text, schema_name, lang_settings);
RETURN 'Function created successfully.';
EXCEPTION
WHEN others THEN
RAISE EXCEPTION 'Error creating function: %', sqlerrm;
END;
$$ LANGUAGE plpgsql;
-- Protecting the function:
REVOKE EXECUTE ON FUNCTION
create_function_from_source FROM public;
REVOKE EXECUTE ON FUNCTION
create_function_from_source FROM anon, authenticated;
This function takes the function's SQL source code and schema name as parameters, creating the function within the database. It's a powerful tool for dynamic function creation.
Here's an example of deploying a function using create_function_from_source:
SELECT create_function_from_source(
$$
-- Note that you can just paste the function below:
CREATE OR REPLACE FUNCTION public.convert_to_uuid(input_value text)
RETURNS uuid
AS $function$
DECLARE
hash_hex text;
BEGIN
-- Return null if input_value is null or an empty string
IF input_value IS NULL OR NULLIF(input_value, '') IS NULL THEN
RETURN NULL;
END IF;
hash_hex := substring(encode(digest(input_value::bytea, 'sha512'), 'hex'), 1, 36);
RETURN (left(hash_hex, 8) || '-' || right(hash_hex, 4) || '-4' || right(hash_hex, 3) || '-a' || right(hash_hex, 3) || '-' || right(hash_hex, 12))::uuid;
END;
$function$
LANGUAGE plpgsql
IMMUTABLE
SECURITY DEFINER;
-- End of the function above
$$
);
Rolling Back Functions
Rolling back functions is as crucial as deploying them. Mistakes happen, and being able to revert to a previous version can save valuable time and prevent data corruption.
The rollback_function function comes to the rescue. It retrieves the most recent function version from the archive.function_history table and executes it. If no previous version exists, it gracefully handles the situation.
Here's the SQL code for creating and using the rollback_function:
CREATE OR REPLACE FUNCTION rollback_function(
func_name text,
schema_n text default 'public'
) RETURNS text
SECURITY DEFINER
AS $$
DECLARE
function_text text;
BEGIN
-- Get the most recent function version from the function_history table
SELECT source_code
INTO function_text
FROM archive.function_history
WHERE function_name = func_name AND schema_name = schema_n
ORDER BY updated_at DESC
LIMIT 1;
-- If no previous version is found, raise an error
IF function_text IS NULL THEN
RAISE EXCEPTION 'No previous version of function % found.', func_name;
END IF;
-- Add 'or replace' to the function text if it's not already there (case-insensitive search and replace)
IF NOT function_text ~* 'or replace' THEN
function_text := regexp_replace(function_text, 'create function', 'create or replace function', 'i');
END IF;
-- Drop current version:
EXECUTE format('DROP FUNCTION IF EXISTS %I.%I', schema_n, func_name);
-- Execute the function text to create the function
EXECUTE function_text;
RETURN 'Function rolled back successfully.';
EXCEPTION
WHEN others THEN
RAISE EXCEPTION 'Error rolling back function: %', sqlerrm;
END;
$$ LANGUAGE plpgsql;
-- Protecting the function:
REVOKE EXECUTE ON FUNCTION rollback_function FROM public;
REVOKE EXECUTE ON FUNCTION rollback_function FROM anon, authenticated;
-- Example of rolling back a function
SELECT rollback_function('convert_to_uuid');
Setting up existing functions as the first version
If you are starting with an existing database but want to start versioning from now. You can use this function below to archive all in the public schema.
CREATE OR REPLACE FUNCTION archive.setup_function_history(schema_name text default 'public')
RETURNS VOID AS
$$
DECLARE
function_record record;
BEGIN
-- Loop through existing functions in the specified schema
FOR function_record IN (
SELECT
n.nspname AS schema_name,
p.proname AS function_name,
pg_catalog.pg_get_function_arguments(p.oid) AS args,
pg_catalog.pg_get_function_result(p.oid) AS return_type,
pg_catalog.pg_get_functiondef(p.oid) AS source_code,
l.lanname AS lang_settings
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE n.nspname = schema_name
)
LOOP
-- Insert information about the function into the history table
PERFORM archive.save_function_history(
function_record.function_name,
function_record.args,
function_record.return_type,
function_record.source_code,
function_record.schema_name,
function_record.lang_settings
);
END LOOP;
END;
$$
LANGUAGE plpgsql;
SELECT archive.setup_function_history();
Conclusion
In conclusion, efficient management of PostgreSQL functions is crucial for web application development. Supabase, with its integration with PostgreSQL and the tools we've explored, offers a streamlined approach to function deployment and rollback.
Key takeaways from this blog post include the importance of function history tracking, the creation of the archive.function_history table, the archive.save_function_history function for recording changes, and the convenience of create_function_from_text and
rollback_function for deployment and rollback.
If you found this article valuable, you might also be interested in exploring related topics:
Rate Limiting Supabase Requests with PostgreSQL and pg_headerkit: Learn how to implement rate limiting for Supabase requests.
Comprehensive Guide: Deploying and Debugging Custom Webhooks on Supabase & PostgreSQL: Explore techniques for deploying and debugging custom webhooks.
We encourage you to explore Supabase and PostgreSQL further to unlock the full potential of efficient database management.
Additional Resources
For further information and exploration, here are some additional resources:
Feel free to delve deeper into these resources to enhance your understanding of these powerful tools for database management.
Top comments (0)