{"id":4048,"date":"2024-10-29T14:01:04","date_gmt":"2024-10-29T07:01:04","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4048"},"modified":"2024-10-29T14:43:28","modified_gmt":"2024-10-29T07:43:28","slug":"insert","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-bun\/insert\/","title":{"rendered":"SQLite Bun: Inserting Data"},"content":{"rendered":"\n

Summary<\/strong>: In this tutorial, you will learn how to insert a row into a table using the Bun app.<\/p>\n\n\n\n

How to Insert a Row into a Table in the Bun App<\/h2>\n\n\n\n

To insert a row into a table in the Bun app, you follow these steps:<\/p>\n\n\n\n

First, 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
Next, open a connection to an SQLite database:<\/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
Then, construct an INSERT<\/code><\/code> statement:<\/p>\n\n\n
const<\/span> sql = 'INSERT INTO table...'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In practice, you may want to pass values to the INSERT<\/code><\/code> statement. To do that, you can use binding parameters.<\/p>\n\n\n\n
Bun requires binding parameters to include $<\/code>, :<\/code>, or @<\/code> prefix. For example:<\/p>\n\n\n
$name\n:name\n@name<\/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 don’t pass parameters when executing the INSERT<\/code><\/code> statement. <\/p>\n\n\n\n
However, if you 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
Bun will throw an error if you don’t pass the parameters. <\/p>\n\n\n\n
Additionally, Bun will let you bind parameters without using prefixes. For example, if you have $name<\/code> parameter, you can pass an object with the name property { name: \"Joe\" }<\/code> , instead of { $name : \"Joe\"}<\/code><\/p>\n\n\n\n
After that, call the prepare()<\/code> method to prepare the INSERT<\/code><\/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 object.<\/p>\n\n\n\n
Finally, call the run()<\/code> method of the Statement object to execute the INSERT<\/code><\/code> statement.<\/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
Optionally, you can 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
An error may occur when opening the SQLite database and inserting data. To handle it properly, you can use the try-catch<\/a> statement.<\/p>\n\n\n\n
Here’s the complete code:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\n\nlet<\/span> db;\nconst<\/span> sql = \"INSERT INTO table...\"<\/span>;\n\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
Inserting data into the Authors table in the Bun App<\/h2>\n\n\n\n
Step 1. Create a new file author.ts<\/code> in the project directory:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\n\nexport<\/span> function<\/span> insert<\/span>(db: Database, firstName: string<\/span>, lastName: string<\/span><\/span>) <\/span>{\n  const<\/span> sql = `INSERT INTO authors(firstName, lastName) \n                 VALUES(:firstName,:lastName);`<\/span>;\n  const<\/span> stmt = db.prepare(sql);\n\n  const<\/span> result = stmt.run({\n    firstName,\n    lastName,\n  });\n\n  return<\/span> result.lastInsertRowid;\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 insert<\/code> function that inserts a new row into the authors<\/code> table and returns the inserted id:<\/p>\n\n\n
export<\/span> function<\/span> insert<\/span>(db: Database, firstName: string<\/span>, lastName: string<\/span><\/span>) <\/span>{<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, construct an INSERT<\/code><\/code> statement that inserts a new row into the authors<\/code>  table:<\/p>\n\n\n
const<\/span> sql = `INSERT INTO authors(firstName, lastName) \n             VALUES(:firstName,:lastName);`<\/span>;<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, prepare the INSERT<\/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 INSERT<\/code> statement:<\/p>\n\n\n
const<\/span> result = stmt.run({\n    firstName,\n    lastName,\n});<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, return the id of the inserted row:<\/p>\n\n\n
return<\/span> result.lastInsertRowid;<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Create a new file insert.ts<\/code> and add the following code to the insert.ts<\/code> file:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\nimport<\/span> { insert } from<\/span> \".\/author\"<\/span>;\n\nlet<\/span> db;\ntry<\/span> {\n  \/\/ connect to SQLite<\/span>\n  db = new<\/span> Database(\"pub.db\"<\/span>, { strict<\/span>: true<\/span> });\n\n  \/\/ insert data<\/span>\n  const<\/span> id = insert(db, \"John\"<\/span>, \"Doe\"<\/span>);\n\n  \/\/ display the id<\/span>\n  console<\/span>.log({ id });\n\n} catch<\/span> (error) {\n\n  \/\/ display the error<\/span>\n  console<\/span>.log(error);\n} finally<\/span> {\n\n  \/\/ close the database<\/span>\n  if<\/span> (db) db.close();\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 insert.ts<\/code> file:<\/p>\n\n\n
bun insert.ts<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll show the id of the inserted row:<\/p>\n\n\n
{ \n   id: 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 * from<\/span> authors;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id  firstName  lastName\n--  ---------  --------\n1   John       Doe<\/code><\/span><\/pre>\n\n\n
The output indicates that Bun app has successfully inserted a new row into the authors<\/code> table.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use binding parameters with a prefix  (:<\/code>, $<\/code> or @<\/code>) to pass values to the INSERT<\/code><\/code> statement when executing it.<\/li>\n\n\n\n
  • Use the prepare()<\/code> method of the Database<\/code> object to prepare the INSERT<\/code><\/code> statement for execution.<\/li>\n\n\n\n
  • Call the run()<\/code> method of the Statement<\/code> object to run the INSERT<\/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