How to Connect SQLite Database with Node.js without Frameworks

Hey, in this tutorial we're gonna go over how to connect to an SQLite database, get some data from a table, and show it in the console. We'll be sticking to just Node.js without any fancy frameworks, so if you're looking to learn the basics of dealing with databases in Node.js, this is the perfect tutorial for you.

First of all we need to install sqlite3 library

npm install sqlite3

Check out this code example on how to connect to your SQLite database and grab the latest posts:

const sqlite3 = require('sqlite3').verbose();

// Abre la base de datos
const db = new sqlite3.Database('ruta/a/la/base/de/datos.db');

// Obtiene los últimos 5 posts de la tabla "posts"
db.all('SELECT * FROM posts ORDER BY created_at DESC LIMIT 5', (err, rows) => {
  if (err) {
    console.error(err.message);
  } else {
    console.log(rows);
  }
});

// Cierra la conexión de la base de datos cuando hayas terminado
db.close();

If you want to use it as an API, I have good news for you: we improved it! Update your index.js file. Now it has a web server and a /posts route. Just don't forget to run the "node index.js" command to start the server. Then, to see the result, go to your browser and enter the link 127.0.0.1:3000/posts. Enjoy it!

const http = require('http');
const sqlite3 = require('sqlite3').verbose();

const PORT = 3000;

const server = http.createServer((req, res) => {
  // Check if the requested URL is /posts
  if (req.url === '/posts') {
    // Open the database
    const db = new sqlite3.Database('path/to/database.db');
    
    // Retrieve the last 5 posts from the "posts" table
    db.all('SELECT * FROM posts ORDER BY created_at DESC LIMIT 5', (err, rows) => {
      if (err) {
        console.error(err.message);
        res.statusCode = 500;
        res.end();
      } else {
        // Return the results as JSON
        res.setHeader('Content-Type', 'application/json');
        res.end(JSON.stringify(rows));
      }
      
      // Close the database connection
      db.close();
    });
  } else {
    // Handle other requests here
    res.statusCode = 404;
    res.end();
  }
});

server.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

So, in this example, we start off by importing the sqlite3 module and creating a new instance of the Database class, with the path to our SQLite database file. Then, we use the all() method to grab the latest 5 posts from the "posts" table, ordering them by the "created_at" column in descending order. The results are returned in the rows array. Finally, we close the database connection using the close() method.

Just keep in mind that this example assumes you already have an SQLite database with a "posts" table that has a "created_at" column. If your table schema is different, you may need to tweak the SQL query to fit your needs.