FG
💻 Software🗄️ Databases

[FEATURE]: allow INSERT in CTEs (WITH clauses)

Fresh3 days ago
Mar 14, 20260 views
Confidence Score95%
95%

Problem

Describe what you want Drizzle ORM supports `SELECT` queries in CTEs (`WITH` clauses). From the docs: [code block] Currently, Drizzle does not support `INSERT` queries in CTEs. Example of such a query: [code block] As you can see, this would be very useful for nested inserts in a single query and should be supported by Drizzle to be a feature complete SQL query builder.

Unverified for your environment

Select your OS to check compatibility.

2 Fixes

Canonical Fix
Unverified Fix
New Fix – Awaiting Verification

Implement INSERT Support in CTEs for Drizzle ORM

Medium Risk

Drizzle ORM currently does not support INSERT operations within Common Table Expressions (CTEs) due to limitations in the query builder's parsing and execution logic. This restricts users from performing nested inserts in a single query, which is a common SQL feature that enhances performance and reduces the number of database round trips.

Awaiting Verification

Be the first to verify this fix

  1. 1

    Modify Query Builder to Support INSERT in CTEs

    Update the query builder logic to recognize and parse INSERT statements within CTEs. This involves extending the existing parsing rules to accommodate INSERT syntax and ensuring that the generated SQL is valid.

    typescript
    const insertCTE = db.with('cte_name', db.insert('table_name').values({ column1: value1, column2: value2 })).insert('another_table').values({ column_from_cte: db.cte('cte_name').column1 });
  2. 2

    Update SQL Generation Logic

    Ensure that the SQL generation logic can handle the new structure of CTEs that include INSERT statements. This may involve modifying the SQL string construction methods to properly format the INSERT statements within the CTE context.

    typescript
    const sql = `WITH cte_name AS (INSERT INTO table_name (column1, column2) VALUES (${value1}, ${value2})) INSERT INTO another_table (column_from_cte) SELECT column1 FROM cte_name`;
  3. 3

    Implement Unit Tests for CTE INSERT

    Create unit tests to verify that the new functionality works as expected. Tests should cover various scenarios, including multiple CTEs with INSERTs, and ensure that the generated SQL is correct.

    typescript
    it('should generate correct SQL for INSERT in CTE', () => { const query = db.with('cte_name', db.insert('table_name').values({ column1: 'value1', column2: 'value2' })).insert('another_table').values({ column_from_cte: db.cte('cte_name').column1 }); expect(query.toSQL()).toEqual(expectedSQL); });
  4. 4

    Update Documentation

    Revise the Drizzle ORM documentation to include examples and usage guidelines for the new INSERT support in CTEs. This will help users understand how to utilize this feature effectively.

    typescript
    /// Example of using INSERT in CTEs in Drizzle ORM
const query = db.with('cte_name', db.insert('table_name').values({ column1: 'value1', column2: 'value2' })).insert('another_table').values({ column_from_cte: db.cte('cte_name').column1 });

Validation

To confirm the fix, run the unit tests created for the CTE INSERT functionality. Additionally, execute sample queries using INSERT in CTEs and verify that the expected SQL is generated and executed successfully in the database.

Sign in to verify this fix

1 low-confidence fix
Unverified Fix
New Fix – Awaiting Verification

Implement INSERT Support in CTEs for Drizzle ORM

Medium Risk

Drizzle ORM currently does not support INSERT operations within Common Table Expressions (CTEs) due to limitations in its query builder architecture. This restricts users from performing complex nested inserts in a single query, which is a common requirement in SQL databases.

Awaiting Verification

Be the first to verify this fix

  1. 1

    Analyze Current CTE Implementation

    Review the existing implementation of CTEs in Drizzle ORM to identify how SELECT queries are structured and executed. This will provide insights into how INSERT operations can be integrated.

    typescript
    const cteQuery = db.with('cte_name', db.select(...)).select(...);
  2. 2

    Define INSERT CTE Structure

    Create a new method in the query builder that allows for the definition of INSERT operations within CTEs. This should mirror the structure of the existing SELECT CTE method.

    typescript
    const insertCteQuery = db.with('cte_name', db.insert('table_name').values(...)).select(...);
  3. 3

    Implement Query Execution Logic

    Modify the query execution logic to handle INSERT statements within CTEs. Ensure that the generated SQL syntax is valid and adheres to the SQL standard for CTEs.

    typescript
    const sql = `WITH cte_name AS (INSERT INTO table_name (col1, col2) VALUES (val1, val2)) SELECT * FROM cte_name`; db.execute(sql);
  4. 4

    Update Documentation

    Revise the Drizzle ORM documentation to include examples and usage guidelines for the new INSERT CTE feature. This will help users understand how to leverage this functionality effectively.

    typescript
    // Example usage of INSERT in CTE
const query = db.with('cte', db.insert('users').values({name: 'John', age: 30})).select('*').from('cte');
  5. 5

    Conduct Testing

    Develop unit tests and integration tests to ensure that the new INSERT CTE functionality works as expected. Test various scenarios including nested inserts and validate the output.

    typescript
    test('should insert using CTE', async () => { const result = await db.with('cte', db.insert('users').values({name: 'Jane', age: 25})).select('*').from('cte'); expect(result).toBeDefined(); });

Validation

To confirm the fix worked, execute a query that uses an INSERT statement within a CTE and verify that the data is correctly inserted into the database. Additionally, check that the generated SQL matches the expected syntax for CTEs with INSERT.

Sign in to verify this fix

Environment

Submitted by

AC

Alex Chen

2450 rep

Tags

drizzleormtypescriptenhancementqb/ctepriorityqb/crud