SQLite.jsm is an abstraction around the low-level Storage APIs that you would have used before. However, it eliminates most of the footguns and makes it easier to write code that doesn't jank the browser and is less prone to memory leaks. It even has an API to free as much memory as possible from the current connection!
If you currently use SQLite via Storage, I highly recommend taking a look at SQLite.jsm - especially if you are using synchronous APIs.
If you are investigating SQLite for the storage needs of your add-on or browser feature, please keep in mind that SQLite can incur lots of filesystem I/O and may run slowly on old machines (especially with magnetic hard drives) and especially with its default configuration. You may be interested in low-level file I/O using OS.File instead.
If you insist on using SQLite, please educate yourself on and then seriously consider using Write-Ahead Logging mode on your database. Some detailed discussion on SQLite behavior as it pertains to Firefox can be found in bug 830492. I hope to eventually incorporate more sane by default connection options into SQLite.jsm to make it easier for add-ons and browser features to have the least-impactful behavior by default (e.g. enable WAL by default). Until then, please, please, please research PRAGMA statements to optimize how your SQLite database runs so it has as little performance overhead as possible. Also consider dropping into an IRC channel on irc.mozilla.org and asking for advice from one of the many who have fallen into SQLite's many performance pitfalls (including me).