Variable is still undefined even though reassignment occurs

For some reason the following code doesn’t work:

const sqlite3 = require("sqlite3").verbose();
const fs = require("fs");

const databaseFile = "/data/allPosts.db";
const dbWrapper = require("sqlite");

const databaseExists = fs.existsSync(databaseFile);

let database;

const CODES = {
  SUCCESS: 0,
  FAILURE: 1,
};

dbWrapper
  .open({
    filename: databaseFile,
    driver: sqlite3.Database,
  })
  .then(async dBase => {
    database = dBase;
    try {
      if (!databaseExists) {
        //Define types
        await database.run(
          "CREATE TYPE STRING_ARR AS VARCHAR(1000) ARRAY[500]"
        );
        await database.run(
          "CREATE TYPE POST AS TABLE (user VARCHAR(255), likes INTEGER, postContents VARCHAR(10000), title VARCHAR(40), comments STRING_ARR)"
        );

        //All posts
        await database.run(
          "CREATE TABLE AllPosts (user VARCHAR(255), likes INTEGER, postContents VARCHAR(10000), title VARCHAR(40), comments STRING_ARR)"
        );

        //Users
        await database.run(
          "CREATE TABLE AllUsers (name VARCHAR(255), liked POST ARRAY[500], daily INTEGER)"
        );
      }
      else {
        console.log(await database.all("SELECT * from AllPosts"));
      }
    }
    catch (err) {
      console.error(err);
    }
  });

module.exports = {
  insertUser: async (userName) => {
    try {
      let doesUserExist;
      let statement;
      
      statement = database.prepare(
        "SELECT name FROM AllUsers WHERE name = ?"
      );
      //throw new Error("" + +doesUserExist)
      doesUserExist = typeof (await statement.get(userName)) !== "undefined";

      if (doesUserExist) {
        return CODES.FAILURE;
      }
      await database.run(
        "INSERT INTO AllUsers (name, liked) VALUES (?, 0)",
        userName
      );
      return CODES.SUCCESS;
    }
    catch (err) {
      return (
        err.stack.substring(0, 100) + " |||||| DATABASE VALUE = " + database
      );
    }
  }
};

It’s giving the error “cannot find property ‘prepare’ on undefined”, and I found out that database is undefined, even though there is an assignment going on.

Here’s one of the hard things about programming something that does anything other than run in a straight line from top to bottom. You have to reason about what happens when. I searched through your code to find what assigns the database variable, and it’s this line:

...
  .then(... {
    database = dBase;
    ...
  })

We can also look at what line that “cannot find property ‘prepare’” error by looking at what lines do .prepare, and it’s this:

module.exports = {
  insertUser: ... {
    ...
    statement = database.prepare(
      ...

So what in your program ensures that the database = ... happens first? The database.prepare line comes from a function that you export, so it’s not even clear from this file alone. You’d have to analyze what imports this module.

I have a solution in mind for this, as I’ve written a similar program. But what are you thoughts on this problem? And do you have any ideas how you’d solve it yourself?

I think you need to define database again within that try statement. What if you add it under ‘let statement’ ?

Also, what is the purpose of database = dBase?

I tried doing

if (!database) {
    database = new sqlite3.Database(databaseFile);
}

but then Glitch gave me a “Site didn’t respond” error

Can you post a link to your project?

https://hyperstream.glitch.me/

I will say I’m not too familiar with sqlite. I’ve used Sequelize with Node, but not in a while… But either way under server.js you’ve got:

const serverDb = require('./sqlite.js')

and then under sqlite.js you’ve got:

database = dBase;

I think you need to keep variables consistent, and from what I’m seeing, there’s no reason to rename the database variable.

You’re also checking for a database twice in your sqlite.js file. You should only have to do that once.

You may want to take a closer look at the sqlite starters on Glitch. I’m not sure how they compare to what you’ve created here or if you’ve utilized them already. if you find sqlite confusing, Sequelize is another alternative to using SQL with Node. https://sequelize.org/ You can install the package and try it out if it looks more manageable.

Personally I prefer Mongoose and MongoDB when I build my servers and API’s with Node. I find they are easier to set up but then again, if you really want to use a SQL DB, SQL is pretty easy to learn IMO.

hi christina, the naming is a little confusing but:

  • sqlite3 = require("sqlite3").verbose() gives us the low-level module with classes that wrap the actual interaction with sqlite. it’s not a connection to any database
  • dbWrapper = require("sqlite") is a wrapper around the sqlite3 library that uses promises or something. but it’s still not a connection to anything
  • dbWrapper.open(...).then(async dBase => { dBase connects a specific database, and we receive an object representing that connection under the name dBase
  • database = dBase assigns a copy of that reference to the module-scope variable database, so that it’s accessible outside this then callback (although this is problematic as there are points in time before the connecting when database is still undefined)
  • within module.exports = { insertUser: ... => { ... database defines higher level application-specific functions that use database (consequently, it’s an error to call them before connecting)
  • ./sqlite.js is probably that file they posted above (seems they rewrote it in mongoose since the question was posted), which exports that {insertUser: ... } dictionary of application-specific functions
  • serverDb = require('./sqlite.js') gives us a reference to that dictionary

thus these things all have different names, but that’s not due to an inconsistency. they really are all different things

Oh ok. What about this part then:

database = dBase /*assigns a copy of that reference to the module-scope variable database, so that it’s accessible outside this then callback (although this is problematic as there are points in time before the connecting when database is still undefined) */

dBase is the local callback parameter, while database is the module-scope variable

Wouldn’t it be ok to just use dBase without defining database at the top at all?

I think I worded my response poorly by saying variable names need to be consistent. serverDb also seems like a poor choice for a variable name, considering we also have dBase (and database).

with other code changes to make sure everything can lexically refer to dBase, yes. that should also solve these problems described in the original post, as dBase would never be undefined.

but we should think about whether those code changes–potentially extensive–would be desirable. moving everything into the then callback where dBase is in scope is the first step of what I’ve seen referred to as the “pyramid of doom”