[Sauce1971]

I used to enjoy working with Oracle databases, but the horrible pricing and high need for dba maintenance was always discomforting. Switch for a while to MSSQL, but then the market for Windows apps disappeared. Have been using mySQL with very little sort of DB satisfaction. Now I don't know why anyone would not choose PostgreSQL for almost any project. Oracle is a bad apple, making money with methods aking to extortion.

[crag]

I still wonder why SQLite isn't used more. Granted for busy large sites/applications SQLite is the wrong tool. But most sites using MySQL are small.

[jeffdavis]

Disclaimer: It's been a while since I tried to use SQLite for anything.

I've never really felt that it was actually easier to use than a client/server DBMS. I can imagine that it's good for some kinds of embedded applications, but for normal web applications I don't see any improvement in usability. Juggling files is not any easier than setting up a connection.

And there were always annoyances associated with the simplicity. All those "complex features" in a real DBMS turn out to be useful sooner than you think (and I don't mean "once in production" I mean while still in development). Even the simplest applications can usually benefit from good support for dates and times.

I think SQLite just drew the line in the wrong place for most applications (although it may serve a niche quite well). Oversimplification requires the developer to reinvent, which results in a much more complex system overall (or one that at least requires more work to develop and maintain). Key/value stores have similar problems for general-purpose application development.

[crag]

1. With the PDO (or any ORM) setting up and "using" SQLite is as easy as any other database.

2. There are several really nice front-ends to SQlite, along with the phpmyadmin like app for SQlite. Like MySQL you'll never have to use the command line (for most operations).

3. SQLite is now included with just about every scripting language (except MS' langs - where it's just a matter of downloading the app and an ODBC driver).

4. "Because it requires no configuration and stores information in ordinary disk files, SQLite is a popular choice as the database to back small to medium-sized websites." - sqlite.com

Simple websites. Would I run this site on SQLite? Of course not. Any shopping site with a million users? No. But a personal blog? Yes. A company website used for marketing and information (and maybe a few Contact US pages)? Yes.

There are millions of sites where using MySQl is over-kill. But everyone uses it because, like PHP, it's always there.

5. SQlite uses SQL. Very few commands aren't available.. like you can't write to a View. But I think that's true in MySQl too. There's a small list on sqlite.com. It even has transactions.

Simply put, if you don't require writing to the db from multiple sources (and lets face it, 80% of the sites out there don't.); running your app or site on sqlite is fine.

[jeffdavis]

"setting up and "using" SQLite is as easy as any other database"

That's exactly the problem. If it's easier, then maybe I'll use it. If it's just as easy, but does less, I have no reason to bother using it, for two reasons:

1. There is non-zero risk that I will need one of the other features.

2. Even if I don't, why would I want to spend time learning something if it isn't better (at something) than what I already know how to use?

I haven't seen any compelling reason to use SQLite outside of the mobile or embedded space.

"over-kill"

What do you mean by that, and why is it bad?

[crag]

""over-kill"

What do you mean by that, and why is it bad?"

It's not. I'm just pointing out that there are alternatives to MySQL. And for small sites/apps that don't require something like Postgres; that can be SQLite.

Frankly I miss the days when an easy to install and use database was available to the masses. Remember Paradox, dBase and Foxpro? Sure all those database where powerful (xBase, for example) and used to develop complex applications. But they were also used for simple applications.

I would love to see something equivalent to those online. Oh wait, there is; SQLite.

[pstuart]

You can write to a view via triggers.

[nahname]

SQLite is not a real solution for anything needing to support more than one user at a time. It's fine for things like managing bookmarks within firefox, but not for a full relational database replacement. The write access is single threaded (at least last time I used it).

[xentronium]

Well, mongo has write lock too, and people seem to use it a lot.

[paulgb]

My experience with SQLite is that if you're using it as more than an object-store, you'll be disappointed by the limited subset of SQL it supports. And if you do just need an object store, Mongo is often a better fit and gives you flexible schema.

That said, as an embedded DB I would use SQLite.

[Sauce1971]

SQLite is very nice for simple single user apps :-)

[xradionut]

MS SQL Server is mainly used by corporations for corporations, so there is a market and good money for apps and expertise in those circles if you have the expertise in the MS stack.

For OSS projects that require a RDBMS, PostgreSQL is a good choice. I instinctively choose it over MySQL years ago and have been pleased.

[taligent]

>Now I don't know why anyone would not choose PostgreSQL for almost any project.

No offense but people who make statements like this clearly don't know much about the current state of databases. PostgreSQL is pretty bad in many areas compared to the newer databases e.g. ease of clustering and sharding, developer friendliness, JSON support, tool support etc.

It's great as a replacement for a typical RDBMS but if you're working on a new startup project I would see no reason to use it.

[killedbydeath]

Not sure what you mean by lack of developer friendliness and what specific tools you find it lacking. Postgres is a great choice for new startups that need an RDBMS and like having schema for their data. There is built-in JSON support in upcoming 9.2 release and most JSON-to-schema mapping should be done in application anyway. Sharding is easy to do at application level; you would lose ability to do certain queries across shards but most solutions offering automatic sharding are designed specifically not to allow most types inter-shard querying and instead offer map-reduce.

[taligent]

Sorry but PostgreSQL's JSON support is a joke. It is nothing more than a VARCHAR that validates. You can't search or index across fields and the way you actually query the JSON is cumbersome at best.

Also all of your arguments are "push to the application layer". Well my point is that with most of the newer databases you don't have to reimplement the wheel.

[killedbydeath]

If you need to store arbitrary JSON fields in the database and index them, then you indeed are better of with some NoSQL database. But I strongly disagree with the statement that all new startups deal with that kind of data organization. Many problems have good structure and can benefit from imposing structure (schema) upfront. In which case any JSON you get will parse in the application, map it to your data organization and store in a clean strict relational format, with all indexable fields extracted to separate columns. I do not store my logs in Postgres, but I do store my billing transactions in Postgres. There are many different types of data with different tradeoffs involved. For most, Postgres is a great choice.

[fooyc]

> ease of clustering and sharding, developer friendliness, JSON support, tool support etc.

Which RDBMS do you have in mind ?

[taligent]

Well some are better than others right. Riak/CouchDB are great when it comes to clustering/sharding. MongoDB has the best JSON support and is very friendly for developers. MySQL is proven, has the best tool support and most widely supported.

The fact is that just like one programming language doesn't satisfy all use cases. Neither does one database. So maybe PostgreSQL 'supporters' should stop trying to claim it is perfect for everyone.

[icelancer]

>RDBMS

>MongoDB

wat