Updating Data

Here's how you insert data into a table in SQL:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

And here's how you would update existing data in SQL:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

And you can even do what's called an "upsert" which is an update or insert depending on whether the data already exists:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
ON CONFLICT (column1)
DO UPDATE SET column2 = value2, column3 = value3, ...;

There's more syntax you can use in SQLite to exclude certain columns from the update, and you can even do an "insert or ignore" which will ignore the insert if the data already exists.

And finally, you can of course delete data from a table:

DELETE FROM table_name
WHERE condition;

Prisma

Prisma has utilities for all of these operations. We've already used them in the seed script we've written. But here's a quick refresher of all of these operations:

// update the rocket with id "1" to have the name "Falcon 9"
await prisma.rocket.update({
	where: { id: 1 },
	data: { name: 'Falcon 9' },
})

// update the rocket with the id "1" to have the name "Falcon 9" if it exists,
// otherwise create a new rocket with the name "Falcon 9"
await prisma.rocket.upsert({
	where: { id: 1 },
	update: { name: 'Falcon 9' },
	create: { name: 'Falcon 9' },
})

await prisma.rocket.delete({
	where: { id: 1 },
})

Prisma with SQLite supports updateMany and deleteMany. So if your where clause matches more than one record, your update will apply to every record that matches. However, upsertMany is not supported.

You can also perform nested queries for these as well.

Transactions

Let's say you're a bank and you're building a system to allow users to send money to each other. This will be a multi-step process that may look something like this:

Check that the user has enough money in their account to send the money
Remove the money from the user's account
Add the money to the recipient's account

If step 3 fails, we'll end up with a user who has less money than they should have! It would be better if we could roll back the changes we made in step 2. Kind of an "all or nothing" approach. That's what a "transaction" is (see why I used a banking metaphor? 😅). And transactions are supported by SQLite and Prisma natively. Here's the SQL syntax for a SQL-based transaction:

BEGIN TRANSACTION;
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
COMMIT;

And here's the Prisma syntax:

await prisma.$transaction([
	prisma.rocket.update({
		where: { id: 1 },
		data: { name: 'Falcon 9' },
	}),
	prisma.rocket.update({
		where: { id: 2 },
		data: { name: 'Falcon Heavy' },
	}),
])

Personally, I'm not a fan of the array API and I prefer the following callback API:

await prisma.$transaction(async $prisma => {
	await $prisma.rocket.update({
		where: { id: 1 },
		data: { name: 'Falcon 9' },
	})
	await $prisma.rocket.update({
		where: { id: 2 },
		data: { name: 'Falcon Heavy' },
	})
})

In either case, it's the same. If any of the queries fail, the entire transaction will fail and none of the changes will be applied.