dale lane

A few code snippets for how you can quickly stand up a SQL database, and provide a REST API for DB read/writes

I was helping out a team at a hackday hosted at Hursley last week. One of the things they wanted for their hack was a SQL database to put sensor data in, which they could access via a REST API. And they wanted it in node.js.

I’d never used Node before, so I used this as a chance to give myself a first crash-course.

I’m not saying this is the way to do this in Node, as it’s the result of my first hour’s tinkering. But it worked, and I mostly wanted to share how quick and easy it was.

• Step 1 – My first Node webserver
• Step 2 – My first Node databases
• Step 3 – My first Node REST API
• Step 4 – REST API for a database

Step 1 – My first Node webserver

Download node.js from nodejs.org and install.

Start with the simple webserver script from the Node.js home page.

Verify with a simple HTTP GET

server
$ node nodejs_simple_webserver.js
Server running at http://127.0.0.1:1337/

client
$ curl -X GET http://localhost:1337
Hello World

Step 2 – My first Node databases

I went with SQLite because the team wanted an SQL database, and I’ve used SQLite before.

Install it using npm

npm install sqlite3

And then install the nodejs bindings for sqlite

npm install https://github.com/mapbox/node-sqlite3/tarball/master

Start with the simple in-memory database script from the bindings documentation.

Verify by running the script, which creates the database, creates a table, inserts some rows, and then selects and prints them all out.

$ node nodejs_simple_inmemory_db.js
1: Ipsum 0
2: Ipsum 1
3: Ipsum 2
4: Ipsum 3
5: Ipsum 4
6: Ipsum 5
7: Ipsum 6
8: Ipsum 7
9: Ipsum 8
10: Ipsum 9

With a little tweak, we can do the same thing but with a database that is persisted to disk.

Each time you run it, it inserts a row, and then selects and prints all of the rows in the table.

So each time you run it, you get one more row than the time before. After a few times…

$ node nodejs_simple_file_db.js
This app was run at 1401967100001
This app was run at 1401967101397
This app was run at 1401967103406
This app was run at 1401967115812
This app was run at 1401967116905
This app was run at 1401967117834
This app was run at 1401967118381
This app was run at 1401967118676
This app was run at 1401967118970
This app was run at 1401967119212

Step 3 – My first Node REST API

I used Express. First step was to install it using npm.

npm install express

Starting from the example in the Express documentation it was easy to come up with this.

Verify with a few simple HTTP GETs

server
$ node nodejs_simple_express_api.js
Registering endpoint: /
Registering endpoint: /stubbed
Registering endpoint: /testing
Registering endpoint: /jsonendpoint

client
$ curl -X GET http://localhost:3000/
hello ROOT world

$ curl -X GET http://localhost:3000/stubbed
hello STUBBED

$ curl -X GET http://localhost:3000/testing
this is a test endpoint

$ curl -X GET http://localhost:3000/jsonendpoint
{"mykey":"myvalue","testy":"something","exnum":123}

Step 4 – REST API for a database

The final step was to bring it all together.

Create an SQLite database on disk, and use a REST API to access it.

An HTTP POST will increment a value in the database.
An HTTP GET will get the current value from the database.

Verify with some HTTP requests

server
$ node nodejs_db_with_restapi.js
Submit GET or POST to http://localhost:3000/data

client
$ curl -X GET http://localhost:3000/data
{"count":5}

$ curl -X POST http://localhost:3000/data

$ curl -X GET http://localhost:3000/data
{"count":6}

And that’s it.

This whole thing took me no time at all – probably about 40 minutes. Despite having never touched Node.js before, I’d managed to pull together a basis for the hack from a few samples. For the last few years, most of the code I’ve written each day has been in Java, so this made a fun change.

Tags: nodejs, sqlite

This entry was posted on Sunday, June 15th, 2014 at 9:19 pm and is filed under code. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.