Conclusions regarding building SPAs in 2019

storeboss icon
The new StoreBoss icon!

I spent the last week researching and testing out the various options I have available to build StoreBoss.

The use case of StoreBoss is Cash Balance Ledger accounting.

The idea is to combine data from WooCommerce, credit card processors like Stripe, and a store owner’s bank — in order to automatically verify an order’s processing flow.

Based on this information, StoreBoss can then provide a variety of temporally-based analytics — beyond the usual charts and graphs you see in many e-commerce db front end apps.

My goal is to develop unique and useful data visualization functionality — while ensuring that access to such information is no more difficult for a non-technical store owner than tapping a screen to wake up a smartphone.

My ace in the hole in all this was that I have rusty but still decent db skills– acquired through a career of working on trading systems on Wall Street.  That’s pretty much it in terms of any ace-in-the-hole advantage!

During the past month, after prevaricating on the issue of PHP/WP admin screen vs some other implementation, I came to the conclusion that I wanted StoreBoss to be a single page app that primarily lives on the desktop.

After some debate regarding the suitability of Python for this project, I discarded that option in favor of a single language JS approach.

I took a week to learn enough about node.js and npm to be able to write a few simple scripts that access tables in a test SQLite database.

I also worked through my issues with callback hell, and ramped up from old school error handling –> to promises –> to async/await –> until I finally came across a really cool async utility that does the job of smoothing the pain through syntactic sugar.

I also surveyed the intimating and confusing JS framework landscape, and also took a long look at the development and evolution of node.js over the last 10 years, as well as its gradual intermarriage with npm.

I will be upfront about something.

I did not particularly like this.

It brought up all kinds of code ownership issues, those same ones that arise with MS-owned Github.

I also do not like how npm’s original developers and people who were crucial to its original development were ultimately screwed.

I was aghast at when Terminal ads began appearing.  The idea that ads are bundled in packages smells to high rot.

As for node.js, it gave me pause to watch a tube vid featuring Ryan Dahl on the mistakes he made with node — which he, of course, invented, and left behind to build Deno.

I also am turned off by the massive code size that is apparently required to generate a simple “hello, world” executable using the npm/node duopoly — and intend to experiment with ES modules to see if I can do away with npm entirely.

I don’t want StoreBoss to turn into some 20 or 50 or 100MB Windows blotto .exe behemoth that a store owner must download and install on his or her machine.

But worse of it, Node is categorically and fundamentally insecure.

How do I get a StoreBoss end-user to trust this application if its runtime environment could allow, say, the theft of SSH keys on his or her machine?

Given the fact that, as CEEJ once said, if you’re using node, you’re using npm, and vice versa, what’s an alternative?

deno

+

 

 

Given the issues surrounding the npm + js stack, can I simple ditch JS entirely?

Specifically, does Typescript support modules?  Ans.  from the documentation

Starting with ECMAScript 2015, JavaScript has a concept of modules. TypeScript shares this concept.

What about the SQL3./node module?  Is there a Deno equivalent?

Alas, no.

And without a driver/binding from Deno to SQLite….

Okay… so even I have to stay with Node/JS, despite the issues, how do I get JS to render HTML/CSS without getting entangled in a Jurassic framework?

Well, I’m going to take the plunge and develop StoreBoss as a lightweight Vue.js 3.0 app.

As of this writing, Vue 3.0 is not yet officially released, and I have never actually used Vue.js  — but the documentation, demos, YouTube talks, Medium articles, and programmer feedback on Vue are all consistently impressive.

This is not going to happen overnight.

In the meantime, over the next 20 days (before my Sigma trial expires), I am going to complete doing my study of Stripe’s schema.  I am not just going to lift SIGMA’s table structures — what’s the fun in that? … but redesign them such as to be generalized, such as to also support, say, Paypal or even a B&M CC batch processor.

Then I will take a really big step, and write the JS needed to call the Stripe API in real-time,

Lots of work ahead.

I can’t wait to see the results!

Advertisements

Calling SQLite using Javascript

windows cmd line
INPUT

After a bit of google this and google that, I figured out how to submit an SQL call to SQLite.  This code uses node.js and SQLite3 to call a database I created called ‘sandbox.db’ that resides on a Windows machine  – the same setup that the majority of StoreBoss end users are likely to have running in their back office.

It took me a day to fgure out the basics of npm, node, and SQLite3. It’s not too bad, once you get the hang of it.

Next I will figure out how to create an .exe package using node and this js file.

After that, I will figure out how to repeat this process, but with added HTML and CSS files, such that I can display the data in the responsive custom tables layouts I’ve already created.  If I can get all this to work. then I will know that i can produce a compiled StoreBoss executable that runs on the desktop, no fuss, no muss — and without having to resort to some opinionated, overbearing framework..

Here’s how I did it.  I don’t have an error handler at the end, because this is just a demo. proof of concept kind of thing. Pay attention to where the closing paren is for the functions.  This is how I understood how callbacks work.  Now all I have to do is extract the call statements from this file, and place them in a main index.js one, and voila, that’s my ticket out of callback hell: forcing javascript to behave procedurally, which is what it does anyway, underneath all the async gunk.

 

// filename: sqlite.js
// author: gounaman

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

var testdb = "sandbox.db";

function opendb(testdb) {

var db = new sqlite3.Database(testdb, (err) => {
if (err) {
return console.error(err.message);
}
console.log('Connected to SQlite database.');
}
);

return db;
}

var db = opendb(testdb);

function getrows(db) {

db.all("SELECT * FROM batch_vw;",
(error, rows) => {
rows.forEach( row => console.log(row));
});

};

getrows(db);

function closedb(db) {
db.close();
}

closedb(db);

windows cmd line
OUTPUT

And the real beauty of this is that I can now write SQL to my heart’s content without hacking it to pieces with ORM and sanitization.

Life is good again.

ODBC bindings for SQLite/Node.js

 

html page
The birth of StoreBoss

So here’s the latest on my plan to come up with the easiest, simplest way to write an intuitive, nice looking Woo-related browser app that’s able to connect in a very simple way to a local datastore on the desktop.

There is only one problem: unlike, say, C, JS has no built-in file I/O capabilities.

In the case of wanting to access SQLite, you have to use SQLite3 + node + npm,  All this to accomplish what can be done in one line of code in C or a C++ program that uses ODBC to connect to SQL Server.

And if you ever had to write a JS script that uses SQLite3, you will soon begin to rue the absolutely maddening error-prone tedium of the unnecessary complexities of error handling when using SQLite3.

All I want to be able to tell JS to (1) connect to SQLite then (2) open a DB then (3) retrieve data from a table.

This is not exactly rocket science. I don’t think the user would mind waiting for a split second while a few hundred rows are retrieved in blocking, synchronous mode.

But… no.

Instead of just being able to connect to SQLite synchronously, as you would if you were writing your app in C, you’re forced to deal with SQLite3 + node.js + npm in order to access SQLite.

Adding all these massive dependencies (over which you have little control, results in a situation where you have no idea what is really going on on your machine behind the scenes): massive security hole, obviously.

Why on earth was SQLite3 not built to also support synchronous I/O?

After all, remember the SQLite mantra: “open-sourcezero-configurationself-containedstand-alone… ” etc etc?

And remember, too, that the intended number of simultaneous end users is exactly 1. In this scenario, it is RIDICULOUS to be forced to make asynchronous calls to a local db.

Plus which, when it comes to SQLite3, does it ever mentioned (gulp)

MUTEX THRASHING

in the documentation? Well, does it, punk?

This NJS (NPM + JS + SQLITE3) Rube Golberg jive is the antithesis of that stated SQLite ideal, or am I wrong?

 

ruth golberg stamp

Then, I thought… why not ODBC?

For a moment, I allowed myself to fondly remember those MS Access days wayback in the 90s when I was able to use ADO to grab data from almost any source, no fuss, no muss, and thank you very much.

My mood began to turn increasingly dark, at the unfairness of Life, as I googled frantically.  Then, the clouds parted, and I found… this.

sqlite odbc driver

I found it!

An ODBC driver that’s MIT licensed and recommended by no less a reputable source than Microsoft.

I quickly added it to my ODBC data sources in the Administrative Tools of the Control Panel in Windows.

Libre Base

Then I tested it with Libre Base.  It worked, with some minor fiddling (see below), like a charm.

Then I tested it again, this time with Microsoft Power BI (which I noticed, by the way, is developing connectivity to Stripe as a data source).

Power BI connect

That worked fine, also.

So the driver ran as advertised.

But I was not out of the woods yet.

Since Javascript cannot work with ODBC directly for a variety of technical and security reasons, I had to find the….

MISSING LINK.

And I did.

On Github.

Here.

So all I had to do now is figure out how to get my JS StoreBoss DB function library to work like this.

ODBC bindings for node

Now that is what I call beautiful. Except that it turned out to be a really bad idea (see Postword below).

Postword:  After installing this complex JS chromium runtime extension (which requires you to use an elevated Admin CLI), I could not get the example code to work, and left a call for help in its git page.  I say bag this native extension stuff; too much of a headache. But if you can follow what Gabriel Schulhof says about native add-ons in this YouTube bid, please leave a comment!

UPDATE #1:  After several re-installs (since this native extension trashed my node/npm setup) of npm + node + SQLite3, I was able — finally! — to get node + sqlite3 to work together.  I tested this by entered my js statements into the node command line, one by one  Great way to debug connectivity issues to SQLite via SQLite 3, when you are first setting things up, btw.  End result: I was able to connect to my sandbox db on SQLite, and output a result set from a table I had created earlier and populated using DB Browser.  Tomorrow I will try to get node to run a simple .js file that selects data from SQLite and displays it in the console asynchronously. Meanwhile, time to study the Node.js chapter in Eloquent Javascript. The guy who wrote this classic has deep reservations about node, btw.

UPDATE #2:  Hello, hello.  What’s this?  Let’s first look at the Trouble Shooting guide.  On Windows, as opposed to UNIX derivatives, things are more complicated (as always).  Let’s see… hmmm.

  1. Start an Admin PowerShell: Right-click the start icon, then pick Windows PowerShell (Admin)
  2. Install both vs2015 and vs2017 libraries. Each line will take ~5-10 minutes.
    npm install --global --production --vs2015 --add-python-to-path windows-build-tools
    npm install --global --production --add-python-to-path windows-build-tools node-gyp

That is what I had to with the native extension, and it trashed my setup.  I’ll not be installing anything again that forces me to use Admin privileges, so bag that.  Too dangerous, and what if an end-user has to change his or her machine’s configuration to get this package to run?  StoreBoss would be dead in the water. Troubling issues page, too.

UPDATE #3  The odd thing is, Javascript is actually synchronous at its core.  Promises and Callbacks were contortions that were added later, to avoid code having to wait for things like  long-running db queries to return results. Async/Await is Promises syntactic sugar that aims to make this mess more human-readable.  See Hopehne articles here and here, with lots of useful background reading references included.