Natively write SQL functions/triggers with Drizzle?

Is it possible to, for example, write this kind of logic directly in Drizzle's schema?

-- Checks if a user already has an active ban before inserting a new one.
CREATE OR REPLACE FUNCTION check_active_ban()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if there is an existing ban for the same user_id with expire NULL or in the future
    IF EXISTS (
        SELECT 1 FROM bans
        WHERE "userId" = NEW."userId"
        AND (expires IS NULL OR expires > CURRENT_TIMESTAMP)
    ) THEN
        -- Raise an exception to prevent the insert
        RAISE EXCEPTION 'A ban for this user already exists and is not expired';
    END IF;
    -- Allow the insert
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-------------------------------- Triggers --------------------------------

CREATE OR REPLACE TRIGGER trigger_before_bans_insert
BEFORE INSERT ON bans
FOR EACH ROW
EXECUTE FUNCTION check_active_ban();

-- Checks if a user already has an active ban before inserting a new one.
CREATE OR REPLACE FUNCTION check_active_ban()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if there is an existing ban for the same user_id with expire NULL or in the future
    IF EXISTS (
        SELECT 1 FROM bans
        WHERE "userId" = NEW."userId"
        AND (expires IS NULL OR expires > CURRENT_TIMESTAMP)
    ) THEN
        -- Raise an exception to prevent the insert
        RAISE EXCEPTION 'A ban for this user already exists and is not expired';
    END IF;
    -- Allow the insert
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-------------------------------- Triggers --------------------------------

CREATE OR REPLACE TRIGGER trigger_before_bans_insert
BEFORE INSERT ON bans
FOR EACH ROW
EXECUTE FUNCTION check_active_ban();

6 Replies

patoOP•3mo ago

bump

theBarracuda•3mo ago

don't think so. Bump anyways

patoOP•3mo ago

bump

Anthony•3mo ago

No, this would require a custom migration

patoOP•3mo ago

this is basically adding a constraint tho if u read the function's body, it's preventing a new row from being created if a certain condition I was gonna swear I've seen native drizzle code for that but I cant remember where

Anthony•3mo ago

@pato see the Check option here if it fits your needs https://orm.drizzle.team/docs/indexes-constraints#check

Drizzle ORM - Indexes & Constraints

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

Natively write SQL functions/triggers with Drizzle?