Edit

Insert Queries

Reference: Postgres, SQLite, MySQL, T-SQL, Oracle

Overview

Insert

.from specifies the table to insert into and .insert specifies the data to insert. .from works it does in delete queries.

sq.from`person(first_name, last_name)`
  .insert`values (${'Shallan'}, ${'Davar'})`
  .query

{ text: 'insert into person(first_name, last_name) values ($1, $2)',
  args: ['Shallan', 'Davar'] }

To insert one row, pass .insert a single object. Column names are inferred from the object's keys.

Sqorn converts input object keys to snake_case by default.

sq.from('person')
  .insert({ firstName: 'Shallan', lastName: 'Davar' })
  .query

{ text: 'insert into person(first_name, last_name) values ($1, $2)',
  args: ['Shallan', 'Davar'] }

undefined values are inserted as default. default is a keyword, not a paraemeter.

sq.from('test').insert({ a: undefined, b: null }).query

{ text: 'insert into test(a, b) values (default, $1)',
  args: [null] }

To insert multiple rows, pass multiple objects. Column names are inferred from the keys of all objects.

sq.from('person')
  .insert(
    { firstName: 'Shallan', lastName: 'Davar' },
    { firstName: 'Navani', lastName: 'Kholin' }
  )
  .query

{ text: 'insert into person(first_name, last_name) values ($1, $2), ($3, $4)',
  args: ['Shallan', 'Davar', 'Navani', 'Kholin'] }

Alternatively, pass .insert an array of objects.

sq.from('person')
  .insert([
    { firstName: 'Shallan', lastName: 'Davar' },
    { firstName: 'Navani', lastName: 'Kholin' }
  ])
  .query

{ text: 'insert into person(first_name, last_name) values ($1, $2), ($3, $4)',
  args: ['Shallan', 'Davar', 'Navani', 'Kholin'] }

Values can be Expressions.

sq.from('person').insert({ firstName: e.upper('moo') }).query

{ text: 'insert into person(first_name) values (upper($1))',
  args: ['moo'] }

Values can be Fragments.

sq.from('person').insert({ firstName: sq.txt`'moo'` }).query

{ text: "insert into person(first_name) values ('moo')",
  args: [] }

Values can be Subqueries.

sq.from('person').insert({
    firstName: sq.return`${'Shallan'}`,
    lastName: sq.txt('Davar')
  })
  .query

{ text: "insert into person(first_name, last_name) values ((select $1), $2)",
  args: ['Shallan', 'Davar'] }

.insert accepts subqueries.

sq.from('superhero(name)')
  .insert(
    sq.return`${'batman'}`.union(sq.return`${'superman'}`)
  )
  .query

{ text: "insert into superhero(name) (select $1 union (select $2))",
  args: ['batman', 'superman'] }

Pass undefined to insert default values.

sq.from('person').insert(undefined).query

{ text: 'insert into person default values',
  args: [] }

Only the last call to .insert is used.

sq.from('person')
  .insert({ firstName: 'Shallan', lastName: 'Davar' })
  .insert({ firstName: 'Navani', lastName: 'Kholin' })
  .query

{ text: 'insert into person(first_name, last_name) values ($1, $2)',
  args: ['Navani', 'Kholin'] }

Returning

Postgres Only: Return the inserted rows with .return.

sq.from('book')
.insert({ title: 'Squirrels and Acorns' })
.return('id')
.query

{ text: 'insert into book(title) values ($1) returning id',
  args: ['Squirrels and Acorns'] }

Express

Express syntax works.

sq('book')()('id').insert({ title: 'Squirrels and Acorns' }).query

{ text: 'insert into book(title) values ($1) returning id',
  args: ['Squirrels and Acorns'] }
← DeleteUpdate →