LIVE DEMONSTRATION
A working example of this tutorial will be available for demonstration at http://fastfeathers.site:3030/ through the end of 2018, and possibly longer based on demand.
Introduction
As a longtime proponent of phpMyEdit and 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 a tool such as MySqlWorkbench which is primarily intended for developers and DBA’s, but of course, these tools are nowhere near as robust.
Unfortunately, both of these wonderful tools are restricted to LAMP usage only.
The purpose of this writing 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 connection 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 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.
Please consider donating to the FeathersJS project.
I will be using jSgrid as my front-end. Having tried several jscript grid packages, I find jsGrid is the easiest to use and is very well supported.
For demonstration purposes, I will be using FastFeathers. FastFeathers is a Github project I have started that utilizes TCL-Expect to answer all of the FeathersJS CLI 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 referential-integrity (parent-child) relationships. (Apologies to all the MongoDB 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
For demonstration purposes, i will be using Centos 7.5 for both server, 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.
The Feathers server will be created on a VPS based in Toronto, Canada, as a tribute to the hardworking Feathers creators, who are actually in Vancouver.
Our CockroachDB server will be based in London.
I – Requirements
At a minimum, this exercise will require a domain name. Please visit Namecheap 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 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.
II – 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 http://YOUR_DOMAIN_NAME/auth/google/callback
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.
Notice underlined Client ID and Client secret
Both of these values will be needed for FeathersJS authorization. Never share these values with anybody.
II – BOTH SERVERS
Both servers will require the following series of commands.
Personally, I love vim, so I enter the following:
set -o vi ; ## this really needs to go into your .bashrc file
we only need to run three commands on both of our servers (sudo may be required on your server):
[sudo] yum --assumeyes update ; ## arguably this command is optional.
[sudo] yum --assumeyes install git ; ## used to make the 'git' command available for github code
Next, we get the fastfeathers code after giving it a Github star:
git clone https://github.com/edwardsmarkf/fastfeathers ; ## get our fastfeathers code after giving it a github star.
Optionally, make sure that previous command worked properly:
ls -l ./fastfeathers/ ; ## nineteen files and counting.
II – 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:
mkdir ./CockroachDB/ ; cd ./CockroachDB/ ; ## create the directory and enter it.
And now we install and initialize CockroachDB:
bash -vx ../fastfeathers/init-cockroach-server.bsh > ./init-cockroach-server.bsh.log ; ## optionally save the results
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.
IV – 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:
mkdir ./FastFeathers/ ; cd ./FastFeathers/ ; ## create the feathers directory
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. This script merely combines the easiest FeathersJS example with the simplest jSgrid example. A very amount of extra html code is added to the ./public/index.html for the login/logout links near the top of the page.
After reviewing the script, run this command:
bash -vx ../fastfeathers/feathers-initial-setup.bsh ; ## initialize the feathers environment
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 before it has been run, 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:
bash -vx ../fastfeathers/jsgrid-sequelize.bsh ; ## create the jsgrid-sequelize FeathersJS app.
And lastly, start it up!
cd ./jsgrid-sequelize; npm start; ## change directory and start our jsgrid-feathers app
Optionally, run it so our app stays alive long after you log out:
nohup npm start <ampersand> ## nohup stays connected after logout and the ampersand runs in the background
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 code is required beyond the jsGrid basic example to interface with feathersJS. And most of that code is just example code such as saving the login information.