Node Starter app: SQL Lite *and* Webpacker

Coding Help
1

I can find a Glitch Node starter app that uses SQL Lite. And I can find a starter app that uses webpacker. But I’m struggling to create a starter app that uses both. I can’t figure out how the package.json should be configured.

I spun a few hours on it and got stuck. I’ll try again at some point, but I wanted to throw this out to the community.

Has anyone done this or would anyone like to tackle this? I feel like this might be a good remixable app that others would benefit from.

2

Hey, @krschacht. I’m sorry you hit a wall after all that time! Integrating different packages is sometimes a daunting task, especially with bundlers (Webpack, Rollup, etc.). Thankfully, utilizing both Webpack and SQLite in one project is actually not as complicated as it may seem. While I don’t know of any starter projects with both of them already (I’m sure someone has one available to remix on Glitch, getting to that point manually only takes a few steps on Glitch.

Webpack is a much more complicated beast than SQLite. Although it might be a rewarding task to delve into the intricacies of Webpack to better understand it for future project integrations, it may be out of the scope of whatever you’re working on to take that time to study it. I suggest you use the core of the official Webpack Glitch project for the base of what you’re working on, whether it’s through remixing it or simply using the Webpack integration in the starter project.

Incorporating SQLite in a project, with or without Webpack, is a walk in the park. SQLite databases are actually singular files you can tangibly move around. You can think of them as JSON files except in a file format better suited for database queries.

For example, a project might use a file named storage.sqlite as its database. Reading and writing to that database could then be done through a myriad of utilities and libraries as long as they integrate with SQLite itself. The simplest way of accessing an SQLite database is through the official SQLite shell (confusingly referred to as sqlite or sqlite3 on the command line).

If you’re running a Unix-based operating system, there’s a 99% chance you already have the SQLite shell available to use.

Screen Shot 2020-04-11 at 9.06.30 PM
Screen Shot 2020-04-11 at 9.06.30 PM1170×730 117 KB

To use SQLite with Node.js, there are more than just a few SQLite packages on npm to use. My recommendation is simply to use the most popular one: sqlite3.

const sqlite3 = require("sqlite3");

// Values to save
const animals = [
    "Dog",
    "Cat",
    "Rat",
    "Iguana"
];

// Load the database
const database = new sqlite3.Database("./storage.sqlite");

database.serialize(() => {
    const statement = database.prepare("INSERT INTO `animals` VALUES (?)");
    
    database.run("CREATE TABLE IF NOT EXISTS `animals` (`type` TEXT)");
    
    for (var i = 0; i < animals.length; i++) {
        statement.run(animals[i]);
    }
    
    database.each("SELECT `rowid` as `id`, `type` FROM `animals`", (error, row) => {
        if (error) {
            throw error;
        }
        
        console.log(`${row.id}: ${row.type}`);
    });
    
    // Complete the transaction
    statement.finalize();
});

// Release the lock on the database file
database.close();

Should you choose to use sqlite3 to interface with your SQLite database, your new package.json file should be modified to include it under the dependencies entry like so:

   },
   "dependencies": {
-    "express": "^4.15.2"
+    "express": "^4.15.2",
+    "sqlite3": "^4.1.1"
   },
   "devDependencies": {
1 Like