The Design of Software

anyone lived with both and have strong opinions about pros and cons?

sqlite is embedded in your code. MySQL isn't. That's the most important difference.
sqlite is public domain. MySQL is GPLd. That's the most important legal difference.

I don't think they are in competition for any given project.

For low load websites, SQLite has worked great in our projects. If you're doing an application in C, its API is simply unbeatable. Perhaps its most distinguishing feature is that it pretty much ignores types. This is, in fact, a "feature", and I have found that it gives it flexibility that is lacking in other situations(although, you have to put your dates in very specific formats to get the sort order to come out right...).

For web development purposes, if you're doing a really high load website, you're going to want to use a non-file based database(personally, I prefer PostgreSQL, but sub MySQL, SQL Server, whatever...)

I also can't express just how great it is for desktop apps though. Its great for 95% of the situations you would need to save data in an application in a "file format" of some variety. It make debugging great(fire up the console on the file your app is writing too and watch inserts as they happen). No configuration at all. No mucking about with binary file formats. No XML parsing.

Relating to the previous poster though, the only place you might really even consider both is low load web applications. While I agree that its good to think big, if you're app is likely to be low load for most of its life, you won't regret SQLite. Another case you might consider it for is a web app that interoperates with a "fatter" version on the desktop, you could do your "export/import" between the two using SQLite.

I too like SQLLite, so long as you can live with its limitations.  Two big ones:

(1) No data types -- everything is just a generic field (think varchar)
(2) No security except for native filesystem security on the data file

But the fact remains that a lot of projects will fit in just fine with these limitations.

SQLite is nice in that it's contained in a file.  There's no install other than the driver, and no big server to run in the background.  It travels nicely -- very neat and clean.

New Riders has a book out on SQLite, and you could read it in and afternoon.  And believe me -- that's about all there is to it.

To continue what Ori Berger said:

SQLite is best for single-user use (one user accessing the database at a time).  MySQL is best for multi-user use.

thanks peeps.