Monday, September 9, 2013

A Very Simple Demonstration of Node.js/Express and SQLite

Obligatory Boring Preamble

At the risk of disappointing all zero of my loyal followers, today we'll take a break from the C#.NET ecosystem and try something totally different.

Server side JavaScript got pretty popular about two years ago (about 2011 - in case you're reading this from the future), and it has matured to the point where new-"ish" tech adopters like me are willing to give it a try - actual tools exist, people are using it for real projects in production, and it's no longer attracting as much attention from "latest thing" fad seekers. (Don't get me wrong. I love new things - but I have learned to love new things that are *useful* for doing *work*.)

So for today's installment, let's create an app that brings these two great tastes together, and as usual, share the goods for you to (hopefully) steal.

The Technology Stack

Like most of the stuff I have the luxury of posting on a public blog, this is going to be pretty basic.

For development, we're going to use:

  • Node.js
  • Eclipse (free multi-language IDE available here)
  • Nodeclipse (free Eclipse plugin for developing Node apps, available here)
  • Node Package Manager (NPM) (free package manager for Node.js modules available here)


And once we've got NPM installed and working, we're going to use it to install a few packages:

  • Express.js ("a minimal and flexible node.js web application framework")
  • Jade (a templating engine for generating our HTML user interface)
  • node-sqlite3 (Asynchronous, non-blocking SQLite3 bindings for Node.js)


The Finished Product

We'll end up with a small Node.js app that allows you to create and view SQLite3 databases, and modify some of their elements (create new tables and columns). The app will also demonstrate a few elements of Express.js - the routing of get and post requests, as well as some basic features of templating with Jade.

If you want to play with it yourself, you can clone the project files from my Git repository after following along with the setup we'll do.

Setup

First, download and install Node.js from http://nodejs.org/.

Next, install Eclipse (and any pre-requisites like Java) from http://eclipse.org/recommenders/download/.

Once Eclipse is installed, install the Nodeclipse plugin:

  1. Go to the Eclipse's Help menu and choose Install New Software
  2. In the "Work With" field, paste this URL: http://www.nodeclipse.org/updates/
  3. Click the 'Add' button


Building the dev environment


When you install Node.js, it puts an icon in the main Start menu, as well as a program group.

This one will let you run Node.js server-side JavaScript in a console and see the output directly.


So, typing "Hello World" at the prompt will echo "Hello World". If you've ever used Firebug for trying out JavaScript, this does more or less the same thing.

What you'll probably end up using more often is the node command prompt:


This lets you start/stop/restart Node.js applications as well as use the Node Package Manager (hereafter referred to as npm). For starters, let's just see what versions of everything we've got:


So far so good! 

Next let's create a new Node project in Eclipse, so that we can start installing modules and get a skeleton site up.

In Eclipse, choose New > Node > Node Express Project > Next

Give you project a name and click Finish. Look at the output in Eclipse's console window:


Note that the Nodeclipse plugin has already done some work for us: it used npm to install 2 local node modules: Express (the web framework we'll be using to route requests and responses), and Jade (the templating engine we'll use to generate HTML output to the browser).

(Note, if your output looks garbled you might have Eclipse's text file encoding set to the wrong character set. Set it to UTF-8 to get the output shown above. See here for more).

You could have done this yourself by opening a Node.js command prompt, navigating to the root of your project directory, and typing: 

npm install express
npm install jade

Ok. It's time to do our first "smoke test" (fire it up, see if it smokes). While still in the Node.js command prompt, type:

node app.js

Open a browser, and open the URL http://localhost:3000

You should see the following:


And if you look back at your console output, you'll see some activity:



Looking at the project structure in Eclipse, we can see that (for now), app.js is where the action is. It's the entry point into our application, as well as containing the code that sets up the environment and starts the http listener. 

We also see that the Nodeclipse plugin generated a few other files and directories: 
  • views/layout.jade - the basic page template that other jade views extend
  • views/index.jade - the default landing page for our app
  • routes/index.js - the code that tells Node.js/Express to render the index page
  • Stylesheets, etc.

Adding a simple page


First, create a new express route. In your Eclipse project:
  1. Right click on routes
  2. Choose New > Node Source File
  3. Call it 'test.js' and click Finish
  4. Copy and paste the contents of index.js into test.js
  5. Replace exports.index with exports.get
  6. Replace res.render('index', { title: 'Express' }); with res.render('test', { title: 'Test Page' });

Next, create a new jade template:
  1. Right click on views
  2. Choose New > General > File
  3. Call it 'test.jade' and click Finish
  4. Copy and paste the contents of index.jade into test.jade
  5. Replace p Welcome to #{title} with   p This is a #{title}

Next, register the route as a module dependency in app.js. 
  1. Open app.js
  2. After the line var path = require('path'); add var test = require('./routes/test');
  3. After the line app.get('/users', user.list); add app.get('/test', test.get);
Try it out - stop the Node.js app by typing <CTRL> C, then run node app.js again. This time, navigate to http://localhost:3000/test. If it all went Ok, you should see:




A few notes about what we did. For simplicity, I used the word 'test' for everything - the name of the route, view, variable, etc. But you don't have to! Node.js/Express doesn't use "convention over configuration". The things to know are:
  • Whatever you name the view file needs to match the call to render it. So you could have a file called wombat.jade and call it from test.js.get() with
      res.render('wombat', { title: 'Test Page' });
  • You can call the variable in app.js anything you want, as long as its pointing to the right file (and calls the same methods). So
      var aardvark = require('./routes/test'); would work, as long as you called the aardvark.get() method. 
  • You can call the method in test.js anything you want, as long as you register it in app.js. So 
      exports.jabiru(){...}
    would work as long as you changed the line in app.js to
      app.get('test', test.jabiru); 
  • You can map the URL from anything you want. So    app.get('/Mah/RESTFul/Test', test.get); would work. This is pretty powerful (well, if you grew up with much more awful oldschool ways to map URLs to code).

    You can also use wildcards, e.g.,       app.get('/Mah/*/Test', test.get);
    or placeholders for variables, so that       app.get('/products/:productName/details', product.details); would pass whatever is in the section of the URL between 'products/' and '/details' as a variable for the product.details method to use.

Let's try one more tweak to our test view/route before we move on to the good stuff - let's add a post method to accept data from the browser.
  1. Add the following method to test.js:

    exports.post = function(req, res){
 
    var name = req.body.name;
 
    console.log("Received POST data: " + name);
 
    res.render('test', { title: 'Test Page designed *exclusively* for ' 
        + name });
 
};
  2. Map the method in app.js. Add:

    app.post('/test', test.post);
  3. Extend the jade template. The complete page should read:
    
extends layout

block content
  h1= title
  p This is a #{title}
  
  div
    form(method='post')
      p Enter your name:
      input(type='text',name='name',id='name')        
      input(type='submit',name='submit',value='Submit')
Note that jade is indent-sensitive! Indented elements are considered to be "children" of the elements before them. So the jade file above will result in the following HTML:

<!DOCTYPE html>
<html>
  <head>
    <title>Test Page</title><link rel="stylesheet" href="/stylesheets/style.css">
    </head>
  <body>
    <h1>Test Page</h1>
    <p>This is a Test Page</p>
    <div>
      <form method="post">
        <p>Enter your name:</p>
        <input type="text" name="name" id="name">
        <input type="submit" name="submit" value="Submit">
      </form>
    </div>
  </body>
</html>

Ok. Restart node (<CTRL> C, then node app.js from the command prompt) and refresh the page. Enter your name and click Submit - but first I should warn you. Prepare to be amazed!



Alright. Now for the good stuff. Let's get sqlite cooking!

Adding SQLite to the mix with node-sqlite3

If you don't have it already, download and install SQLite, and Python. Now we need a wrapper - e.g., an interface between the database and Node.js. Luckily there's node-sqlite3.

Let's use npm to install node-sqlite3:

  1. Open the Node.js command prompt from Start > All Programs > Node.js > Node.js command prompt.
  2. Navigate to your project directory (probably something like c:\users\[username]\workspace\[project]).
  3. Type the following command:

npm install sqlite3

If you would like to run the sqlite3 unit tests provided (always a good idea), you'll need to install mocha as well. (IMPORTANT NOTE! You must first navigate to the Sqlite3 directory inside your project - it should be in c:\users\[username]\workspace\[project]\node_modules\sqlite3 - otherwise the tests won't execute...).

npm install mocha
npm test

If everything went well you should see output similar to the following:




Let's create a SQLite database and see if we can spit some data out to the browser.

Create a new database

Open a plain old Windows command prompt and navigate to the root of your project directory. We're going to create a new database called test.db, add a table called 'foo', and insert a few rows of dummy data for testing purposes. Like so:


C:\Users\bricej\workspace\nodetest>sqlite3 test.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo(id integer primary key asc, some_text text);
sqlite> insert into foo(some_text) values('howdy');
sqlite> insert into foo(some_text) values('there');
sqlite> insert into foo(some_text) values('folks!');
sqlite>

(I will spare us all the rant about always, ALWAYS having a surrogate key as a table's primary key, other than to say you should ALWAYS ALWAYS use a surrogate key. The fact that there is even a debate about this makes me sad. If you ever use a 'natural' key, you are signing up for some serious and painful data surgery at some point, buddy!)

Ok, now we're going to rinse and repeat the steps we used to create our fabulous test post page above.

First, create a route, called table_view.js: 

/**
 * Table view
 */
var sqlite3 = require('sqlite3').verbose();
var db;

exports.get = function(req, res){
 console.log("Serving " + __filename);
 
 var db_name = req.params.db_resource;
 var table = req.params.table;
 
 var dbpath = db_name;
 db = new sqlite3.Database(dbpath, function(err){
  if (err) res.render('index', {title: 'Error: ' + err}); 
 });
 
 db.all(" select * from " + table, function(err, rows) {
        
        var message = rows.length > 0 ? "Viewing " 
             + db_name + '/' + table 
             : "No data found in table '" + table + "' in " + db_name;
        
        res.render('table_view', {message: message, rows: rows});
        db.close();
    });
};

Next, create a jade view called table_view.jade: 

extends layout

block content
  h1= message
    
    h3 Data
    table
      - for (var r in rows)
          tr
            td= rows[r].id
            td= rows[r].some_text

  br

Finally, add a reference and a mapping to the app.js 
var table_view = require('./routes/table_view');

and 
app.get('/database/view/:db_resource/:table', table_view.get);

Now, if you restart the app and go to http://localhost:3000/database/view/test.db/foo you will see:



As in the previous example, the JavaScript file does most of the "heavy lifting" of querying the database and making the output available to the view. The jade view formats the output using a (hopefully) easy to understand templating syntax. The app (and Express framework) handle the routing of the request, so that we can create nice, RESTful URLs to our code.

You can see how easy it is to use these simple components to feed data to a browser - and can imagine extending the form example above to build more sophisticated data querying capabilities.

A few more additions and tweaks

Let's expand what we've got so far to meet the following requirements:

  • Add the ability to create a new database on the fly without having to use the command line
  • Add the ability to list existing databases
  • Add the ability to add tables to any database in the list
  • Add the ability to add columns to any table
While we're at it, let's refactor our code so that:
  • The database specific stuff is in its own subdirectory, so that the root doesn't look so cluttered.
  • Verbose console messages are only printed while in "debug" mode.
When we're done, our file tree should look like this:

Project Root
|   app.js
|   package.json
|   README.md
|   
|       
|---databases
|       test.db
|       
|---node_modules
[Ommitted for space]
|                   
|---public
|   |---stylesheets
|           style.css
|           
|---routes
|   |   echo.js
|   |   index.js
|   |   user.js
|   |   
|   |---database
|           db_create.js
|           db_edit.js
|           db_list.js
|           table_edit.js
|           table_view.js
|           
|---utils
|       date_time.js
|       environment.js
|       reflection.js
|       
|---views
    |   echo.jade
    |   index.jade
    |   layout.jade
    |   
    |---database
            db_create.jade
            db_edit.jade
            db_list.jade
            table_edit.jade
            table_view.jade
We'll also update the app.js so that pages can handle both get and post requests (where appropriate).

To save you the trouble, I've published a version of the project that does all these things: https://github.com/jasonbrice/nodeling

If you clone/fork/download it, you should be able to import it straight into Eclipse:



Try it out and let me know if it worked in the comments section...

To Do

To make this more like a "real" project, we should do a few things:
  • Sanitize and validate inputs!
  • Add more querying tools.
  • Use reflection so that object properties are not hard-coded.
  • Package utils code as user modules.
  • Write unit tests for each page.
  • Usability: add breadcrumbs, etc.

Other odds & ends

In getting ready to push this to Github, I did a quick inventory of files, sizes, etc. to make sure I wasn't pushing any unnecessary junk up. So I ran:
npm ls

And saw that at the end of the output, there was this: 

¦     +-- uglify-to-browserify@1.0.1
+-- sqlite3@2.1.15 extraneous

npm ERR! extraneous: sqlite3@2.1.15 C:\Users\bricej\workspace\nodeling\node_modules\sqlite3
npm ERR! not ok code 0

Huh? Sqlite is TOO a dependency! In fact, if I use npm prune to remove extraneous node_modules, then try and start the app, I get an error. So what gives?
It turns out that some piece in the install mechanism (npm? sqlite3's installer?) forgot to update the package.json. So in order to get node and npm to recognize it as such, I hand-updated the package.json to the following: 

{
  "name": "nodeling",
  "version": "0.0.1",
  "private": true,
  "scripts": {
    "start": "node app.js"
  },
  "dependencies": {
    "express": "3.3.7",
    "jade": "*", 
    "sqlite3" : "*"
  }
}

This says that the app is dependant on a version (any version) of sqlite3.

Also, the "right way" to bundle the utility files like date_time.js and reflection.js would be to make them local node modules and add them as dependencies.

Additional Resources

Source code for this demo is here: https://github.com/jasonbrice/nodeling

This site is invaluable in understanding the node-sqlite3 wrapper: https://github.com/developmentseed/node-sqlite3

Eclipse and Nodeclipse help: http://www.nodeclipse.org/community/q-and-a

Everything you need to know about Node Package Manager: http://howtonode.org/introduction-to-npm

A great tutorial focused on Node.js and Express: http://evanhahn.com/understanding-express-js/

Another tutorial, using Expresso, Jade, MongoDB: http://dailyjs.com/2010/11/22/node-tutorial-4/

Another tutorial, using more or less same as above: http://blog.ijasoneverett.com/2013/03/a-sample-app-with-node-js-express-and-mongodb-part-1/

These tutorials are good to check out since they did similar things to this one, but show a few slightly different approaches. It's always helpful (to me) to understand how other people approach similar problems.

Posted by at 6 comments:
Subscribe to: Posts (Atom)