{"id":3152,"date":"2014-06-15T21:19:58","date_gmt":"2014-06-15T21:19:58","guid":{"rendered":"http:\/\/dalelane.co.uk\/blog\/?p=3152"},"modified":"2014-06-17T14:19:48","modified_gmt":"2014-06-17T14:19:48","slug":"using-node-js-to-create-a-rest-api-around-a-sql-database","status":"publish","type":"post","link":"https:\/\/dalelane.co.uk\/blog\/?p=3152","title":{"rendered":"Using Node.js to create a REST API around a SQL database"},"content":{"rendered":"<p><strong>A few code snippets for how you can quickly stand up a SQL database, and provide a REST API for DB read\/writes<\/strong><\/p>\n<p>I was helping out a team at a hackday hosted at <a href=\"http:\/\/www.ibm.com\/connections\/blogs\/et\/entry\/facilities\">Hursley<\/a> 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. <\/p>\n<p>I&#8217;d never used Node before, so I used this as a chance to give myself a first crash-course. <\/p>\n<p><em>I&#8217;m not saying this is <strong>the<\/strong> way to do this in Node, as it&#8217;s the result of my first hour&#8217;s tinkering. But it worked, and I mostly wanted to share how quick and easy it was. <\/em><\/p>\n<ul>\n<li><a href=\"http:\/\/dalelane.co.uk\/blog\/?p=3152#step1\">Step 1 &#8211; My first Node webserver<\/a>\n<\/li>\n<li><a href=\"http:\/\/dalelane.co.uk\/blog\/?p=3152#step2\">Step 2 &#8211; My first Node databases<\/a>\n<\/li>\n<li><a href=\"http:\/\/dalelane.co.uk\/blog\/?p=3152#step3\">Step 3 &#8211; My first Node REST API<\/a>\n<\/li>\n<li><a href=\"http:\/\/dalelane.co.uk\/blog\/?p=3152#step4\">Step 4 &#8211; REST API for a database<\/a>\n<\/li>\n<\/ul>\n<p><!--more--><a name=\"step1\"><\/a><\/p>\n<h3>Step 1 &#8211; My first Node webserver<\/h3>\n<p>Download node.js from <a href=\"http:\/\/nodejs.org\/\">nodejs.org<\/a> and install. <\/p>\n<p>Start with the simple webserver script from the Node.js home page.<\/p>\n<p><script src=\"https:\/\/gist.github.com\/dalelane\/2563e239001eda5f94cc.js\"><\/script><br \/>\n<noscript><small>Source: <a href=\"https:\/\/gist.github.com\/dalelane\/2563e239001eda5f94cc\">nodejs_simple_webserver.js<\/a><\/small><\/noscript><\/p>\n<p>Verify with a simple HTTP GET<\/p>\n<p><em>server<\/em><br \/>\n<code>$ node nodejs_simple_webserver.js<br \/>\nServer running at http:\/\/127.0.0.1:1337\/<\/code><\/p>\n<p><em>client<\/em><br \/>\n<code>$ curl -X GET http:\/\/localhost:1337<br \/>\nHello World<\/code><\/p>\n<p><a name=\"step2\"><\/a><\/p>\n<h3>Step 2 &#8211; My first Node databases<\/h3>\n<p>I went with SQLite because the team wanted an SQL database, and I&#8217;ve <a href=\"http:\/\/dalelane.co.uk\/blog\/?s=sqlite\">used SQLite before<\/a>.<\/p>\n<p>Install it using npm<\/p>\n<p><code>npm install sqlite3<\/code><\/p>\n<p>And then install the nodejs bindings for sqlite<\/p>\n<pre style=\"overflow-x: scroll\"><code>npm install https:\/\/github.com\/mapbox\/node-sqlite3\/tarball\/master<\/code><\/pre>\n<p>Start with the simple in-memory database script from the <a href=\"https:\/\/github.com\/mapbox\/node-sqlite3\">bindings documentation<\/a>. <\/p>\n<p><script src=\"https:\/\/gist.github.com\/dalelane\/77c08b3bc698499fde80.js\"><\/script><br \/>\n<noscript><small>Source: <a href=\"https:\/\/gist.github.com\/dalelane\/77c08b3bc698499fde80\">nodejs_simple_inmemory_db.js<\/a><\/small><\/noscript><\/p>\n<p>Verify by running the script, which creates the database, creates a table, inserts some rows, and then selects and prints them all out.<\/p>\n<p><code>$ node nodejs_simple_inmemory_db.js<br \/>\n1: Ipsum 0<br \/>\n2: Ipsum 1<br \/>\n3: Ipsum 2<br \/>\n4: Ipsum 3<br \/>\n5: Ipsum 4<br \/>\n6: Ipsum 5<br \/>\n7: Ipsum 6<br \/>\n8: Ipsum 7<br \/>\n9: Ipsum 8<br \/>\n10: Ipsum 9<\/code><\/p>\n<p>With a little tweak, we can do the same thing but with a database that is persisted to disk.<\/p>\n<p><script src=\"https:\/\/gist.github.com\/dalelane\/d0259abd7a39cfb15cbe.js\"><\/script><br \/>\n<noscript><small>Source: <a href=\"https:\/\/gist.github.com\/dalelane\/d0259abd7a39cfb15cbe\">nodejs_simple_file_db.js<\/a><\/small><\/noscript><\/p>\n<p>Each time you run it, it inserts a row, and then selects and prints all of the rows in the table. <\/p>\n<p>So each time you run it, you get one more row than the time before. After a few times&#8230;<\/p>\n<p><code>$ node nodejs_simple_file_db.js<br \/>\nThis app was run at 1401967100001<br \/>\nThis app was run at 1401967101397<br \/>\nThis app was run at 1401967103406<br \/>\nThis app was run at 1401967115812<br \/>\nThis app was run at 1401967116905<br \/>\nThis app was run at 1401967117834<br \/>\nThis app was run at 1401967118381<br \/>\nThis app was run at 1401967118676<br \/>\nThis app was run at 1401967118970<br \/>\nThis app was run at 1401967119212<\/code><\/p>\n<p><a name=\"step3\"><\/a><\/p>\n<h3>Step 3 &#8211; My first Node REST API<\/h3>\n<p>I used <a href=\"http:\/\/expressjs.com\/\">Express<\/a>. First step was to install it using npm.<\/p>\n<p><code>npm install express<\/code><\/p>\n<p>Starting from the <a href=\"http:\/\/expressjs.com\/4x\/api.html\">example in the Express documentation<\/a> it was easy to come up with this.<\/p>\n<p><script src=\"https:\/\/gist.github.com\/dalelane\/494433bb2d1fa9d3d4c3.js\"><\/script><br \/>\n<noscript><small>Source: <a href=\"https:\/\/gist.github.com\/dalelane\/494433bb2d1fa9d3d4c3\">nodejs_simple_express_api.js<\/a><\/small><\/noscript><\/p>\n<p>Verify with a few simple HTTP GETs<\/p>\n<p><em>server<\/em><br \/>\n<code>$ node nodejs_simple_express_api.js<br \/>\nRegistering endpoint: \/<br \/>\nRegistering endpoint: \/stubbed<br \/>\nRegistering endpoint: \/testing<br \/>\nRegistering endpoint: \/jsonendpoint<\/code><\/p>\n<p><em>client<\/em><br \/>\n<code>$ curl -X GET http:\/\/localhost:3000\/<br \/>\nhello ROOT world<\/p>\n<p>$ curl -X GET http:\/\/localhost:3000\/stubbed<br \/>\nhello STUBBED<\/p>\n<p>$ curl -X GET http:\/\/localhost:3000\/testing<br \/>\nthis is a test endpoint<\/p>\n<p>$ curl -X GET http:\/\/localhost:3000\/jsonendpoint<br \/>\n{\"mykey\":\"myvalue\",\"testy\":\"something\",\"exnum\":123}<\/code><\/p>\n<p><a name=\"step4\"><\/a><\/p>\n<h3>Step 4 &#8211; REST API for a database<\/h3>\n<p>The final step was to bring it all together. <\/p>\n<p>Create an SQLite database on disk, and use a REST API to access it. <\/p>\n<p>An HTTP POST will increment a value in the database.<br \/>\nAn HTTP GET will get the current value from the database. <\/p>\n<p><script src=\"https:\/\/gist.github.com\/dalelane\/6ce08b52d5cca8f92926.js\"><\/script><br \/>\n<noscript><small>Source: <a href=\"https:\/\/gist.github.com\/dalelane\/6ce08b52d5cca8f92926\">nodejs_db_with_restapi.js<\/a><\/small><\/noscript> <\/p>\n<p>Verify with some HTTP requests<\/p>\n<p><em>server<\/em><br \/>\n<code>$ node nodejs_db_with_restapi.js<br \/>\nSubmit GET or POST to http:\/\/localhost:3000\/data<\/code><\/p>\n<p><em>client<\/em><br \/>\n<code>$ curl -X GET http:\/\/localhost:3000\/data<br \/>\n{\"count\":5}<\/p>\n<p>$ curl -X POST http:\/\/localhost:3000\/data<\/p>\n<p>$ curl -X GET http:\/\/localhost:3000\/data<br \/>\n{\"count\":6}<\/code><\/p>\n<p>And that&#8217;s it. <\/p>\n<p>This whole thing took me no time at all &#8211; probably about 40 minutes. Despite having never touched Node.js before, I&#8217;d managed to pull together a basis for the hack from a few samples. For the last few years, most of the code I&#8217;ve written each day has been in Java, so this made a fun change.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[563,564],"class_list":["post-3152","post","type-post","status-publish","format-standard","hentry","category-code","tag-nodejs","tag-sqlite"],"_links":{"self":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3152"}],"version-history":[{"count":0,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3152\/revisions"}],"wp:attachment":[{"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dalelane.co.uk\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}