How To Updates 🔗

How To Insert Data? 🔗

The simplest method inserts the rows and returns the number of inserted rows.

def insertCustomer: Int = ctx.run {
  sql"INSERT INTO customers(name) VALUES('my_customer')".insert()
}

How To Insert Returning Generated Keys? 🔗

This method inserts the rows and returns autogenerated keys (AUTOINCREMENT, SERIAL etc).

def insertCustomer: Seq[Int] = ctx.run {
  sql"INSERT INTO customers(name) VALUES('my_customer')".insertReturningGenKeys[Int]()
}

Generated keys can also be selected by JDBC column name or index:

sql"INSERT INTO customers(name) VALUES('my_customer')"
  .withGeneratedKeys(GeneratedKeys.ColumnNames(Seq("id")))
  .insertReturningGenKeys[Int]()

sql"INSERT INTO customers(name) VALUES('my_customer')"
  .withGeneratedKeys(GeneratedKeys.ColumnIndexes(Seq(1)))
  .insertReturningGenKeys[Int]()

---

There are also variations that return a single result, depending if you want an Option[T] or T (throws if no row returned):

  sql"SELECT ...".insertReturningGenKeyOpt[T]() : Option[T] // first result, if present
  sql"SELECT ...".insertReturningGenKey[T]() : T            // first result, or exception

How To Return Inserted Rows? 🔗

This method inserts the rows and returns columns you want from the inserted rows.
This is not supported by all databases, unfortunately.

def insertCustomers: List[Customer] = ctx.run {
  sql"""
    INSERT INTO customers(name)
    VALUES ('abc'), ('def'), ('ghi')
    RETURNING id, name
  """.returningRows[Customer]()
}

Here in one query you can both insert + get the row you inserted.

For exactly one returned row, use returningRow[Customer](). It throws a SqueryException if the database returns no row.

How To Do Updates? 🔗

You can do arbitrary SQL commands here.
The most common one is UPDATE-ing some rows:

// returns number of affected rows
def updateCustomers: Int = ctx.run {
  sql"""
    UPDATE customers
    SET name = 'whatever'
    WHERE name LIKE 'xyz_%'
  """.update()
}

Databases with RETURNING support can return affected rows from updates and deletes:

val renamed: Seq[Customer] =
  sql"""
    UPDATE customers SET name = 'Alice' WHERE id = 1
    RETURNING id, name
  """.returningRows[Customer]()

val deleted: Customer =
  sql"""
    DELETE FROM customers WHERE id = 1
    RETURNING id, name
  """.returningRow[Customer]()

The singular returningRow variant throws when no row is returned. Support and syntax depend on the database.

How To Batch Updates? 🔗

Use batchUpdate() when the same SQL statement needs to run with multiple sets of values:

val customers = Seq(
  Customer("Alice", "alice@example.com"),
  Customer("Bob", "bob@example.com")
)

val updateCounts: Seq[Int] = ctx.runTransaction {
  customers
    .map { customer =>
      sql"""
        INSERT INTO customers(name, email)
        VALUES (${customer.name}, ${customer.email})
      """
    }
    .batchUpdate()
}

Every query in the batch must have the same SQL shape and argument count. batchUpdate() returns the JDBC update count for each query in input order. A driver may return java.sql.Statement.SUCCESS_NO_INFO when it cannot provide an exact count.

Batching does not start a transaction automatically. Use runTransaction when the whole batch must succeed or fail together. The JDBC driver must implement prepared statement batching.

---

But of course you can do other commands as well:

def createTable: Unit = ctx.run {
  sql"""
    CREATE TABLE customers(
    id SERIAL PRIMARY KEY,
    name VARCHAR
    )
  """.update()
}

For statements that do not naturally return an update count, such as stored-procedure definitions, use sql"...".execute().