Add support for `insert into ... select`

[L-Mario564]

Addresses #398. This PR adds support to insert rows from one table into another via the insert into ... select syntax.

The following is valid Drizzle syntax:

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  role: text('role').notNull()
});

const employees pgTable('employees', {
  id: serial('id').primaryKey(),
  name: text('name').notNull()
});

// Copy users who are employees into the employees table, also returns the inserted rows
const insertedEmployees = await db
  .insert(employees)
  .select({
    name: users.name
  })
  .from(users)
  .where(eq(users.role, 'employee'))
  .returning({
    id: employees.id,
    name: employees.name
  });

And would roughly be as follows in SQL:

insert into "employees" ("name")
select "users"."name" from "users" where "users"."role" = 'employee'
returning "employees"."id", "employees"."name"

Everything from the select and insert statements are valid for this insert().select() syntax.

There's a catch

$defaultFn realistically can't be implemented here, so there's no support for it when using this syntax. Reason being is that the default function can't be called in the query, since it's a Typescript function, not an SQL one, meaning that this can lead to issues, like for example: Using the default function to generate a UUID with Javascript. This function will only run once before the SQL query is built, meaning that, if the select part of the query returns multiple rows, it will lead to duplicate UUIDs if the column can be null, or an error will be thrown if the column can't be null. This isn't the case for SQL functions like now() because this is left for the database to handle instead of the Javascript runtime.

[L-Mario564]

This PR is still work in progress. Right now, only PG has support for this syntax. I would like some input before implementing this for the other two dialects.

[AndriiSherman]

API was approved. Need to review code as well

[AndriiSherman]

@L-Mario564 this PR looks good. Could you please resolve all the conflicts so I can merge it to beta?

[AndriiSherman]

Sorry, not this one, I was reviewing with

[AndriiSherman]

@L-Mario564 thanks for this PR. While reviewing all the changes, we found a better API, that we can use, which also can improve architecture for builders

This is a proposed API that we would like to include in Drizzle. It will offer various options and usages

Explanations will be included after all Options listed:

Option 1: Using Query Builder directly

import { QueryBuilder } from 'drizzle-orm/pg-core';

const qb = new QueryBuilder();
const query = qb.select({
    name: users.name
  })
  .from(users)
  .where(eq(users.role, 'employee'))

const insertedEmployees = await db
	.insert(employees)
	.select(query)
	.returning({
		id: employees.id,
		name: employees.name,
	});

Option 2: Use callback inside select

const insertedEmployees = await db
	.insert(employees)
	.select((qb) => qb.select({  name: users.name }).from(users).where(eq(users.role, 'employee')))
	.returning({
		id: employees.id,
		name: employees.name,
	});

Option 3: Use sql template for custom select statement. Both callback or just sql

import { sql } from 'drizzle-orm';

const insertedEmployees = await db
	.insert(employees)
	.select(() => sql`select * from ...`)
	.returning({
		id: employees.id,
		name: employees.name,
	});
	
// or

const insertedEmployees = await db
	.insert(employees)
	.select(sql`select * from ...`)
	.returning({
		id: employees.id,
		name: employees.name,
	});

The main concern was that .returning() was added after the select, and it primarily pertains to the insert scope rather than the select scope. Therefore, this method should be visually and semantically linked to insert. With the proposed API, we aim to achieve both visual separation and the ability to define a select query as a standalone query using QueryBuilder. Subsequently, this query can be reused inside the select for insert. This approach will also prevent the need to create any abstract classes in the implementation, as you did

I would suggest not creating InsertSelect builders but rather retrieving the entire select query inside SQL and placing it into PgInsertConfig. Then, at the buildInsertQuery step, simply use this SQL in the last statements where you are constructing the insert query:

return sql`insert into ${table} ${insertOrder} values ${valuesSql}${onConflictSql}${returningSql}`;

You can just use this select SQL as a template param instead of values ${valuesSql}

[DanielBaulig]

Bumping this PR. Any chance this will get another stab any time soon?