Drizzle Team•2mo ago

How would you write this SQL query using Drizzle?

Hey! This is my first time using Drizzle. I have the following SQL query, which returns the ID for "my_name" (which I expect to return most of the time). If the name doesn't exist, it will insert it into the table and return the new ID. It's one call to the database and avoids trying to INSERT only if the value doesn't exist. How do I write this using Drizzle?

WITH ins AS (
    INSERT INTO names (name)
    VALUES ('my_name')
    ON CONFLICT (name) DO NOTHING
    RETURNING id
)
SELECT COALESCE(
    (SELECT id FROM your_table WHERE name = 'your_name'),
    (SELECT id FROM ins)
);

WITH ins AS (
    INSERT INTO names (name)
    VALUES ('my_name')
    ON CONFLICT (name) DO NOTHING
    RETURNING id
)
SELECT COALESCE(
    (SELECT id FROM your_table WHERE name = 'your_name'),
    (SELECT id FROM ins)
);

1 Reply

Mike JOP•2mo ago

I realise I have made this more complicated than it needs to be. Because I expect select to return something most of the time, it's far simpler to just do a select and if I get null do a second query to insert. The COALESCE didn't do what I thought it doesn't stop the CTE being executed

Gaming

Programming

How would you write this SQL query using Drizzle?