Insert

Single row

The return value is the SERIAL generated by inserting the new row located in result.rows[0][serialColumn = 'id'] of the pg result object.

const id = await sql.insert(
  'users',
  { email: 'email', name: 'name' }
)

// text: INSERT INTO "users" ("email", "name") VALUES ($1, $2) RETURNING "id"
// values: ['email', 'name']

Multiple rows

The return value is an array of the SERIALs generated by inserting the new row located in result.rows[][serialColumn = 'id'] of the pg result object.

const ids = await sql.insert(
  'users',
  [
    { email: 'emailA', name: 'nameA' },
    { email: 'emailB', name: 'nameB' },
    { email: 'emailC', name: 'nameC' }
  ]
)

// text: INSERT INTO "users" ("email", "name") 
//   VALUES ($1, $2), ($3, $4), ($5, $6) RETURNING "id"
// values: ['emailA', 'nameA', 'emailB', 'nameB', 'emailC', 'nameC']

The return value is also an array if the given rows array contains only one object.

Only insert specific columns

const ids = await sql.insert(
  'users',
  [
    { email: 'emailA', name: 'nameA', active: true },
    { email: 'emailB', name: 'nameB', active: true },
    { email: 'emailC', name: 'nameC', active: true }
  ],
  { columns: ['email', 'name'] }
)

// text: INSERT INTO "users" ("email", "name") 
//   VALUES ($1, $2), ($3, $4), ($5, $6) RETURNING "id"
// values: ['emailA', 'nameA', 'emailB', 'nameB', 'emailC', 'nameC']

Returning another serial column

const example = await sql.insert(
  'users',
  { email: 'email', name: 'name' },
  { serialColumn: 'example' }
)

// text: INSERT INTO "users" ("email", "name") VALUES ($1, $2) RETURNING "example"
// values: ['email', 'name']

Use SQL Tag inserting data

For more complex insert queries the SQL Tag can be used.

const example = await sql.insert(
  sql`
    INSERT INTO "validated_users" 
      SELECT "*" FROM "users" WHERE "validated" = 1 
    RETURNING "id"
  `
)

// text: INSERT INTO "validated_users" 
//   SELECT "*" FROM "users" WHERE "validated" = 1 RETURNING "id"
// values: []

Use SQL Tag inserting data combined with another serial column

const example = await sql.insert(
  sql`
    INSERT INTO "validated_users" 
      SELECT "*" FROM "users" WHERE "validated" = 1 
    RETURNING "example"
  `,
  { serialColumn: 'example' }
)

// text: INSERT INTO "validated_users" 
//   SELECT "*" FROM "users" WHERE "validated" = 1 RETURNING "example"
// values: []

The default serialColumn id can be changed by setting sql.defaultSerialColumn.

table can also be given as an array with the schema. Otherwise if it's defined the defaultSchema option will be used.