As a longtime proponent of phpMyEdit and MATE(MysqlAjaxTableEditor), I have appreciated the need for easy to use tools which allow users to interact with MariaDB (Mysql) tables in a spreadsheet-style grid interface.
This is very useful for more advanced users who are comfortable with spreadsheets as a user-interface since software coding development time is practically eliminated.
These are certainly easier to use than an interactive tool such as MySql-Workbench which is primarily intended for developers and DBA’s, but of course, these tools are nowhere near as robust.
Unfortunately, all of these wonderful productivity tools are restricted to PHP and MariaDB(Mysql) usage only.
The purpose of this article is to demonstrate a very similar approach for a spreadsheet-like interface, but to be used with NodeJS, and to have the back-end be as database agnostic as possible.
The backbone of this approach is FeathersJS. This author has researched a number of frameworks, including Rails, Sails, Trails, and Deepstream. While all of them certainly had their strengths, none of them were specifically written to handle relational database constructs.
FeathersJS is a totally amazing framework that provides a simple interface between Sequelize ORM and express.io.
And as a side-note, the developers of FeathersJS apparently are never satisfied with anything, so they have also added sails-waterline ORM, MongoDB, RethinkDB and many others. And again, never ones to stop, they even added a REST, Websockets, and Primus interface as well.
The new FeathersJS version aka “Buzzard” even has a front-end CLI that generates the skeleton, or scaffold, of your entire application for you simply by answering a few basic questions. (Do the feathers developers ever take time to sleep?)
The FeathersJS framework (may i call it a framework?) offers the best structure solution of any framework I have encountered. All interaction between the front-end and whatever databases (yes you may use more than one) selected offers wide variety of middleware hooks, where the developer can easily control the interface.
I will be using jsGrid as my front-end. Having tried several jQuery grid packages, I find jsGrid is the easiest to use and is very well supported.
For demonstration purposes, I will be using FastFeathers, a Github project I have started that utilizes TCL-Expect to answer all of the FeathersJS command-line questions.
In other words, it’s simply a series of bash scripts that literally do all the heavy-lifting work for you. The reader is strongly encouraged to inspect the bash-scripts before simply running them, as they are meant to be reviewed as well as executed.
FastFeathers scripts started as a very fast and easy way to get the FeathersJS chat example working, and then the script list was expanded to include different databases, oath2 chat login, and simple standalone oath2 examples. For a comprehensive list of FastFeathers bash-shell scripts, please see the README file.
You may use any database that FeathersJS supports. For the purpose of this article, I will be using CockroachDB.
My specific requirements call for Key-Value storage that still utilizes a conventional SQL interface and strongly supports multiple referential-integrity (parent-child) relationships. (Apologies to all the MongoDB/nosql enthusiasts).
My overly simplistic example only uses two columns, name and address. Also, I am displaying the primary key value and the email address of the person who last maintained the row as read-only values. This is just for illustration only.
Also, to keep life a bit more interesting, the FeathersJS and the CockroachDB will be hosted in separate countries, perhaps in a small effort to help reduce the increasing global trade tensions.
Regarding security, I will be requiring a Gmail address, although a Facebook or GitHub account could be used just as easily.
Finally, we will be starting completely from scratch using both
- Google VM instances
- Digital-ocean VPS
For demonstration purposes, I will be using Centos 7.5 for both servers, because Centos is the best Linux available. NOTE: when using any paid VPS like I will be demonstrating, use the most economical space/size available, as our requirements are minimal.
CockroachDB might require a bit more memory, but for demonstration purposes, the smallest amount should work. Of course, using cloud hosting is certainly not required, but it does make this exercise more enjoyable.
Our Google-VM CockroachDB server will be based in London.
The DigitalOcean Feathers server will be created on a VPS based in Toronto, as a tribute to the hardworking Feathers creators, who are actually in Vancouver BC.
Let’s move ahead to code.
1 – Requirements
At a minimum, this exercise will require a domain name. Please visit Namecheap.com and pick up a 99-cent domain name if you do not already have one. Set up your “A” record with the IP number of your feathers server. Personally, I like to set the TTL (time to live) records at one minute while testing due to an extreme lack of patience.
And of course, you will need at least one Linux server accessible by your domain name. Google’s implementation of oauth2 login credentials seems to require an actual domain name rather than merely using ‘localhost’.
I always like to start out creating a simple ‘hello world’ index.html file to ensure my domain name is working properly.
2 – Security
As stated earlier, we will be using your Gmail address as our grid login. Create a new API project using whatever name you wish if you don’t already have one. To obtain your login credentials, select OAuth client ID and enter your domain name as shown:
Please pay particular attention to the Authorized JavaScript origins and the Authorized redirect URIs (https://YOUR_DOMAIN_NAME/auth/google/callback) as it must point back to your domain name :
Notice the resulting Client ID and the Client secret. These values will be copy/pasted into the next section. Take this opportunity to double-check your Authorized JavaScript origins and Authorized redirect URIs settings.
Both of these values will be needed for FeathersJS authorization. Never share these values with anybody.
3 – BOTH SERVERS
Both servers will require the following series of commands.
Personally, I love vi(vim), so I enter the following:
we only need to run three commands on both of our servers (sudo may be required on your server):
[sudo] yum --assumeyes install git ; ## used to make the 'git' command available for github code
Next, we load the fastfeathers code after giving it a Github star:
Optionally, make sure that previous command worked properly:
4 – DATABASE SERVER
I believe it is easier to start with the database, so first, we will build a CockroachDB environment. We start by creating a CockroachDB work directory:
And now we install and initialize CockroachDB:
Our CockroachDB server should now be up and running. There are several example commands will display in the logfile, such as logging into the database, stopping the database server, starting, etc. Optionally, the ../fastfeathers/init-cockroach-client.bsh script may be run as well if a simple network connectivity test is required.
Notice the CockroachDB database name, login username and password, as well as the database server IP number either displayed or at the end of the resulting logfile. We will need these for our next step.
5 – FEATHERS SERVER
Before we begin, we will need the following seven items from the previous steps:
- Feathers Server Domain Name
- Oauth Client ID
- Oauth Secret
- Database Server IP Number
- Database Name
- Database Username
- Database Password
Create a directory and set it as the default:
Now, finally, we do some actual work. Lets set up our feathers environment, mainly installing Node/npm and Feathers. Please carefully review the ../fastfeathers/feathers-initial-setup.bsh script to see what all the script is doing.
This bash shell script will create several subsequent scripts, but in particular, it will create ./public/app.js, a required FeathersJS script. It merely combines the easiest FeathersJS example with the simplest jSgrid example.
A very small amount of extra html/jscript code is added to the ./public/index.html for the Gmail oath2 login/logout links near the top of the page. This code is certainly not a requirement, but it makes the demonstration easier to use.
After reviewing the script, run this command:
Voila! We now have a FeathersJS work environment.
Next, you will either need to do one of the following for our seven required values:
- Manually edit the ../fastfeathers/jsgrid-sequelize.bsh script using vi(m) before it has been executed, and add the values. (this is the preferred method)
- Manually edit the ./config/default.json after the jsgrid-sequelize.bsh script has completed.
- Run the ./fastfeathers/jsgrid-sequelize.bsh script as is, and be prompted for values.
To run the jsgrid-sequelize.bsh script, enter the following:
And lastly, start it up!
Optionally, run it so our app stays alive long after you log out:
Now, go reward yourself by visiting your chosen domain name with ‘:3030’ as a suffix.
Note that to add a new record, you must click the green plus symbol top-right. Otherwise, the empty text fields serve as search filters.
jsGrid and feathers/sequelize combined to talk to CockroachDB
Conclusion
Notice how few lines of javascript are required beyond the most basic jsGrid example to interface with feathersJS. And most of the extra code is just for example code such as saving the login information.
