{"id":4066,"date":"2024-10-29T14:37:24","date_gmt":"2024-10-29T07:37:24","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4066"},"modified":"2024-10-29T14:38:19","modified_gmt":"2024-10-29T07:38:19","slug":"delete","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-bun\/delete\/","title":{"rendered":"SQLite Bun: Deleting Data"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn how to delete a row from a table in an SQLite database in a Bun app.<\/p>\n\n\n\n

How to Delete Data from a Table in the Bun App<\/h2>\n\n\n\n

To delete data from a table of an SQLite database in a Bun app, you follow these steps:<\/p>\n\n\n\n

Step 1. Import the Database<\/code> class from the bun:sqlite<\/code> module:<\/p>\n\n\n

import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Open a connection to an SQLite database<\/a>:<\/p>\n\n\n
const<\/span> db = new<\/span> Database(filename);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Construct an DELETE<\/code> statement:<\/p>\n\n\n
const<\/span> sql = 'DELETE FROM table WHERE id = :id'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Typically, you need to pass values to the DELETE<\/code> statement such as an id to delete the row by id. To do that, you can use binding parameters<\/em>.<\/p>\n\n\n\n
In Bun, binding parameters have one of the prefixes: $<\/code>, :<\/code>, or @<\/code>. For example:<\/p>\n\n\n
$id\n:id\n@id<\/span><\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
By default, Bun does not throw an error if you skip passing parameters when executing the DELETE<\/code> statement.<\/p>\n\n\n\n
But you can set the strict<\/code> mode to true<\/code> when connecting to a SQLite database:<\/p>\n\n\n
const<\/span> db = new<\/span> Database(\"mydb.sqlite\"<\/span>, { strict : true<\/span> });<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, Bun will throw an error if you don’t pass the parameters. Also, Bun will let you bind parameters without using prefixes.<\/p>\n\n\n\n
For example, if you have :id<\/code> parameter, you can pass an object with the id<\/code> property { id: 1 }<\/code> , instead of { :id : 1 }<\/code><\/p>\n\n\n\n
Step 4. Call the prepare()<\/code> method to prepare the DELETE<\/code> statement for execution:<\/p>\n\n\n
const<\/span> stmt = db.prepare(sql);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The prepare()<\/code> method returns a Statement<\/code> object.<\/p>\n\n\n\n
Step 5. Call the run()<\/code> method of the Statement<\/code> object to execute the DELETE<\/code> statement to delete data:<\/p>\n\n\n
const<\/span> result = stmt.run({...});<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
The changes property of the result object stores the number of rows deleted:<\/p>\n\n\n
result<\/span>.changes<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 6. Optionally, you can explicitly close the database connection:<\/p>\n\n\n
db.close();<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Here’s the complete code:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\n\nconst<\/span> sql = \"DELETE FROM table ...\"<\/span>;\n\nlet<\/span> db;\ntry<\/span> {\n  db = new<\/span> Database(\"<pathToDbFile>\"<\/span>, { strict<\/span> : true<\/span>});\n  const<\/span> stmt = db.prepare(sql);\n  stmt.run({...})\n} catch<\/span> (error) {\n  console<\/span>.log(error);\n} finally<\/span> {\n  if<\/span> (db) db.close();\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Delete Data from the authors Table in the Bun App<\/h2>\n\n\n\n
Step 1. Add a new function called remove()<\/code> to file author.ts<\/code> file:<\/p>\n\n\n
\/\/ ...<\/span>\n\nexport<\/span> function<\/span> remove<\/span>(db: Database, id: number<\/span><\/span>): number<\/span> <\/span>{\n  const<\/span> sql = `DELETE FROM authors\n               WHERE id = :id;`<\/span>;\n  const<\/span> stmt = db.prepare(sql);\n  const<\/span> result = stmt.run({ id });\n  return<\/span> result.changes;\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, define remove<\/code> function that deletes a row from the authors<\/code> table and returns the number of rows deleted:<\/p>\n\n\n
export<\/span> function<\/span> remove<\/span>(db: Database, id: number<\/span><\/span>): number<\/span> <\/span>{<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, construct an DELETE<\/code><\/code> statement that deletes a row from the authors<\/code> table:<\/p>\n\n\n
const<\/span> sql = `DELETE FROM authors\n               WHERE id = :id;`<\/span>;<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, prepare the UPDATE<\/code> statement for execution:<\/p>\n\n\n
const<\/span> stmt = db.prepare(sql);<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, execute the DELETE<\/code> statement:<\/p>\n\n\n
const<\/span> result = stmt.run({ id });<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, return the number of rows deleted:<\/p>\n\n\n
return<\/span> result.changes;<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Create a new file delete.ts<\/code> with the following code:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\nimport<\/span> { remove } from<\/span> \".\/author\"<\/span>;\n\nlet<\/span> db;\n\ntry<\/span> {\n  \/\/ connect to SQLite<\/span>\n  db = new<\/span> Database(\"pub.db\"<\/span>, { strict<\/span>: true<\/span> });\n\n  \/\/ remove an author with id 1<\/span>\n  const<\/span> rowDeleted = remove(db, 1<\/span>);\n\n  \/\/ display the number of rows deleted<\/span>\n  console<\/span>.log({ rowDeleted });\n} catch<\/span> (error) {\n  \/\/ display the error<\/span>\n  console<\/span>.log(error);\n} finally<\/span> {\n  \/\/ close the database<\/span>\n  if<\/span> (db) db.close();\n}\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open the terminal and run the delete.ts<\/code> file:<\/p>\n\n\n
bun delete<\/span>.ts<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll show the number of rows deleted:<\/p>\n\n\n
{\n  rowUpdated: 1<\/span>,\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Verifying data<\/h2>\n\n\n\n
Step 1. Open your terminal and navigate to the project directory.<\/p>\n\n\n\n
Step 2. Connect to the pub.db<\/code> database using the sqlite3<\/code> shell:<\/p>\n\n\n
sqlite3<\/span> pub<\/span>.db<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Format the query output:<\/p>\n\n\n
.mode<\/span> column<\/span>\n.header<\/span> on<\/span> \n.nullvalue<\/span> null<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Retrieve data from the authors<\/code> table:<\/p>\n\n\n
select<\/span> * from<\/span> authors<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id  firstName  lastName\n--  ---------  --------\n1   Johnathan  Smith<\/code><\/span><\/pre>\n\n\n
The output indicates that the Bun app has successfully updated the author with id 1.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the prepare()<\/code> method of the Database<\/code> object to prepare the UPDATE<\/code> statement for execution.<\/li>\n\n\n\n
  • Call the run()<\/code> method of the Statement<\/code> object to run the UPDATE<\/code><\/code> statement.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
When you open a SQLite database connection and delete data from a table, an error may occur. To handle it properly, you can use the try-catch<\/a> statement.<\/p>\n\n\n\n