identity / autoincrement fields are not ignored by seed.sql

[rodpatulski]

Using this code

import { SnapletClient } from '@snaplet/seed';

// You can use @snaplet/copycat to generate fake data for a field, for example:
// ```
// await snaplet.users([{ email: ({ seed }) => copycat.email(seed) }])
// ```
// More on this in our docs: https://docs.snaplet.dev/core-concepts/seed#inside-the-snapletseed-workflow
import { copycat } from '@snaplet/copycat'

// This is a basic example generated by Snaplet to start you off, check out the docs for where to go from here
// * For more on getting started with @snaplet/seed: https://docs.snaplet.dev/getting-started/quick-start/seed
// * For a more detailed reference: https://docs.snaplet.dev/core-concepts/seed


const snaplet = new SnapletClient({
  dryRun: process.env.DRY !== '0',
});

// Clears all existing data in the database, but keep the structure
await snaplet.$resetDatabase()

await snaplet.users([
  {
    purchases: (x) => x(3, (index) => ({
      amount: ({ seed }) => copycat.int(seed, { min: 1, max: 100 }),
      description: copycat.sentence(index),
    }))
  }
]);

//await snaplet.purchases([{}], { connect: true }); // should create a purchase per user?

The sql inserts random integers into a order_id column (that is used for sorting). I want it to just ignore that field since the database takes care of setting the value. I looked everywhere how to ignore/not set any fields in a column to no avail. Any help would be appreciated?

[peterp]

@justinvdm or @jgoux is it possible to pass in a null value here any let the database take care of this?

[jgoux]

Hello @rodpatulski, could you share the schema of the table containing order_id?
Normally if there is a DEFAULT value for the column we shouldn't produce a value on our end if you don't specify anything.

[rodpatulski]

Here is the schema:

-- PostgreSQL database dump
--

-- Dumped from database version 15.1 (Ubuntu 15.1-1.pgdg20.04+1)
-- Dumped by pg_dump version 15.2

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: public; Type: SCHEMA; Schema: -; Owner: pg_database_owner
--

CREATE SCHEMA public;


ALTER SCHEMA public OWNER TO pg_database_owner;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: pg_database_owner
--

COMMENT ON SCHEMA public IS 'standard public schema';


--
-- Name: handle_new_user(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.handle_new_user() RETURNS trigger
    LANGUAGE plpgsql
    AS $$begin

insert into public.profiles (id, full_name, avatar_url)

values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');

return new;

end;$$;


ALTER FUNCTION public.handle_new_user() OWNER TO postgres;

--
-- Name: running_purchase_total(uuid, boolean); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.running_purchase_total(usr_id uuid, isweek boolean) RETURNS TABLE(purchase_date date, cumulative_amount numeric)
    LANGUAGE plpgsql
    AS $$
DECLARE 
    user_budget NUMERIC;
BEGIN
    -- Retrieve the daily budget for the user from the preferences table
    SELECT daily_budget INTO user_budget
    FROM preferences
    WHERE user_id = usr_id;

    IF NOT FOUND THEN
        user_budget := 20;
    END IF;

    ASSERT user_budget IS NOT NULL, 'user_budget cannot be NULL for user_id: ' || usr_id;
    ASSERT user_budget >= 0, 'user_budget cannot be negative or 0 for user_id: ' || usr_id;

    RETURN QUERY
    WITH date_series AS (
        SELECT
            generate_series(
                CASE
                    WHEN isWeek THEN date_trunc('week', CURRENT_DATE)
                    ELSE date_trunc('month', CURRENT_DATE)
                END,
                CASE
                    WHEN isWeek THEN date_trunc('week', CURRENT_DATE) + interval '6 days'
                    ELSE date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day'
                END,
                interval '1 day'
            )::date AS purchase_date
    ),
    daily_purchase AS (
        SELECT
            ds.purchase_date,
            COALESCE(SUM(P.amount), 0) AS total_purchase_amount
        FROM
            date_series ds
        LEFT JOIN
            purchases P ON ds.purchase_date = DATE(P.created_at) AND P.user_id = usr_id
        GROUP BY
            ds.purchase_date  -- Group by the date from the date_series
    )
    SELECT
        DP.purchase_date,
        SUM(user_budget - DP.total_purchase_amount) OVER (ORDER BY DP.purchase_date) AS cumulative_amount
    FROM
        daily_purchase DP;
END;
$$;


ALTER FUNCTION public.running_purchase_total(usr_id uuid, isweek boolean) OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: preferences; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.preferences (
    user_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    daily_budget smallint DEFAULT '20'::smallint
);


ALTER TABLE public.preferences OWNER TO postgres;

--
-- Name: purchases; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.purchases (
    created_at date DEFAULT now() NOT NULL,
    amount smallint DEFAULT '0'::smallint NOT NULL,
    description character varying DEFAULT ''::character varying NOT NULL,
    user_id uuid NOT NULL,
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    order_id integer NOT NULL
);


ALTER TABLE public.purchases OWNER TO postgres;

--
-- Name: purchases_order_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

ALTER TABLE public.purchases ALTER COLUMN order_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.purchases_order_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Data for Name: preferences; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.preferences (user_id, created_at, daily_budget) FROM stdin;
\.


--
-- Data for Name: purchases; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.purchases (created_at, amount, description, user_id, id, order_id) FROM stdin;
2024-01-24	78	Discens sibus possitatque fatem quos.	0f5d546a-dd80-5406-a005-a4f3061b9fb4	889b70dc-0238-5556-9f83-b458c43d10c5	0
2024-01-24	30	Advero quisquamquam inquam ii possit est dolorem synephebos.	0f5d546a-dd80-5406-a005-a4f3061b9fb4	9ffb4f71-471c-59cf-b48d-de0f11935ce7	1
2024-01-24	4	Dicunt ut nulla in odia qui doctis et, recte quisitural ita beate sitata voluptatem nostri quoniam.	0f5d546a-dd80-5406-a005-a4f3061b9fb4	4099a4ff-334f-5bfe-8f49-4e5a5b58abd9	2
2024-01-24	4	Discens sibus possitatque fatem quos.	5fef9e7c-116a-5504-a6ae-d16f4442d448	538057a3-fc03-5231-af06-2f4f0ab34f00	3
2024-01-24	41	Advero quisquamquam inquam ii possit est dolorem synephebos.	5fef9e7c-116a-5504-a6ae-d16f4442d448	1d593d16-5015-5afd-bf57-bee4fe94b479	4
2024-01-24	65	Dicunt ut nulla in odia qui doctis et, recte quisitural ita beate sitata voluptatem nostri quoniam.	5fef9e7c-116a-5504-a6ae-d16f4442d448	00842d54-aa98-5a96-ac30-0354afaa2745	5
\.


--
-- Name: purchases_order_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.purchases_order_id_seq', 5, true);


--
-- Name: preferences preferences_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.preferences
    ADD CONSTRAINT preferences_pkey PRIMARY KEY (user_id);


--
-- Name: purchases purchases_order_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.purchases
    ADD CONSTRAINT purchases_order_id_key UNIQUE (order_id);


--
-- Name: purchases purchases_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.purchases
    ADD CONSTRAINT purchases_pkey PRIMARY KEY (id);


--
-- Name: preferences preferences_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.preferences
    ADD CONSTRAINT preferences_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE;


--
-- Name: purchases purchases_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.purchases
    ADD CONSTRAINT purchases_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE;


--
-- Name: purchases Enable all actions for users based on user_id; Type: POLICY; Schema: public; Owner: postgres
--

CREATE POLICY "Enable all actions for users based on user_id" ON public.purchases TO authenticated USING ((auth.uid() = user_id)) WITH CHECK ((auth.uid() = user_id));


--
-- Name: preferences; Type: ROW SECURITY; Schema: public; Owner: postgres
--

ALTER TABLE public.preferences ENABLE ROW LEVEL SECURITY;

--
-- Name: purchases; Type: ROW SECURITY; Schema: public; Owner: postgres
--

ALTER TABLE public.purchases ENABLE ROW LEVEL SECURITY;

--
-- Name: preferences specific_user_crud_on_preferences; Type: POLICY; Schema: public; Owner: postgres
--

CREATE POLICY specific_user_crud_on_preferences ON public.preferences TO authenticated USING ((auth.uid() = user_id)) WITH CHECK ((auth.uid() = user_id));


--
-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: pg_database_owner
--

GRANT USAGE ON SCHEMA public TO postgres;
GRANT USAGE ON SCHEMA public TO anon;
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT USAGE ON SCHEMA public TO service_role;


--
-- Name: FUNCTION handle_new_user(); Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON FUNCTION public.handle_new_user() TO anon;
GRANT ALL ON FUNCTION public.handle_new_user() TO authenticated;
GRANT ALL ON FUNCTION public.handle_new_user() TO service_role;


--
-- Name: FUNCTION running_purchase_total(usr_id uuid, isweek boolean); Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON FUNCTION public.running_purchase_total(usr_id uuid, isweek boolean) TO anon;
GRANT ALL ON FUNCTION public.running_purchase_total(usr_id uuid, isweek boolean) TO authenticated;
GRANT ALL ON FUNCTION public.running_purchase_total(usr_id uuid, isweek boolean) TO service_role;


--
-- Name: TABLE preferences; Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON TABLE public.preferences TO anon;
GRANT ALL ON TABLE public.preferences TO authenticated;
GRANT ALL ON TABLE public.preferences TO service_role;


--
-- Name: TABLE purchases; Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON TABLE public.purchases TO anon;
GRANT ALL ON TABLE public.purchases TO authenticated;
GRANT ALL ON TABLE public.purchases TO service_role;


--
-- Name: SEQUENCE purchases_order_id_seq; Type: ACL; Schema: public; Owner: postgres
--

GRANT ALL ON SEQUENCE public.purchases_order_id_seq TO anon;
GRANT ALL ON SEQUENCE public.purchases_order_id_seq TO authenticated;
GRANT ALL ON SEQUENCE public.purchases_order_id_seq TO service_role;


--
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: public; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES  TO postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES  TO anon;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES  TO authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES  TO service_role;


--
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: public; Owner: supabase_admin
--

ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON SEQUENCES  TO postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON SEQUENCES  TO anon;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON SEQUENCES  TO authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON SEQUENCES  TO service_role;


--
-- Name: DEFAULT PRIVILEGES FOR FUNCTIONS; Type: DEFAULT ACL; Schema: public; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON FUNCTIONS  TO postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON FUNCTIONS  TO anon;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON FUNCTIONS  TO authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON FUNCTIONS  TO service_role;


--
-- Name: DEFAULT PRIVILEGES FOR FUNCTIONS; Type: DEFAULT ACL; Schema: public; Owner: supabase_admin
--

ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON FUNCTIONS  TO postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON FUNCTIONS  TO anon;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON FUNCTIONS  TO authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON FUNCTIONS  TO service_role;


--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: public; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES  TO postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES  TO anon;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES  TO authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES  TO service_role;


--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: public; Owner: supabase_admin
--

ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON TABLES  TO postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON TABLES  TO anon;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON TABLES  TO authenticated;
ALTER DEFAULT PRIVILEGES FOR ROLE supabase_admin IN SCHEMA public GRANT ALL ON TABLES  TO service_role;


--
-- PostgreSQL database dump complete
--

I can see that there is no DEFAULT but that supabase generated an identity section for the order_id column. (This was done in the supabase interface by checking the 'identity' option in column options when editing the table in the supabase gui). Hope this helps.

[avallete]

I this bug still occurring on latest version @rodpatulski ?

We should now continue the sequence assigned to a column so I'm wondering if it fixed your bug.

[peterp]

I'm going to close this due to lack of response. If this is still happening please let us know and we'll open it up again.

[rossPatton]

Not sure if this is the same behavior, but snaplet seems to break when seeding tables that have a generative field.

Anything DB generated really is difficult. I had to manually randomize an int for increments, which is how I worked around that issue, but I cannot get it to ignore a fullName generated column to save my life

It's autogenerated by the db. It doesn't need to be created, and this is something that prisma itself (which I am using with snaplet) doesn't seem to have any difficulties with.

In the data model json, I've tried manually setting it as generated, given it a default value, nothing works. It always tries to generate random names

I could work around this with a trigger, which I've seen suggested a few times but that feels like a convoluted approach to me