Insert

ExoQuery has a simple DSL for performing SQL actions.

Basic Insert

val q =
  sql {
    insert<Person> { set(name to "Joe", age to 44, companyId to 123) }
  }
q.buildFor.Postgres().runOn(myDatabase)
//> INSERT INTO Person (name, age, companyId) VALUES ('Joe', 44, 123)

Typically you will use param to insert data from runtime values:

val nameVal = "Joe"
val ageVal = 44
val companyIdVal = 123
val q =
  sql {
    insert<Person> { set(name to param(nameVal), age to param(ageVal), companyId to param(companyIdVal)) }
  }
//> INSERT INTO Person (name, age, companyId) VALUES (?, ?, ?)

Insert a Whole Row

You can insert and entire person object using setParams.

val person = Person("Joe", 44, 123)
val q =
  sql {
    insert<Person> { setParams(person) }
  }
//> INSERT INTO Person (name, age, companyId) VALUES (?, ?, ?)

Insert with Exclusions

Wait a second, don’t database-model objects like Person typically have one or more primary keys key columns that need to be excluded during the insert because the database generates them?

Here is how to do that:

val person = Person(id = 0, "Joe", 44, 123)

val q =
  sql {
    insert<Person> { setParams(person).excluding(id) } // you can add multiple exclusions here e.g. exlcuding(id, id1, id2, ...)
  }
//> INSERT INTO Person (name, age, companyId) VALUES (?, ?, ?)

Insert with Returning ID

What do we do if we need to know the row id of the row we just inserted? The best way to do that is to use the returning function to add a RETURNING clause to the insert statement.

data class Person(val id: Int, val name: String, val age: Int, val companyId: Int)

val person = Person(id = 0, "Joe", 44, 123)
val q =
  sql {
    insert<Person> { setParams(person).excluding(id) }.returning { p -> p.id }
  }

q.buildFor.Postgres().runOn(myDatabase) // Also works with SQLite
//> INSERT INTO Person (name, age, companyId) VALUES (?, ?, ?) RETURNING id
q.buildFor.SqlServer().runOn(myDatabase)
//> INSERT INTO Person (name, age, companyId) OUTPUT INSERTED.id VALUES (?, ?, ?)

This will work for Postgres, SQLite, and SqlServer. For other databases use .returningKeys { id } which will instruct the database-driver to return the inserted row keys on a more low level. This function is more limited than what returning can do, and it is prone to various database-driver quirks so be sure to test it on your database appropriately.

The returning function is more flexible that returningKeys because it allows you to return not only any column in the inserted row but also collect these columns into a composite object of your choice. For example:

data class MyOutputData(val id: Int, val name: String)

val person = Person(id = 0, "Joe", 44, 123)

val q =
  sql {
    insert<Person> { setParams(person).excluding(id) }
      .returning { p -> MyOutputData(p.id, p.name + "-suffix") }
  }

val output: List<MyOutputData> = q.buildFor.Postgres().runOn(myDatabase)
//> INSERT INTO Person (name, age, companyId) VALUES (?, ?, ?) RETURNING id, name || '-suffix' AS name

Insert with OnConflict

You can use the onConflict function to specify what to do in case of a conflict. This is supported in Postgres, Sqlite, and MySQL.

Use onConflictUpdate to update rows when a conflict occurs. Use the excluded argument to refer to the row that is being inserted. This will become EXCLUDED term in the generated SQL.

val person = Person(id = 0, "Joe", 44, 123)
val q =
  sql {
    insert<Person> { setParams(person) }
      onConflictUpdate(id) { excluded -> set(name to excluded.name) }
  }
// Postgres and SQLite:
// INSERT INTO Person (name, age, companyId) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
// MySQL
// INSERT INTO Person (name, age, companyId) VALUES (?, ?, ?) AS x ON DUPLICATE KEY UPDATE name = x.name

The onConflictUpdate supports complex expressions as well. For example:

val person = Person(id = 0, "Joe", 44, 123)
val q =
  sql {
    insert<Person> { setParams(person) }
    onConflictUpdate(id) { excluded -> set(name to name + "-" + excluded.name) }
  }
// Postgres and SQLite:
// INSERT INTO Person AS x (name, age, companyId) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET name = x.name || '-' || EXCLUDED.name
// MySQL
// INSERT INTO Person (name, age, companyId) VALUES (?, ?, ?) AS x ON DUPLICATE KEY UPDATE name = Person.name || '-' || x.name

(Note that quite ironically, the table alias x is understood by Postgres and Sqlite to be the previous value, it understood by MySQL as the incoming value.)

Use onConflictIgnore to ignore the insert if a conflict occurs. This is also supported in Postgres, Sqlite, and MySQL.

val person = Person(id = 0, "Joe", 44, 123)
val q =
  sql {
    insert<Person> { setParams(person) }
    onConflictIgnore(id)
  }
// Postgres and SQLite:
// INSERT INTO Person (name, age, companyId) VALUES (?, ?, ?) ON CONFLICT (id) DO NOTHING
// MySQL:
// INSERT IGNORE INTO Person (name, age, companyId) VALUES (?, ?, ?)