{"id":4088,"date":"2024-10-29T19:56:33","date_gmt":"2024-10-29T12:56:33","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4088"},"modified":"2024-10-29T19:57:07","modified_gmt":"2024-10-29T12:57:07","slug":"select","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-bun\/select\/","title":{"rendered":"SQLite Bun: Selecting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to select data from a table in SQLite using Bun.<\/p>\n\n\n\n

How to Select data from a table in Bun<\/h2>\n\n\n\n

To select data from a table in Bun, 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> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Next, open a connection to an SQLite database<\/a>:<\/p>\n\n\n
const<\/span> db = new<\/span> Database(filename);<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, construct a SELECT<\/code> statement:<\/p>\n\n\n
const<\/span> sql = 'SELECT * FROM table ...'<\/span>;<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Typically, you want to filter data by passing value to the SELECT statement. For example, you may want to get row by id or find rows whose columns match a pattern. <\/p>\n\n\n\n
To do this, you can use the binding parameters. In Bun, bind parameters use a prefix as $<\/code>, :<\/code>, and @<\/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
When executing a query, if you don’t pass values to binding parameters, Bun will not throw any error. This is the default behavior.<\/p>\n\n\n\n
If you want Bun to throw an error if you forget to pass parameters, you can connect to the SQLite database using strict mode:<\/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
Also, the strict<\/code> mode allows you to use the bind parameters without prefixes. <\/p>\n\n\n\n
For example, if you have $id<\/code>parameter, you can pass an object with the id property { id: 1 }<\/code> , instead of { $id : 1}<\/code><\/p>\n\n\n\n
After that, call the prepare()<\/code> method to prepare the SELECT<\/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
The prepare()<\/code> method returns a Statement<\/code> object.<\/p>\n\n\n\n
Finally, call the get()<\/code> method of the Statement<\/code> object to get the first matching row:<\/p>\n\n\n
const<\/span> result = stmt.get({id: value});<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Or call the all()<\/code> method of the Statement<\/code> object to get all matching rows:<\/p>\n\n\n
const<\/span> result = stmt.all({param: value});<\/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> TypeScript<\/span> (<\/span>typescript<\/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\nlet<\/span> db;\nconst<\/span> sql = \"SELECT * FROM table...\"<\/span>;\n\ntry<\/span> {\n  db = new<\/span> Database(\"<pathToDbFile>\"<\/span>, { strict<\/span> : true<\/span>});\n  const<\/span> stmt = db.prepare(sql);\n  const<\/span> rows = stmt.all({...})\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
Selecting one row<\/h2>\n\n\n\n
We’ll select the author by id from the authors<\/code> table.<\/p>\n\n\n\n
Step 1. Add a new function called findById<\/code> to the author.ts<\/code> file:<\/p>\n\n\n
export<\/span> function<\/span> findById<\/span>(db: Database, id: number<\/span><\/span>) <\/span>{\n  const<\/span> sql = `SELECT * FROM authors \n               WHERE id = :id`<\/span>;\n  const<\/span> stmt = db.prepare(sql);\n  return<\/span> stmt.get({ id });\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
The findById()<\/code> function returns a row with a specified id.<\/p>\n\n\n\n
Step 2. Create a new file fetchOne.ts<\/code> with the following code:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\nimport<\/span> { findById } from<\/span> \".\/author\"<\/span>;\n\nlet<\/span> db;\ntry<\/span> {\n  db = new<\/span> Database(\"pub.db\"<\/span>, { strict: true<\/span> });\n  const<\/span> author = findById(db, 1<\/span>);\n  console<\/span>.log(author);\n} catch<\/span> (error) {\n  console<\/span>.log(error);\n} finally<\/span> {\n  if<\/span> (db) db.close();\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open your terminal and run the fetchOne.ts<\/code> file:<\/p>\n\n\n
bun fetchOne.ts<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
{\n  id: 1<\/span>,\n  firstName: \"Alex\"<\/span>,\n  lastName: \"Starling\"<\/span>,\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output shows the author with id 1.<\/p>\n\n\n\n
Selecting all rows from a table<\/h2>\n\n\n\n
We’ll select all rows from the authors<\/code> table.<\/p>\n\n\n\n
Step 1. Add a new function called findAll<\/code> to the author.ts<\/code> file:<\/p>\n\n\n
export<\/span> function<\/span> findAll<\/span>(db: Database<\/span>) <\/span>{\n  const<\/span> sql = `SELECT * FROM authors \n               ORDER BY firstName`<\/span>;\n  const<\/span> stmt = db.prepare(sql);\n  return<\/span> stmt.all();\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
The findAll()<\/code> function returns all rows from the authors<\/code> table. The result set is sorted by the authors’ first names.<\/p>\n\n\n\n
Step 2. Create a new file fetchAll.ts<\/code> with the following code:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\nimport<\/span> { findAll } from<\/span> \".\/author\"<\/span>;\n\nlet<\/span> db;\ntry<\/span> {\n  db = new<\/span> Database(\"pub.db\"<\/span>, { strict: true<\/span> });\n  const<\/span> authors = findAll(db);\n  console<\/span>.log(authors);\n} catch<\/span> (error) {\n  console<\/span>.log(error);\n} finally<\/span> {\n  if<\/span> (db) db.close();\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open your terminal and run the fetchAll.ts<\/code> file:<\/p>\n\n\n
bun fetchAll.ts<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output<\/summary>
[\n  {\n    id: 1<\/span>,\n    firstName: \"Alex\"<\/span>,\n    lastName: \"Starling\"<\/span>,\n  }, {\n    id: 7<\/span>,\n    firstName: \"Avery\"<\/span>,\n    lastName: \"Storm\"<\/span>,\n  }, {\n    id: 3<\/span>,\n    firstName: \"Casey\"<\/span>,\n    lastName: \"Rivers\"<\/span>,\n  }, {\n    id: 10<\/span>,\n    firstName: \"Dakota\"<\/span>,\n    lastName: \"Ember\"<\/span>,\n  }, {\n    id: 2<\/span>,\n    firstName: \"Jamie\"<\/span>,\n    lastName: \"Falcon\"<\/span>,\n  }, {\n    id: 6<\/span>,\n    firstName: \"Jordan\"<\/span>,\n    lastName: \"Blaze\"<\/span>,\n  }, {\n    id: 4<\/span>,\n    firstName: \"Morgan\"<\/span>,\n    lastName: \"Sky\"<\/span>,\n  }, {\n    id: 9<\/span>,\n    firstName: \"Quinn\"<\/span>,\n    lastName: \"Frost\"<\/span>,\n  }, {\n    id: 8<\/span>,\n    firstName: \"Riley\"<\/span>,\n    lastName: \"Moon\"<\/span>,\n  }, {\n    id: 5<\/span>,\n    firstName: \"Taylor\"<\/span>,\n    lastName: \"Phoenix\"<\/span>,\n  }\n]<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre><\/details>\n\n\n\n
The output shows all the rows from the authors<\/code> table.<\/p>\n\n\n\n
Searching for authors by names<\/h2>\n\n\n\n
We’ll find authors by matching the first or last names with a search term.<\/p>\n\n\n\n
Step 1. Add a new function called find<\/code> to the author.ts<\/code> file:<\/p>\n\n\n
export<\/span> function<\/span> find<\/span>(db: Database, name: string<\/span><\/span>) <\/span>{\n  const<\/span> sql = `SELECT * FROM authors \n              WHERE firstName like :name OR lastName like :name\n              ORDER BY firstName`<\/span>;\n  const<\/span> stmt = db.prepare(sql);\n  return<\/span> stmt.all({ name: `%${name}<\/span>%`<\/span> });\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
The find()<\/code> function returns all authors whose first or last names contain a search term. We use the LIKE<\/a> operator for pattern matching.<\/p>\n\n\n\n
Step 2. Create a new file fetch.ts<\/code> with the following code:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\nimport<\/span> { find } from<\/span> \".\/author\"<\/span>;\n\nlet<\/span> db;\ntry<\/span> {\n  db = new<\/span> Database(\"pub.db\"<\/span>, { strict: true<\/span> });\n  const<\/span> authors = find(db, \"or\"<\/span>);\n  console<\/span>.log(authors);\n} catch<\/span> (error) {\n  console<\/span>.log(error);\n} finally<\/span> {\n  if<\/span> (db) db.close();\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open your terminal and run the fetch.ts<\/code> file:<\/p>\n\n\n
bun fetch.ts<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output<\/summary>
[\n  {\n    id: 7<\/span>,\n    firstName: \"Avery\"<\/span>,\n    lastName: \"Storm\"<\/span>,\n  }, {\n    id: 6<\/span>,\n    firstName: \"Jordan\"<\/span>,\n    lastName: \"Blaze\"<\/span>,\n  }, {\n    id: 4<\/span>,\n    firstName: \"Morgan\"<\/span>,\n    lastName: \"Sky\"<\/span>,\n  }, {\n    id: 5<\/span>,\n    firstName: \"Taylor\"<\/span>,\n    lastName: \"Phoenix\"<\/span>,\n  }\n]<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre><\/details>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the get()<\/code> method of the Statment<\/code> object to retrieve the first matching row.<\/li>\n\n\n\n
  • Use the all()<\/code> method of the Statment<\/code> object to retrieve all the matching rows.<\/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
An error may occur when opening the SQLite database and selecting data. To handle it properly, you can use the try-catch<\/a> statement.<\/p>\n\n\n\n