DT
Drizzle Team•4mo ago
Nico

Subqueries in insert statements

I have the following schema (in sqlite) 
CREATE TABLE
    languages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE NOT NULL
    );
    
    -- Create JobTitles Table
CREATE TABLE
    job_titles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        language_id INTEGER,
        FOREIGN KEY (language_id) REFERENCES languages (id),
    );
CREATE TABLE
    languages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        code TEXT UNIQUE NOT NULL
    );
    
    -- Create JobTitles Table
CREATE TABLE
    job_titles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        language_id INTEGER,
        FOREIGN KEY (language_id) REFERENCES languages (id),
    );
When inserting into job_titles, in SQL, I've done the following to get the correct language_id given a language.code: 
-- Insert into JobTitles
INSERT INTO
    job_titles (title, language_id)
VALUES
    (
        'Barmanka',
        (SELECT id FROM languages WHERE code = 'pl-PL'),
    );
-- Insert into JobTitles
INSERT INTO
    job_titles (title, language_id)
VALUES
    (
        'Barmanka',
        (SELECT id FROM languages WHERE code = 'pl-PL'),
    );
Is there a way to do this in Drizzle outside of using the sql function and writing the wrong string? Right now, I've just writen a Typescript function to recreate the functionality but I just want to know if there's other approaches
2 Replies
Nico
NicoOP•3mo ago
Wanted to bump this to see if anyone has ideas
Vuong Pham
Vuong Pham•3mo ago
const subquery = dbClient.select({id}).from(languages).where(eq(code, 'pl-PL')
const subquery = dbClient.select({id}).from(languages).where(eq(code, 'pl-PL')
do not execute it (not add await)

Did you find this page helpful?