I am developing a application that needs to store data with many writes and reads as requiring fast searching of data (the need for indexes of some sort), and also be able to serialize and save the data.
Currently I am thinking about using SQLite, which gets the job done, but I am open for alternatives. The SQLite's syntax really doesn't fit elegantly in C++ code (for example I am forced to access the results by column index numbers instead of column names, etc).
It sounds as if a database is the only real option for you. If you chose SQLite or MySql or Postgres does not really matter in the column index point, though.
Maybe what you really want is a better database abstraction. You might want to try Poco, it has a great one: http://pocoproject.org/
Stay with SQLite but find a good C++ library for this.
This StackOverflow question should help you ...
Could i suggest that you take a list of the wrapper APIs listed on the Sqlite site? There's plenty there for many languages, including C++. Sqlite Plus looks particularly good. POCO, which 'usuallyuseless' mentioned, looks good too.
If SQL syntax is not important to you, I recommend MetaKit - It is a slightly different approach but it quite powerful and I personally know of more than a few commercial projects which use it successfully, even on embedded systems.
This Tutorial is quite helpful to get started.
I would argue that the added dependency on a wrapper library is not worth the cost of having to deal with the costs of that extra dependency. You don't want to be stuck debugging these wrapper libs when the documentation for them may be scant. SQLLite is bound to be more stable and have better documentation and therefore a more reliable dependency.
I would either deal with sqllite or wrap it yourself in some intelligent way based on your end application.
Instead of a sql derivative, you might think about using an inverted index IR library like Lucene (or one of the many clones out there). I know Lucene is Java, but you could write a managed C++ library to interop with Lecene.Net.
As far as speed, I'm very happy with the performance of Lucene for its reads. Its not as optimized for its writes, but its still pretty good, as long as your not trying to build an index of millions of "documents".
If you're not working on Windows, you might look into Tokyo Cabinet. It looks to be quite good. I'm waiting for the Win32 port, so I can't say much about it other than the performance looks to be impressive.
http://anyall.org/blog/2009/04/performance-comparison-keyvalue-stores-for-language-model-counts/
This is not an SQL derivative. It's a straight b-tree/hash based store. This is very welcome, in my opinion. The only competitive library that I'm aware of is Berkeley DB, which has less than desirable licensing.
Berkeley DB aka SleepyCat. If you search the archives there is an article by Mike Olsen that talks about the major disadvantages of SQL in any project. There is another followup article within the last year that suggests that SQL is on it's way out because the ORM layers are getting so much better.
I agree, it's bogus to say SQLite is incompatible with either C++ or column name indexing There are plenty of C++ SQLite wrappers that encourage accessing columns by name. SQLite++ is just one of many.
If you ask me stay with SQLite and "do it your self" - the wrapping it is quite simple I did various wrappings over SQLite myself. Why? Because its API is much friendlier than anything else database related and you need not spend much effort. With a wrapper library you will only add more unlnowns and most importantly most wrappers are not especially dedicated to serve embedded databases. I am always repeating that with embedded database the right way is very different from the optimal approach for a database server...