Writing Data with INSERT, UPDATE, DELETE

The three write operations — INSERT, UPDATE, DELETE — modify the database. Each returns a result object with useful metadata.

INSERT

INSERT INTO posts (title, body, user_id) VALUES (?, ?, ?);

const stmt = db.prepare(
  'INSERT INTO posts (title, body, user_id) VALUES (?, ?, ?)'
)
const result = stmt.run('My Post Title', 'Post content here', userId)

console.log(result.lastInsertRowid) // the new row's ID
console.log(result.changes)         // 1 (number of rows affected)

lastInsertRowid is the primary key of the newly created row — return it to the client as the post’s id.

Named parameters (cleaner syntax)

const createPost = db.prepare(
  'INSERT INTO posts (title, body, user_id) VALUES (@title, @body, @userId)'
)

const result = createPost.run({
  title: 'My Post',
  body: 'Content',
  userId: 3,
})

Named parameters are easier to read when there are multiple values.

UPDATE

UPDATE posts SET title = ?, body = ? WHERE id = ? AND user_id = ?;

const stmt = db.prepare(
  'UPDATE posts SET title = ?, body = ? WHERE id = ? AND user_id = ?'
)
const result = stmt.run(newTitle, newBody, postId, userId)

if (result.changes === 0) {
  // Either the post doesn't exist or doesn't belong to this user
  return res.status(404).json({ error: 'Post not found or not yours' })
}

Always check result.changes — if it’s 0, the WHERE clause didn’t match any rows.

DELETE

DELETE FROM posts WHERE id = ? AND user_id = ?;

const result = db.prepare(
  'DELETE FROM posts WHERE id = ? AND user_id = ?'
).run(postId, userId)

if (result.changes === 0) {
  return res.status(404).json({ error: 'Post not found or not yours' })
}

res.sendStatus(204) // No Content — successful delete

Transactions

When multiple related writes must all succeed or all fail, use a transaction:

// Example: increment post upvotes AND record the upvoter
const upvotePost = db.transaction((userId: number, postId: number) => {
  db.prepare(
    'INSERT INTO post_upvotes (user_id, post_id) VALUES (?, ?)'
  ).run(userId, postId)

  db.prepare(
    'UPDATE posts SET upvotes = upvotes + 1 WHERE id = ?'
  ).run(postId)
})

try {
  upvotePost(userId, postId)
} catch (err) {
  // If either INSERT or UPDATE fails, neither is committed
  // The duplicate key constraint on post_upvotes will throw if already upvoted
  return res.status(409).json({ error: 'Already upvoted' })
}

better-sqlite3 transactions are synchronous functions. If any statement inside throws, the entire transaction is rolled back automatically.

Returning the created/updated data

After INSERT, you often want to return the new row:

const result = db.prepare(
  'INSERT INTO posts (title, body, user_id) VALUES (?, ?, ?)'
).run(title, body, userId)

// Fetch the newly created post
const newPost = db.prepare('SELECT * FROM posts WHERE id = ?')
  .get(result.lastInsertRowid)

res.status(201).json(newPost)

Exercise

1. Write a function createUser(username, passwordHash) that inserts a user and returns the new row.
2. Write a function deletePost(postId, userId) that deletes a post and returns a boolean indicating success.
3. Implement the upvote transaction from above and test it.
4. Try upvoting the same post twice — confirm the unique constraint prevents it.

Recap

• .run(params) executes INSERT/UPDATE/DELETE and returns { lastInsertRowid, changes }.
• lastInsertRowid is the primary key of the new row — return it to the client.
• result.changes === 0 means no rows matched the WHERE clause — return 404.
• Use db.transaction(fn) for atomic multi-statement operations — auto-rolls back on error.
• After INSERT, query the new row to return complete data to the client.