9.22.2015

Styling & Customizing File Inputs the Smart Way

There are quite a few techniques for “customizing” the <input type="file" /> element. I tried most of them, but none was good enough to have on Readerrr (for importing feeds by uploading a file). Probably the worst technique was the one where the input element is put into a container (which imitates a button), and the input follows the cursor so that when you click anywhere on the container, you actually click the input. Sounds interesting and weird at the same time, right? Anyway, it had some unacceptable drawbacks (usability, touch).

As as result, I tried googling for an unseen solution. Once it seemed that there was nothing new, my I eyes were caught by a comment on StackOverflow. It had just a few up votes and was lost somewhere in the middle of the page, but most importantly it contained a magic word – <label>! As you may know, pressing a label basically triggers the focus event for the bound input. Interesting thing is that, if it is a file input, it works out as a click event, resulting in opening a file browser. This is great for crafting a semantic solution.

<input type="file" name="file" id="file" class="inputfile" />
<label for="file">Choose a file</label>

So, pressing any of these two elements gives us the same result. That means that the most difficult part is… solved! No JavaScript, no other complex solutions like cursor position tracking, just these two lines. See for yourself:

Now let’s just style it and make this look like a normal button.

Hiding the <input>

First off, we need to hide the ugly duckling. CSS properties such as display: none or visibility: hidden will not work out. The reasons are: the input value will not be sent to the server on form submit; the input will be excluded out of tab order (you want your website to be accessible, right?). I set up a combination of CSS properties/values for hiding the input visually but keeping it visible for the browser:

.inputfile {
 width: 0.1px;
 height: 0.1px;
 opacity: 0;
 overflow: hidden;
 position: absolute;
 z-index: -1;
}

I see you are wondering why width and height are set to 0.1px instead of just 0px. Setting the property values to zero ends up throwing the element out of tab party in some browsers. And position: absolute guarantees the element does not interfere with the sibling elements.

Styling the <label>

Since the <label> element is visually the button, you can use all of your creative CSS juices on it. I’m sticking to something very simple for now:

.inputfile + label {
    font-size: 1.25em;
    font-weight: 700;
    color: white;
    background-color: black;
    display: inline-block;
}

.inputfile:focus + label,
.inputfile + label:hover {
    background-color: red;
}

Accessibility

How do you know that an element on the website is pressable? Firstly, the element should communicate a feeling that you can tap or click on it. Secondly, the cursor icon should change to an appropriate one when hovering the element. The former we’ve solved previously, let’s solve the latter, because labels do not trigger a cursor change by default:

.inputfile + label {
 cursor: pointer; /* "hand" cursor */
}

Keyboard Navigation

If users are unable to navigate on your website using just a keyboard, you are doing something wrong. Hiding the input itself in a correct manner was one thing, the other is indicating when the element is focused, i.e. rendering .inputfile:focus on the label:

.inputfile:focus + label {
 outline: 1px dotted #000;
 outline: -webkit-focus-ring-color auto 5px;
}

-webkit-focus-ring-color auto 5px is a little trick for obtaining default outline looks on Chrome, Opera and Safari. The style in the line above is for browsers that do not understand the -webkit… expression.

Possible Touch Issues

In case you’ve been using FastClick (a library for eliminating the 300ms tap-pause on touch-capable devices) and have plans to add some extra markup to the content of a label, the button won’t work as it should, unless you use pointer-events: none, respectively:

<label for="file"><strong>Choose a file</strong></label>
.inputfile + label * {
 pointer-events: none;
}

JavaScript Enhancement

Probably and hopefully the last thing missing is indicating if files were selected. The file input does usually indicate that, but in our case the input is visually hidden. Luckily, there is a way out: a tiny JavaScript enhancement. The text of a label becomes the name of the selected file. If there were multiple files selected, the text will tell us how many of them were selected.

<input type="file" name="file" id="file" class="inputfile" data-multiple-caption="{count} files selected" multiple />
var inputs = document.querySelectorAll( '.inputfile' );
Array.prototype.forEach.call( inputs, function( input )
{
 var label  = input.nextElementSibling,
  labelVal = label.innerHTML;

 input.addEventListener( 'change', function( e )
 {
  var fileName = '';
  if( this.files && this.files.length > 1 )
   fileName = ( this.getAttribute( 'data-multiple-caption' ) || '' ).replace( '{count}', this.files.length );
  else
   fileName = e.target.value.split( '\\' ).pop();

  if( fileName )
   label.querySelector( 'span' ).innerHTML = fileName;
  else
   label.innerHTML = labelVal;
 });
});

There is also a jQuery version of this code presented in the source of the demo files. Make sure to check them out.

A little explanation:

  • Having the native [multiple] attribute allows users to select more than one file per upload. Whereas [data-multiple-caption] is a fictive attribute for expressing the message if multiple files were selected. Here you can set a custom message. The use of the {count} phrase is optional and the fragment is replaced with the number of files selected. The reason I use an additional HTML attribute instead of assigning this sentence as a value for a JavaScript variable is because it’s much easier to maintain the copy when it is in one place.
  • HTML attribute [multiple] is not supported in IE 9 and below and neither is the files property of JavaScript. For the latter case, we simply rely on value. Since it usually has a value of C:\fakepath\filename.jpg format, the split( '\\' ).pop() extracts what’s actual – the name of the file.
  • An interesting thing is that you can unset a value of the input by pressing the ESC button while in the file browser. This is possible only in Chrome and Opera. Therefore, we use labelVal for storing the default value of the label and bringing it back when necessary.

This is how the final result looks like:

What if JavaScript is not available?

Since there is no JavaScript-less way to indicate if any files were selected, it would be better to rely on the default looks of the file input for the sake of usability. All we need to do is to add a .no-js class name to the <html> element and then use JavaScript and replace it with .js – that’s how we will know if JavaScript is available.

<html class="no-js">
    <head>
        <!-- remove this if you use Modernizr -->
        <script>(function(e,t,n){var r=e.querySelectorAll("html")[0];r.className=r.className.replace(/(^|\s)no-js(\s|$)/,"$1js$2")})(document,window,0);</script>
    </head>
</html>

The CSS part accordingly:

.js .inputfile {
    width: 0.1px;
    height: 0.1px;
    opacity: 0;
    overflow: hidden;
    position: absolute;
    z-index: -1;
}

.no-js .inputfile + label {
    display: none;
}

Firefox Bug

It is quite unexpected that Firefox completely ignores the input[type="file"]:focus expression, whereas :hover and :active work just fine! Surprisingly, Firefox allows to catch the focus event in JavaScript, so the workaround is adding a class to the file input element that let’s us control the focus style:

input.addEventListener( 'focus', function(){ input.classList.add( 'has-focus' ); });
input.addEventListener( 'blur', function(){ input.classList.remove( 'has-focus' ); });
.inputfile:focus + label,
.inputfile.has-focus + label {
    outline: 1px dotted #000;
    outline: -webkit-focus-ring-color auto 5px;
}

Check out the example styles in the demo to see how to style the file input element according to your needs. Make sure to take a look at the source code of the demo and feel free to use this technique in your projects. Happy uploading!

3 Ways to Master AngularJS Directives

For AngularJs, the most powerful component continues to be directives. Let’s understand what directives mean here. It is nothing but an extended version of the HTML attributes with the prefix ng added to it. With the ng-app, you can initialize the AngularJS application. Similarly, using the ng-init directive you can initialize the application data. In this article, we will talk mostly about the custom directives, and how you can master these directives thus making your AngularJS based application worthy for users. Remember, your aim is to offer a good application that can empower the users.

Simple Directives

If you want to create a widget that would arrange the different profile details that you are planning to display on your application, then you should ideally use simple directives. It offers a creative view of the elements.

Here is an example of a widget created to view the book details on an e-commerce application

angular.module('masteringAngularJsDirectives', [])
.directive('book', function() {
 return {
 restrict: 'E',
 scope: {
 data: '='
 },
 templateUrl: 'templates/book-widget.html'
 }
})

With this, you have created a directive first, which has been named book for this example. This directive returns an object. Restrict will define the directive type- attribute, class, element or comment. Scope gives you an idea of the directive’s scope. With templateURL, you can view content for render specific content. This will remove the complications associated with HTML

Isolated Scope

When you talk of directives, you will see an underlying scope alongside each directive. Data binding is an important element of directive declaration. For this example, we will consider implementing the basket portion of the e-commerce application.

Here’s how you can declare the directive in your application

app.directive("item", function() {
 return {
 restrict: 'E',
 link: function(scope, element, attrs) {
 scope.name = attrs.name;
 },
 template: '<div><strong>Name:</strong> {{name}} <strong>Select Amount:</strong> <select name="count" ng-model="count"><option value="1">1</option><option value="2">2</option></select> <strong>Selected Amount:</strong> {{count}}</div>'
 }
})

Let’s say you want to display three items using HTML, here’s how you would do that

<item name="Item-1"></item>
<item name="Item-2"></item>
<item name="Item-3"></item>

The basic issue here is that when you wish to update a particular item, all the items in the amounts section get updated. The reason being there is a two way data binding being done here, and the scope is not isolated here. Here’s how you can isolate the scope

 

app.directive("item", function() {
 return {
 restrict: 'E',
 scope: {},
 link: function(scope, element, attrs) {
 scope.name = attrs.name;
 },
 template: '<div><strong>Name:</strong> {{name}} <strong>Select Amount:</strong> <select name="count" ng-model="count"><option value="1">1</option><option value="2">2</option></select> <strong>Selected Amount:</strong> {{count}}</div>'
 }
})

Directive Inheritance

What if you have a feature that will be used by several directives? You just need to introduce it to a parent directive, and allow it to be used by all the child directives. Let’s say you want to initiate a mouse click event for a book directive, and it is going to be used by another directive as well. Here’s how you can get it done

app.directive('mouseClicked', function() {
 return {
 restrict: 'E',
 scope: {},
 controller: "MouseClickedCtrl as mouseClicked"
 }
})

This is your parent directive which will eventually be used by the child directives too. You will need to define a controller directive too

app.controller('MouseClickedCtrl', function($element) {
 var mouseClicked = this;
 mouseClicked.bookType = null;
 mouseClicked.setBookType = function(type) {
 mouseClicked.bookType = type
 };
 
 $element.bind("click", function() {
 alert("Typeof book: " + mouseClicked.bookType + " sent for statistical analysis!");
 })
})

Set the controller instance for the variable booktype while using child directives.

app.directive('ebook', function() {
 return {
 require: "mouseClicked",
 link: function(scope, element, attrs, mouseClickedCtrl) {
 mouseClickedCtrl.setBookType("EBOOK");
 }
 }
})
.directive('magazine', function() {
 return {
 require: "mouseClicked",
 link: function(scope, element, attrs, mouseClickedCtrl) {
 mouseClickedCtrl.setBookType("MAGAZINE");
 }
 }
})

A require keyword is generally used by the child directive

<a><mouse-clicked ebook>Game of thrones (click me)</mouse-clicked></a><br/>
<a><mouse-clicked magazine>PC World (click me)</mouse-clicked></a>

You need to understand the child directives are assets of the parent directive, and will act on their command.

When you have gained expertise on managing the AngularJS directives, then you can manage the pages of the application in a more determined manner, without working too much on the code. The user experience will be enhanced with the use of directives, and the debugging would be less complicated.

Semaphore Software offers expertise in AngularJS application development. Get in touch with us via info@silvertouch.com, to discuss your requirements further.

Simplify Android Development Using manifoldJS With Crosswalk

With version 0.3.0 of manifoldJS, you can now choose to build your Android apps with Crosswalk instead of the traditional Android webview. It's quite simple to implement as well.

If you're not familiar, manifoldJS is a new open-source framework that can take a website and create an app for Windows, iOS, Android, Chrome, and Firefox, simplifying the creation of hosted apps across platforms. It debuted at the Microsoft Build 2015 conference in April. manifoldJS runs as a command line tool through Node.js, or you can use the web-based tool.

manifoldJS

In this tutorial, I'll show you the simple steps to get it up and running so that you can try it yourself. First, make sure you have manifoldJS installed and running.

Install Node.js from nodejs.org.

Open your favorite command prompt (Terminal on Mac or Linux) and type:

Now, you simply add the Crosswalk flag to your launch parameters, and watch what happens next:

And…BOOM! You’ve just built a hosted web app with Crosswalk.

Shiftr app on Android phones

With Crosswalk, you can be confident that the newest HTML5 features, such as WebGL, IndexedDB, Web Sockets, and CSS3, are there for your app. For example, here's the output from a WebGL application that uses the default webview and was generated by manifoldJS with the following command:

We are sorry but your browser does not seem to support WebGL

And here is the same application with Crosswalk enabled. It was generated after adding the -c (or –-crosswalk) flag to the previous command:

Or:

Your browser supports WebGL

With Crosswalk, you can be sure that all your users get the intended experience.

Crosswalk is a web runtime environment engineered by the Crosswalk Project. Crosswalk has taken the open-source Chromium and Blink engines, and compiled them into a modern, up-to-date runtime environment. You can think of Crosswalk as a powerful webview. In fact, when the Crosswalk flag is set, we use it in place of the traditional Android webview.

Crosswalk support brings two main advantages. First, it’s an "updated" web runtime environment. That might not be much of an advantage for Android users on a recent version of the Android OS, but for users on older versions of the OS, it's an immense improvement. The Crosswalk webview will give you access to all the latest HTML5 features and performance gains over the traditional webview.

Secondly, Crosswalk provides a consistent runtime environment. With all the different versions of Android in use today, you have that many different versions of the Android webview, so you’re forced to write to the lowest common denominator. Using Crosswalk removes that hindrance. Additionally, the runtime only changes when you update it in your app, not with the OS. We know that many enterprise users rely on this type of consistency for their applications.

I can only think of one reason why you wouldn’t want to use Crosswalk: application size. The average .apk file (an application file for Android) that we produce is just a few megabytes. Adding Crosswalk to the app adds an additional 20MB, close to 60MB once installed on the device. You need to decide whether the resource cost is worth it.

Bundling the runtime with the application is the simplest approach for distribution purposes, but Crosswalk applications can also share a single runtime library (in "shared mode") to lighten the load. A package which enables shared mode is part of the Crosswalk for Android distribution. However, you would have to distribute this shared runtime package yourself. Visit the Crosswalk wiki for more details.

Keep in mind that the nature of a hosted web app is that you make your app updates on your webserver. So in most cases, the cost of the added package size will be felt with the initial download, not with every update like a regular native app.

Advertisement

We’re excited to be supporting the Crosswalk web runtime environment. It’s filling a gap in the Android system that makes development simpler and more reliable. Give it a try with your next manifoldJS app and see what you think. For more information on Crosswalk, visit the Crosswalk Project website. To start building store apps from your website, go to the manifoldJS website and get started.

This article is part of the web development series from Microsoft tech evangelists on practical JavaScript learning, open-source projects, and interoperability best practices, including Microsoft Edge browser and the new EdgeHTML rendering engine

We encourage you to test across browsers and devices including Microsoft Edge—the default browser for Windows 10—with free tools on dev.modern.IE:

In-depth tech learning on Microsoft Edge and the Web Platform from our engineers and evangelists:

More free cross-platform tools and resources for the Web Platform:

SQL vs NoSQL: The Differences

SQL (Structured Query Language) databases have been a primary data storage mechanism for more than four decades. Usage exploded in the late 1990s with the rise of web applications and open-source options such as MySQL, PostgreSQL and SQLite.

NoSQL databases have existed since the 1960s, but have been recently gaining traction with popular options such as MongoDB, CouchDB, Redis and Apache Cassandra.

You’ll find many tutorials explaining how to use a particular flavor of SQL or NoSQL, but few discuss why you should choose one in preference to the other. I hope to fill that gap. In this article, we’ll cover the fundamental differences. In a later follow-up article, we’ll look at typical scenarios and determine the optimal choice.

Most examples apply to the popular MySQL SQL and MongoDB NoSQL database systems. Other SQL/NoSQL databases are similar, but there will be minor differences in features and syntax.

The SQL vs NoSQL Holy War

Before we go further, let’s dispel a number of myths …

MYTH: NoSQL supersedes SQL
That would be like saying boats were superseded by cars because they’re a newer technology. SQL and NoSQL do the same thing: store data. They take different approaches, which may help or hinder your project. Despite feeling newer and grabbing recent headlines, NoSQL is not a replacement for SQL — it’s an alternative.

MYTH: NoSQL is better / worse than SQL
Some projects are better suited to using an SQL database. Some are better suited to NoSQL. Some could use either interchangeably. This article could never be a SitePoint Smackdown, because you cannot apply the same blanket assumptions everywhere.

MYTH: SQL vs NoSQL is a clear distinction
This is not necessarily true. Some SQL databases are adopting NoSQL features and vice versa. The choices are likely to become increasingly blurred, and NewSQL hybrid databases could provide some interesting options in the future.

MYTH: the language/framework determines the database
We’ve grown accustom to technology stacks, such as —

  • LAMP: Linux, Apache, MySQL (SQL), PHP
  • MEAN: MongoDB (NoSQL), Express, Angular, Node.js
  • .NET, IIS and SQL Server
  • Java, Apache and Oracle.

There are practical, historical and commercial reasons why these stacks evolved — but don’t presume they are rules. You can use a MongoDB NoSQL database in your PHP or .NET project. You can connect to MySQL or SQL Server in Node.js. You may not find as many tutorials and resources, but your requirements should determine the database type — not the language.

(That said, don’t make life purposely difficult for yourself! Choosing an unusual technology combination or a mix of SQL and NoSQL is possible, but you’ll find it tougher to find support and employ experienced developers.)

With that in mind, let’s look at the primary differences …

SQL Tables vs NoSQL Documents

SQL databases provide a store of related data tables. For example, if you run an online book store, book information can be added to a table named book:

ISBN title author format price
9780992461225 JavaScript: Novice to Ninja Darren Jones ebook 29.00
9780994182654 Jump Start Git Shaumik Daityari ebook 29.00

Every row is a different book record. The design is rigid; you cannot use the same table to store different information or insert a string where a number is expected.

NoSQL databases store JSON-like field-value pair documents, e.g.

{
 ISBN: 9780992461225,
 title: "JavaScript: Novice to Ninja",
 author: "Darren Jones",
 format: "ebook",
 price: 29.00
}

Similar documents can be stored in a collection, which is analogous to an SQL table. However, you can store any data you like in any document; the NoSQL database won’t complain. For example:

{
 ISBN: 9780992461225,
 title: "JavaScript: Novice to Ninja",
 author: "Darren Jones",
 year: 2014,
 format: "ebook",
 price: 29.00,
 description: "Learn JavaScript from scratch!",
 rating: "5/5",
 review: [
  { name: "A Reader", text: "The best JavaScript book I've ever read." },
  { name: "JS Expert", text: "Recommended to novice and expert developers alike." }
 ]
}

SQL tables create a strict data template, so it’s difficult to make mistakes. NoSQL is more flexible and forgiving, but being able to store any data anywhere can lead to consistency issues.

SQL Schema vs NoSQL Schemaless

In an SQL database, it’s impossible to add data until you define tables and field types in what’s referred to as a schema. The schema optionally contains other information, such as —

  • primary keys — unique identifiers such as the ISBN which apply to a single record
  • indexes — commonly queried fields indexed to aid quick searching
  • relationships — logical links between data fields
  • functionality such as triggers and stored procedures.

Your data schema must be designed and implemented before any business logic can be developed to manipulate data. It’s possible to make updates later, but large changes can be complicated.

In a NoSQL database, data can be added anywhere, at any time. There’s no need to specify a document design or even a collection up-front. For example, in MongoDB the following statement will create a new document in a new book collection if it’s not been previously created:

db.book.insert(
 ISBN: 9780994182654,
 title: "Jump Start Git",
 author: "Shaumik Daityari",
 format: "ebook",
 price: 29.00
);

(MongoDB will automatically add a unique _id value to each document in a collection. You may still want to define indexes, but that can be done later if necessary.)

A NoSQL database may be more suited to projects where the initial data requirements are difficult to ascertain. That said, don’t mistake difficulty for laziness: neglecting to design a good data store at project commencement will lead to problems later.

SQL Normalization vs NoSQL Denormalization

Presume we want to add publisher information to our book store database. A single publisher could offer more than one title so, in an SQL database, we create a new publisher table:

id name country email
SP001 SitePoint Australia feedback@sitepoint.com

We can then add a publisher_id field to our book table, which references records by publisher.id:

ISBN title author format price publisher_id
9780992461225 JavaScript: Novice to Ninja Darren Jones ebook 29.00 SP001
9780994182654 Jump Start Git Shaumik Daityari ebook 29.00 SP001

This minimizes data redundancy; we’re not repeating the publisher information for every book — only the reference to it. This technique is known as normalization, and has practical benefits. We can update a single publisher without changing book data.

We can use normalization techniques in NoSQL. Documents in the book collection —

{
 ISBN: 9780992461225,
 title: "JavaScript: Novice to Ninja",
 author: "Darren Jones",
 format: "ebook",
 price: 29.00,
 publisher_id: "SP001"
}

— reference a document in a publisher collection:

{
 id: "SP001"
 name: "SitePoint",
 country: "Australia",
 email: "feedback@sitepoint.com"
}

However, this is not always practical, for reasons that will become evident below. We may opt to denormalize our document and repeat publisher information for every book:

{
 ISBN: 9780992461225,
 title: "JavaScript: Novice to Ninja",
 author: "Darren Jones",
 format: "ebook",
 price: 29.00,
 publisher: {
  name: "SitePoint",
  country: "Australia",
  email: "feedback@sitepoint.com"
 }
}

This leads to faster queries, but updating the publisher information in multiple records will be significantly slower.

SQL Relational JOIN vs NoSQL

SQL queries offer a powerful JOIN clause. We can obtain related data in multiple tables using a single SQL statement. For example:

SELECT book.title, book.author, publisher.name
FROM book
LEFT JOIN book.publisher_id ON publisher.id;

This returns all book titles, authors and associated publisher names (presuming one has been set).

NoSQL has no equivalent of JOIN, and this can shock those with SQL experience. If we used normalized collections as described above, we would need to fetch all book documents, retrieve all associated publisher documents, and manually link the two in our program logic. This is one reason denormalization is often essential.

SQL vs NoSQL Data Integrity

Most SQL databases allow you to enforce data integrity rules using foreign key constraints (unless you’re still using the older, defunct MyISAM storage engine in MySQL). Our book store could —

  • ensure all books have a valid publisher_id code that matches one entry in the publisher table, and
  • not permit publishers to be removed if one or more books are assigned to them.

The schema enforces these rules for the database to follow. It’s impossible for developers or users to add, edit or remove records, which could result in invalid data or orphan records.

The same data integrity options are not available in NoSQL databases; you can store what you want regardless of any other documents. Ideally, a single document will be the sole source of all information about an item.

SQL vs NoSQL Transactions

In SQL databases, two or more updates can be executed in a transaction — an all-or-nothing wrapper that guarantees success or failure. For example, presume our book store contained order and stock tables. When a book is ordered, we add a record to the order table and decrement the stock count in the stock table. If we execute those two updates individually, one could succeed and the other fail — thus leaving our figures out of sync. Placing the same updates within a transaction ensures either both succeed or both fail.

In a NoSQL database, modification of a single document is atomic. In other words, if you’re updating three values within a document, either all three are updated successfully or it remains unchanged. However, there’s no transaction equivalent for updates to multiple documents. There are transaction-like options, but, at the time of writing, these must be manually processed in your code.

SQL vs NoSQL CRUD Syntax

Creating, reading updating and deleting data is the basis of all database systems. In essence —

  • SQL is a lightweight declarative language. It’s deceptively powerful, and has become an international standard, although most systems implement subtly different syntaxes.
  • NoSQL databases use JavaScripty-looking queries with JSON-like arguments! Basic operations are simple, but nested JSON can become increasingly convoluted for more complex queries.

A quick comparison:

SQL NoSQL
insert a new book record
INSERT INTO book (
  `ISBN`, `title`, `author`
)
VALUES (
  '9780992461256', 
  'Full Stack JavaScript', 
  'Colin Ihrig & Adam Bretz'
);
db.book.insert({
  ISBN: "9780992461256",
  title: "Full Stack JavaScript",
  author: "Colin Ihrig & Adam Bretz"
});
update a book record
UPDATE book
SET price = 19.99
WHERE ISBN = '9780992461256'
db.book.update(
  { ISBN: '9780992461256' },
  { $set: { price: 19.99 } }
);
return all book titles over $10
SELECT title FROM book
WHERE price > 10;
db.book.find(
  { price: { &gt;: 10 } },
  { _id: 0, title: 1 }
);

The second JSON object is known as a projection: it sets which fields are returned (_id is returned by default so it needs to be unset).

count the number of SitePoint books
SELECT COUNT(1) FROM book
WHERE publisher_id = 'SP001';
db.book.count({
  "publisher.name": "SitePoint"
});

This presumes denormalized documents are used.

return the number of book format types
SELECT format, COUNT(1) AS `total`
FROM book
GROUP BY format;
db.book.aggregate([
  { $group:
    { 
      _id: "$format", 
      total: { $sum: 1 } 
    }
  }
]);

This is known as aggregation: a new set of documents is computed from an original set.

delete all SitePoint books
DELETE FROM book
WHERE publisher_id = 'SP001';

Alternatively, it’s possible to delete the publisher record and have this cascade to associated book records if foreign keys are specified appropriately.

db.book.remove({
  "publisher.name": "SitePoint"
});

SQL vs NoSQL Performance

Perhaps the most controversial comparison, NoSQL is regularly quoted as being faster than SQL. This isn’t surprising; NoSQL’s simpler denormalized store allows you to retrieve all information about a specific item in a single request. There’s no need for related JOINs or complex SQL queries.

That said, your project design and data requirements will have most impact. A well-designed SQL database will almost certainly perform better than a badly designed NoSQL equivalent and vice versa.

SQL vs NoSQL Scaling

As your data grows, you may find it necessary to distribute the load among multiple servers. This can be tricky for SQL-based systems. How do you allocate related data? Clustering is possibly the simplest option; multiple servers access the same central store — but even this has challenges.

NoSQL’s simpler data models can make the process easier, and many have been built with scaling functionality from the start. That is a generalization, so seek expert advice if you encounter this situation.

SQL vs NoSQL Practicalities

Finally, let’s consider security and system problems. The most popular NoSQL databases have been around a few years; they are more likely to exhibit issues than more mature SQL products. Many problems have been reported, but most boil down to a single issue: knowledge.

Developers and sysadmins have less experience with newer database systems, so mistakes are made. Opting for NoSQL because it feels fresher, or because you want to avoid schema design inevitably, leads to problems later.

SQL vs NoSQL Summary

SQL and NoSQL databases do the same thing in different ways. It’s possible choose one option and switch to another later, but a little planning can save time and money.

Projects where SQL is ideal:

  • logical related discrete data requirements which can be identified up-front
  • data integrity is essential
  • standards-based proven technology with good developer experience and support.

Projects where NoSQL is ideal:

  • unrelated, indeterminate or evolving data requirements
  • simpler or looser project objectives, able to start coding immediately
  • speed and scalability is imperative.

In the case of our book store, an SQL database appears the most practical option — especially when we introduce ecommerce facilities requiring robust transaction support. In the next article, we’ll discuss further project scenarios, and determine whether an SQL or NoSQL database would be the best solution.