Recs.
Updated
Not only is this one of the most trusted databases in the world, it's also one of the most supported in Node.
A portable version can be dowloaded from GitHub , for a small application an older and lightweight version like 9.4 will do fine.
A PostgreSQL database can easily be turned into a fast REST API using Postgrest.
SpecsUpdate
Pros
Pro High performance
PostgreSQL performance increases with each release, this is also proven by many benchmark tests.
Notable performance features include:
As PostgreSQL only supports one storage engine, it has been able to integrate and optimise it and with the rest of the database. This has resulted in multiple benefits such as the ability to allow different transaction types to co-exist efficiently without the need to select storage engine types once for each table ahead of time.
On the fly data compression resulting in less IO required for reading.
Asynchronous + synchronous Replication.
PostgreSQL supports an asynchronous API for use by client applications. It is reported to increase performance by up to 40% and is not supported by MySQL.
Designed to scale very well with large numbers of cores at high concurrency levels.
Pro Support for JSON data type
JSON data can be stored as a column with optional indexes. In 9.4 (upcoming at the time of this writing), JSONB will be a binary version of JSON that will save space. It's like the best of the NO-SQL world without having to give up ACID and Relationships. This means that cascading deletes can be done in a single Transaction across multiple JSON documents.
Pro Fully ACID compliant
PostgreSQL is known to have a very holistic approach to robustness and data integrity which is reflected by it being fully ACID compliant.
PostgreSQL has always been strict about making sure data is valid before allowing it into the database, and there is no way for a client to bypass those checks.
Depending on your requirements, ACID compliance might be important.
Cons
Con Not suited for small apps
Because of its complexity and power, it may be an overkill to use PostgreSQL in small applications that will not make use of its full power.