UpdatesSection
Related Doc:
package doobie
Other databases (including PostgreSQL) provide a way to do this in one shot by returning multiple specified columns from the inserted row.
Other databases (including PostgreSQL) provide a way to do this in one shot by returning multiple specified columns from the inserted row.
def insert3(name: String, age: Option[Int]): ConnectionIO[Person] = { sql"insert into person (name, age) values ($name, $age)" .update.withUniqueGeneratedKeys("id", "name", "age") }
The withUniqueGeneratedKeys specifies that we expect exactly one row back (otherwise an
exception will be thrown), and requires a list of columns to return.
This mechanism also works for updates, for databases that support it. In the case of multiple
row updates we use withGeneratedKeys and get a Process[ConnectionIO, Person] back.
doobie supports batch updating via the updateMany and updateManyWithGeneratedKeys
operations on the Update data type.
By using an Update directly we can apply many sets of arguments to the same statement, and
execute it as a single batch operation.
- updateMany will return the updated of affected rows
- For databases that support it (such as PostgreSQL) we can use updateManyWithGeneratedKeys
to return a stream of updated rows.
Let's insert a new row by using the recently defined insert1 method.
If there is no dependency between the SQL operations, it could be better to use an applicative functor.
On the contrary, if we want to insert several rows, there are different ways to do that.
On the contrary, if we want to insert several rows, there are different ways to do that. A
first try could be to use a for-comprehension to compose all the single operations.
If all the data to be inserted is represented by a List, other way could be by using the
Scalaz traverse method.
When we insert we usually want the new row back, so let’s do that.
When we insert we usually want the new row back, so let’s do that. First we’ll do it the hard
way, by inserting, getting the last used key via lastVal(), then selecting the indicated
row.
def insert2(name: String, age: Option[Short]): ConnectionIO[Person] = for { _ <- sql"insert into person (name, age) values ($name, $age)".update.run id <- sql"select lastval()".query[Long].unique p <- sql"select id, name, age from person where id = $id".query[Person].unique } yield p
This is irritating but it is supported by all databases (although the “get the last used id” function will vary by vendor).
Some database (like H2) allow you to return [only] the inserted id, allowing the above
operation to be reduced to two statements (see below for an explanation of
withUniqueGeneratedKeys).
Updating follows the same pattern.
Updating follows the same pattern. For instance, we suppose that we want to modify the age of a person.
The parameterless execute method has been deprecated and will be removed in a future version of ScalaTest. Please invoke execute with empty parens instead: execute().
The trap method is no longer needed for demos in the REPL, which now abreviates stack traces, and will be removed in a future version of ScalaTest
In this section we examine operations that modify data in the database, and ways to retrieve the results of these updates.
Data Definition
It is uncommon to define database structures at runtime, but doobie handles it just fine and treats such operations like any other kind of update. And it happens to be useful here!.
Let’s create a new table, which we will use for the exercises to follow. This looks a lot like our prior usage of the
sqlinterpolator, but this time we’re usingupdaterather thanquery. The.runmethod gives aConnectionIO[Int]that yields the total number of rows modifiedWe can compose these and run them together.
Inserting
Inserting is straightforward and works just as with selects. Here we define a method that constructs an
Update0that inserts a row into thepersontable.