MySQL also has a C/C++ API as well so that would be another good choice to look into. In all honesty though almost every major SQL backend has some sort of C++ API out there. SQLAPI++ in fact is an API for C++ that combines all of them into one API package and is cross platform.

If you're asking for information about the database engines themselves….

SQLite is a soft-typed SQL-compatible database that is designed to be lightweight, rather than fast or robust. The engine is embedded into your application, so you don't need a seperate database to connect to. The downsides are that it's not as fast or flexible as a "real" database, and because the types are "soft", you can't have the database itself do the same kind of sanity checking you might with a full RDBMS.

MySQL has come a long way and is very popular. It does the job for what MOST people seem to want to use an SQL database for. It's pretty fast, and still somewhat lightweight. It supports most of the SQL standard.

PostgreSQL is the database I use. It's a little heavier, but it supports the full SQL standard, including foreign key constraints, triggers, cascade deletes, and also supports stored procedures in a variety of languages. Like MySQL, it's free and somewhat easy to install, but doesn't have as widespread support.

If you want something that feels more like Access, SQLite is probably the choice. If you want something that feels more like MS SQL Server, PostgreSQL would be my first choice, with MySQL only if you know you'll never want stored procedures or "fancy" SQL constructs. :)

If you're using C++, you're in good shape. If you're using C.. much pain and suffering awaits you.

SQLite was specifically designed to be robust, first and foremost. And IMHO, succeeds in that regard. It has the most exhaustive testing suite of any "free" database software that I know of.

SQLite is not ACID compliant, because of its use of "soft" types. If you added check constraints to every column to enforce that values inserted actually match the column type, it would be. Other than that, it does seem to be pretty robust.

SQLite is not ACID compliant, because of its use of "soft" types. If you added check constraints to every column to enforce that values inserted actually match the column type, it would be. Other than that, it does seem to be pretty robust.

No, it's ACID compliant because of its conversion to the correct type. Consistency can be maintained by either by rejecting the update or by conversion. In all cases, the end result is that the field MUST contain the type it was defined as in order to comply with Consistency. For example, IBM's DB2 can also convert data types before storing. Oracle allows PL-SQL hooks to convert data-types.

ACID consistency means that only valid data can be written to the database. If a transaction is executed that violates the databases consistency rules, the entire transaction should be rolled back and the database restored to a state consistent with those rules. SQLite by design doesn't enforce integrity constraints on data written to the database by default so one could insert e.g. a string into an integer column or an invalid date into a database with no error. That means that to make an SQLite database ACID-compliant one must add check constraints to check every value before inserting it to the database, ensuring e.g. the correct data type, string length or a valid integer value. In cases when the ACID compliance is not needed or the overhead is not acceptable the consistency of data inserted into a database must be verified by an application.

(emphasis mine)

In other words, it would appear that SQLite can be made ACID compliant but is not necessarily so.

Yes, SQLite does not convert data. If you insert a string into an integer column, it really will be stored in that column as a string. This is one of the major complaints people have, since this poses a problem for static typed languages that expect an integer column to have only integer data.

I've done a fair amount of work with Python and SQLite and yes, it stores everything as strings. Once you know that though, it's really a non-issue. I'll write a data mapping module to my application and do all the casts I need on retrieval. The column type is useful for getting the 'ORDER BY' you want when doing selects.

For the OP, SQLite is well suited for handling data for a stand-alone application. The kind where you ask questions like 'do I need a database or a flatfile?' SQLite has no access control – no usernames, no passwords, just open the file and go. This makes is very dev friendly.

MySQL (or in agreement with Quixadhal, PostgreSQL) is better suited for projects where multiple applications/machines need access to the data with access control. They may have improved it since, but one point I'd give MySQL over PostgreSQL is ease of install on *nix systems. PgSQL made me jump through a lot more hoops.

Yes, SQLite does not convert data. If you insert a string into an integer column, it really will be stored in that column as a string. This is one of the major complaints people have, since this poses a problem for static typed languages that expect an integer column to have only integer data.

I don't understand how this is a problem (in C at least). MySQL may not do the same thing under the hood, but the C SQL api only ever returns a string, and you can only ever insert from a string. So regardless of what you want from the SELECT calls, you always get a row of strings which you have to convert to an integer if need be.

It's not just the API; it's also controlling what goes into the columns in the first place. If the DB isn't doing integrity checking, then you could insert "foo" into a column that should only take numbers, if everything is left as a string under the hood (and, again, type integrity isn't being checked).

Yes, SQLite does not convert data. If you insert a string into an integer column, it really will be stored in that column as a string. This is one of the major complaints people have, since this poses a problem for static typed languages that expect an integer column to have only integer data.

I don't understand how this is a problem (in C at least). MySQL may not do the same thing under the hood, but the C SQL api only ever returns a string, and you can only ever insert from a string. So regardless of what you want from the SELECT calls, you always get a row of strings which you have to convert to an integer if need be.

As David said, if you connect to an SQLite database, you can indeed do something like insert into foo (thing_id, thing_text) values ('foo', 23); It would let you do that. Now, when trying to aggregate with something like select max(thing_id) from foo; what should the result be?

Also, there are MySQL API's that don't use strings for everything. They are more complicated to use, but they do allow you to bind variables directly so query results deposit data directly into an integer/float/etc, and likewise bound parameters take data from intergers/floats/etc.

If you're using one of these API's (and you should, they are safer!), asking for a result set to be stuffed into array variables would tend to crash if one column's array is expecting 32-bit integers and instead it gets 40-bit floats.

Yes, I agree: they're safer, and in some cases probably faster, since it can shuffle binary data around instead of converting to strings. Not to mention that it would be irritating to have to do the conversions yourself.