db.
js
const sqlite3 = require('sqlite3').verbose();
// Function to initialize the database schema
function initializeDatabase() {
const db = new sqlite3.Database('./mydatabase.db', (err) => {
if (err) {
console.error(err.message);
} else {
console.log('Connected to the SQLite database.');
createStudentsTable(db); // Create the 'students' table if it doesn't exist
});
// Close the database connection when the Node process exits
process.on('exit', () => {
db.close((err) => {
if (err) {
console.error(err.message);
} else {
console.log('Disconnected from the SQLite database.');
});
});
// Function to create the 'students' table if it doesn't exist
function createStudentsTable(db) {
const createTableQuery = `
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER,
grade TEXT
);
`;
db.run(createTableQuery, (err) => {
if (err) {
console.error(err.message);
} else {
console.log('The students table has been created or already exists.');
});
module.exports = { initializeDatabase };
// 👇 This line runs the function when you run the script directly
initializeDatabase();
app.js
const express = require('express');
const sqlite3 = require('sqlite3');
const { initializeDatabase } = require('./db');
const app = express();
const port = 3000;
// Connect to SQLite database
const db = new sqlite3.Database('./mydatabase.db', (err) => {
if (err) {
console.log(err.message);
} else {
console.log('Connected to the SQLite database.');
});
// Middleware to parse request body as JSON
app.use(express.json());
app.get('/', (req, res) => {
res.send('Welcome to the Student API');
});
// Get all students
app.get('/students', (req, res) => {
db.all('SELECT * FROM students', [], (err, rows) => {
if (err) {
return console.error(err.message);
res.json(rows);
});
});
// Get a single student by ID
app.get('/students/:id', (req, res) => {
const id = req.params.id;
db.get('SELECT * FROM students WHERE id = ?', [id], (err, row) => {
if (err) {
return console.error(err.message);
res.json(row);
});
});
// Create a new student
app.post('/students', (req, res) => {
const { name, age, grade } = req.body;
db.run('INSERT INTO students (name, age, grade) VALUES (?, ?, ?)', [name, age, grade], function
(err) {
if (err) {
return console.error(err.message);
res.status(201).json({ id: this.lastID });
});
});
// Update a student
app.put('/students/:id', (req, res) => {
const id = req.params.id;
const { name, age, grade } = req.body;
db.run('UPDATE students SET name = ?, age = ?, grade = ? WHERE id = ?', [name, age, grade, id],
function (err) {
if (err) {
return console.error(err.message);
res.json({ updatedID: id });
});
});
// Delete a student
app.delete('/students/:id', (req, res) => {
const id = req.params.id;
db.run('DELETE FROM students WHERE id = ?', id, function (err) {
if (err) {
return console.error(err.message);
res.json({ deletedID: id });
});
});
app.listen(port, () => {
console.log(`Server running at http://localhost:${port}`);
});
