2 years ago
Is it possible to clear my database data automatically every night at midnight? We are running a showcase type app using our web framework, and want to clear data.
Also - with that being said, another user has "edit" access and they can do most everything else but can't wipe the volume.
We can do other things but I'm wondering if its available for us via the UI
https://fullstack.litestar.dev/
1 Replies
2 years ago
6f1f252f-a6f4-42ab-a5c7-6ba05c3f74de
2 years ago
users with edit permissions can't do destructive actions.
as for the wipe, you could write a script that uses the public api to wipe the volume of the database and then run have railway run that as a cron job
a year ago
Postgresql?
CREATE EXTENSION pg_cron;
SELECT cron.schedule('clear_data', '0 0 * * *', $$
DELETE FROM your_table_name;
-- Add more SQL commands here if you need to clear data from multiple tables
$$);
-- Delete all tables
SELECT cron.schedule('wipe_db', '0 0 * * *', $$
DO $$ DECLARE
r RECORD;
BEGIN
-- Drop all tables
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
-- DROP or DELETE, choose which one is best for your use case
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
-- Optionally, you can re-create the tables here
-- EXECUTE 'CREATE TABLE your_table_name (...);'
END $$;
$$);
-- Log + Monitor
CREATE TABLE cron_log (
log_time timestamp with time zone DEFAULT now(),
message text
);
-- Update the cron job to log the output
SELECT cron.schedule('clear_data', '0 0 * * *', $$
DELETE FROM your_table_name RETURNING 'Data cleared at ' || now();
$$, 'INSERT INTO cron_log (message) VALUES ($1)');