{"id":556,"date":"2024-05-06T00:31:13","date_gmt":"2024-05-06T00:31:13","guid":{"rendered":"https:\/\/www.freelifemakers.org\/wordpress\/?p=556"},"modified":"2024-05-07T22:35:50","modified_gmt":"2024-05-07T22:35:50","slug":"nodejsdatabase-insertsqlite","status":"publish","type":"post","link":"https:\/\/www.freelifemakers.org\/wordpress\/index.php\/2024\/05\/06\/nodejsdatabase-insertsqlite\/","title":{"rendered":"[NODEJS]DATABASE INSERT,SELECT,DELETE(SQLITE)"},"content":{"rendered":"\n

SQLITE\ub294 \ub85c\uadf8\uc778\uc5c6\uc774 \uc0ac\uc6a9 \ud560 \uc218 \uc788\ub294 \uac00\ubcbc\uc6b4 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc785\ub2c8\ub2e4.
\ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc11c\ubc84\ubd80\ubd84\uc774 \uc5c6\uae30 \ub54c\ubb38\uc5d0 db\ud30c\uc77c\uc5d0 \ubc14\ub85c \uc811\uadfc\ud574\uc11c \uc0ac\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.
\uc11c\ubc84\uc811\uc18d \uc5c6\uc774 \uc6f9\uc571\uc744 \ub9cc\ub4e4\uac70\ub098 \ubaa8\ubc14\uc77c \uc571\uc744 \ub9cc\ub4e4 \ub54c \ub9ce\uc774 \uc0ac\uc6a9\ud569\ub2c8\ub2e4.
\uc624\ud508 \uc18c\uc2a4\uc774\uba70 \uc790\uc138\ud55c \ub0b4\uc6a9\uc740 https:\/\/sqlite.org <\/a>\uc5d0\uc11c \ud655\uc778 \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. <\/p>\n\n\n\n

SQLITE is a lightweight database that can be used without logging in.
Since there is no database server part, you can directly access and use the db file.
It is often used when creating web apps or mobile phone apps without server access.
It is open source and more information can be found at https:\/\/sqlite.org.<\/p>\n\n\n\n

1.SQLITE \ud568\uc218(SQLITE FUNCTION)<\/strong>
– \uc544\ub798 \ucf54\ub4dc\uc5d0\uc11c \uc0ac\uc6a9\ub41c SQLITE\ud568\uc218 \uc785\ub2c8\ub2e4.
– This is the SQLITE function used in the code below.

.exec() : \uc77c\ubc18\uc801\uc778 SQL \uad6c\ubb38\uc744 \uc2e4\ud589\ud558\uba70 \ud30c\ub77c\ubbf8\ud130\ub97c \uc9c1\uc811\uc801\uc73c\ub85c \uc870\uc791(\ud578\ub4e4\ub9c1) \ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4.
.run() : \uc774 \ubc29\ubc95\uc740 \uc77c\ubc18\uc801\uc73c\ub85c INSERT, UPDATE, DELETE \ub4f1\uacfc \uac19\uc774 \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub97c \uc218\uc815\ud558\ub294 SQL \ucffc\ub9ac\ub97c \uc2e4\ud589\ud558\ub294 \ub370 \uc0ac\uc6a9\ub429\ub2c8\ub2e4. SQL \uc778\uc81d\uc158 \uacf5\uaca9\uc744 \ubc29\uc9c0\ud558\uae30 \uc704\ud574 \ub9e4\uac1c\ubcc0\uc218\ud654\ub41c \ucffc\ub9ac\ub97c \uc0ac\uc6a9\ud569\ub2c8\ub2e4.
.get() : SQLLITE\uc758 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0\uc11c 1\ud589\uc758 \ub370\uc774\ud130\ub9cc \uac00\uc9c0\uace0 \uc635\ub2c8\ub2e4.
.all() : \ud2b9\uc815 \uae30\uc900\uc5d0 \ub530\ub77c \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0\uc11c \uc5ec\ub7ec \ub808\ucf54\ub4dc\ub97c \uac80\uc0c9\ud558\ub824\uace0 \ud560 \ub54c \uc77c\ubc18\uc801\uc73c\ub85c \uc0ac\uc6a9\ub429\ub2c8\ub2e4.
.each(): SELECT \ucffc\ub9ac\ub97c \uc2e4\ud589\ud558\uc5ec \ub370\uc774\ud130 \ud14c\uc774\ube14\uc5d0\uc11c \ub370\uc774\ud130\ub97c \uac80\uc0c9\ud558\ub294 \ub370 \uc0ac\uc6a9\ub429\ub2c8\ub2e4. \uac80\uc0c9\ub41c \ub370\uc774\ud130\ub97c \ud589\ub2e8\uc704\ub85c \ubc18\ubcf5\ud574\uc11c \uac00\uc838\uc635\ub2c8\ub2e4.

.exec() : General SQL statements are executed and parameters cannot be directly manipulated (handled).
.run():This method is typically used to execute SQL queries that modify the database, such as INSERT, UPDATE, DELETE, etc.It uses parameterized queries to prevent SQL injection attacks.
.get():Only one row of data is imported from the SQLLITE database.
.all():It’s commonly used when you expect to retrieve multiple records from the database based on certain criteria.
.each : Used to retrieve data from a data table by executing a SELECT query. Retrieves the searched data row by row repeatedly.<\/p>\n\n\n\n

2.\uc124\uce58(install)<\/strong>
\uac04\ub2e8\ud788 npm\uba85\ub839\uc5b4\ub85c \uc124\uce58 \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.
You can simply install it with the npm command.<\/p>\n\n\n\n

#npm install sqlite sqlite3<\/code><\/pre>\n\n\n\n
3.\ub370\uc774\ud130\ubca0\uc774\uc2a4\uc811\uc18d(Database connection)<\/strong>
\ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc811\uc18d\uc744 \uc704\ud574\uc11c open\ud568\uc218 \ub610\ub294 new sqlite3.Database()\ub97c \uc0ac\uc6a9\ud560 \uc788\uc2b5\ub2c8\ub2e4.
To connect to the database, you can use the open function or new sqlite3.Database().

1)\ubaa8\ub4c8 \uc784\ud3ec\ud2b8(module import)<\/p>\n\n\n\n
const sqlite3 = require('sqlite3').verbose();\nconst { open } = require('sqlite');\nconst dbname = \"member.db\";<\/code><\/pre>\n\n\n\n
2)open()\ud568\uc218 \uc0ac\uc6a9(Using the open() function) or new sqlite3.Database()<\/p>\n\n\n\n
  const db = await open({\n        filename: 'member.db',\n        driver: sqlite3.Database\n  });<\/code><\/pre>\n\n\n\n
const db = new sqlite3.Database('member.db');<\/code><\/pre>\n\n\n\n
4.\ub370\uc774\ud130\uc785\ub825(Data input) [ insertDB() ]<\/strong>
– members\ud14c\uc774\ube14\uc774 \uc874\uc7ac\ud558\uc9c0 \uc54a\uc73c\uba74 \uc0dd\uc131\ud569\ub2c8\ub2e4.
– insert\uad6c\ubb38\uc744 \uc774\uc6a9\ud574\uc11c \ub370\uc774\ud130\ub97c \uc785\ub825\ud569\ub2c8\ub2e4. 
– (?,?,?,) \uc774 \ubd80\ubd84\uc5d0 id,username,room\uc774 \uc704\uce58\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
– If the members table does not exist, it is created.
– Enter data using the insert statement.
– The id, username, and room are located in this part. (?,?,?,)<\/p>\n\n\n\n
....      \n     await db.exec(`\n        CREATE TABLE IF NOT EXISTS members (\n            idx INTEGER PRIMARY KEY AUTOINCREMENT,\n            id TEXT,\n            username TEXT,\n            room TEXT\n        );\n      `);\n    \n      await db.run(\"INSERT INTO members (id, username, room) VALUES (?, ?, ?)\", id, username,room);\n      db.close();\n....<\/code><\/pre>\n\n\n\n
5.\ubaa8\ub4e0 \ub370\uc774\ud130 \ubcf4\uae30( View all data )[ showAllDB() ]<\/strong>
–  members\ud14c\uc774\ube14\uc758 \ubaa8\ub4e0\ub370\uc774\ud130\ub97c \uac80\uc0c9\ud558\uace0\ucd9c\ub825\ud569\ub2c8\ub2e4.
– All data in the members table is searched and output.<\/p>\n\n\n\n
  ...\n   await db.each(\"SELECT * FROM members\", (err, row) => {\n    if (err) {\n      console.error(err.message);\n    }\n    console.log(row.id, row.username, row.room);\n  });\n  \n  db.close();\n...<\/code><\/pre>\n\n\n\n
6.\ubaa8\ub4e0 \ub370\uc774\ud130 \uc0ad\uc81c(delete all data)[ deleteAllDB() ]<\/strong>
– members\ud14c\uc774\ube14\uc758 \ubaa8\ub4e0 \ub370\uc774\ud130\ub97c \uc0ad\uc81c\ud569\ub2c8\ub2e4.
– Delete all data in the members table.<\/p>\n\n\n\n
...\n  await db.run('DELETE FROM members');\n  await db.close();\n...<\/code><\/pre>\n\n\n\n
7.\ud568\uc218 \uc2e4\ud589(Function excute)<\/strong>
– \ucf54\uba58\ud2b8\ucc98\ub9ac \ubd80\ubd84(\/\/)\uc744 \uc0ad\uc81c\ud574\uc11c \uc2e4\ud589\ud569\ub2c8\ub2e4.
– Execute it by deleting the comment processing part ( \/\/ ).<\/p>\n\n\n\n
\/\/insertDB(\"240502\",\"helloworld1\",\"worldclass\");\n\/\/showAllDB();\n\/\/deleteAllDB();<\/code><\/pre>\n\n\n\n
8.\uc804\uccb4\ucf54\ub4dc(Full code)<\/strong><\/p>\n\n\n\n
const sqlite3 = require('sqlite3').verbose();\nconst { open } = require('sqlite');<\/mark>\nconst dbname = \"member.db\";\n\/\/const db = new sqlite3.Database('member.db');\n\nasync function insertDB<\/mark><\/strong>(id,username,room) {\n\/*\n  const db = await open({\n        filename: 'member.db',\n        driver: sqlite3.Database\n    });\n*\/   \nconst db = new sqlite3.Database(dbname);\n\n      await db.exec(`\n        CREATE TABLE IF NOT EXISTS members (\n            idx INTEGER PRIMARY KEY AUTOINCREMENT,\n            id TEXT,\n            username TEXT,\n            room TEXT\n        );\n      `);\n    \n      await db.run(\"INSERT INTO members (id, username, room) VALUES (?, ?, ?)\", id, username,room);\n      db.close();\n}\n\nasync function showAllDB<\/mark><\/strong>(){\n  \n  const db = new sqlite3.Database(dbname);\n  await db.each(\"SELECT * FROM members\", (err, row) => {\n    if (err) {\n      console.error(err.message);\n    }\n    console.log(row.id, row.username, row.room);\n  });\n  \n  db.close();\n\n}\n\nasync function deleteAllDB<\/mark><\/strong>(){\n\n  const db = new sqlite3.Database(dbname);\n  await db.run('DELETE FROM members');\n  await db.close();\n\n}\n\n\/\/insertDB(\"240502\",\"helloworld1\",\"worldclass\");\n\/\/showAllDB();\n\/\/deleteAllDB();\n\n\n<\/code><\/pre>\n\n\n\n