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]()
}

          

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 Insert returning inserted values? 🔗

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
  """.insertReturningRows[Customer]()
}

          

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

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()
}

          

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()
}