Short version: Web SQL was deprecated because standards are really important and turning Web SQL into a proper standard would have been prohibitively difficult.
Since existing implementations of Web SQL are basically wrappers around SQLite, any attempt to define a standard of it was basically "do what SQLite does." This isn't good enough; a true standard needs to be self-contained, to define the interface and corner cases and exceptions itself instead of pointing to an existing implementation (especially a third-party implementation like SQLite). Otherwise, you run the risk of taking one particular implementation's quirks and enshrining them as the standard. From what I've read, the W3C prefers multiple independent implementations of proposed standards to help ensure that this happens; since Web SQL was so tied to SQLite, that just wasn't going to happen.
Mozilla's blog gives more details on their reasoning in particular for not supporting Web SQL; apparently they were one of the major voices in getting Web SQL deprecated.
Should you go with Web SQL now? I don't expect the vendors that currently support it (like Google and Apple) to drop it any time soon, but IE and Firefox won't be adding it, and since it's deprecated, why invest in it? (For example, Ido Green, with Google Developer Relations, doesn't recommend using it.)