{"id":4041,"date":"2024-10-29T08:42:12","date_gmt":"2024-10-29T01:42:12","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4041"},"modified":"2024-10-29T08:45:31","modified_gmt":"2024-10-29T01:45:31","slug":"create-tables","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-bun\/create-tables\/","title":{"rendered":"SQLite Bun: Create Tables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create a new table in SQLite from the Bun app.<\/p>\n\n\n\n

To create a new table, 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<\/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
const<\/span> sql = 'CREATE TABLE ...'<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, call the exec()<\/code> method of the db<\/code> instance to execute the CREATE TABLE<\/code> statement to create a new table in the SQLite database:<\/p>\n\n\n
db.exec(sql)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, 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\nlet<\/span> db;\nconst<\/span> sql = \"CREATE TABLE ...\"<\/span>;\n\ntry<\/span> {\n  db = new<\/span> Database(\"<pathToDbFile>\"<\/span>);\n  db.exec(sql);\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
Creating table example<\/h2>\n\n\n\n
Step 1. Create a new file createTables.ts<\/code> in the project directory.<\/p>\n\n\n\n
Step 2. Add the following code to the createTables.ts<\/code> file:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\n\nconst<\/span> sql = `\n    CREATE TABLE IF NOT EXISTS authors(\n        id  INTEGER PRIMARY KEY,\n        firstName TEXT NOT NULL,\n        lastName TEXT NOT NULL\n    )`<\/span>;\n\nlet<\/span> db;\ntry<\/span> {\n  db = new<\/span> Database(\"pub.db\"<\/span>);\n  db.exec(sql);\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
Step 3. Open the terminal and run the createTables.ts<\/code> file:<\/p>\n\n\n
bun createTables.ts<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Verifying tables<\/h2>\n\n\n\n
Step 1. Open a 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 pub.db<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. List all the tables in the pub.db<\/code> database:<\/p>\n\n\n
.tables<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
authors<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the authors<\/code> table has been created successfully.<\/p>\n\n\n\n
Step 3. Check the schema of the authors<\/code> table:<\/p>\n\n\n
.schema authors<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> authors<\/span>(\n        id<\/span>  INTEGER<\/span> PRIMARY KEY<\/span>,\n        firstName TEXT<\/span> NOT<\/span> NULL<\/span>,\n        lastName TEXT<\/span> NOT<\/span> NULL<\/span>\n    );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Call the exec()<\/code> method of the Database<\/code> instance to execute a CREATE TABLE<\/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
An error may occur when opening the SQLite database and creating a table. To handle it properly, you can use the try-catch<\/a> statement.<\/p>\n\n\n\n
Then, construct a CREATE TABLE<\/a><\/code> statement:<\/p>\n\n\n