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

NicoOP•2mo ago

Wanted to bump this to see if anyone has ideas

Vuong Pham•2mo 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)

Gaming

Programming

Subqueries in insert statements