I'm curious...
When you start a project, given that you can use several different SQL engines, what makes you choose one over the other ?

I mean, I've played with mysql, but always was disappointed with it. I could not say why, but when I was learning SQL with it, I had an impression of "wrong" with it.

It became clear the day I touched postgresql... I immediately loved the namespaces, the updatable views, the stored procedure, and the power of the engine that allowed much more advanced queries (like union, intersect, except and nested queries) to be ran than mysql at the time.

It was slower at that time, true.
But today, it's as quick and still more advanced than mysql in my opinion, yet very accessible and well documented.

By the time I've learned sql, almost 9 years, I've touched many sql engines. Oracle, postgresql, mysql, ms sql server, sqlite....
I recognize that they have all their good and bad side.
But what I'm wondering, is if you take 1 because you know it, and don't bother to look elsewhere, or if the features of the db (replication, acid compliance, networked/network less, hot backup possibility) are something you consider.

I

I've always used MySQL, though I can't say I have the best reasons. It's what I know and it's often been the only database available to me given where I've hosted. Most of the apps I use default to MySQL too so it all just feeds into each other.

I'm on a server now with postresql available. I've always heard good things about it so it's something on my list to learn. I expect it won't be too hard to pick up what's specific to postgre.

Most of my sql needs usually aren't too advanced though so the differences between engines might not be so meaningful to me without learning sql more and better.

I'm using MySQL in my almost projects and it never mades me disappointed. In addition it is good that MySQL is free or you can have the enterprise version with affordable price.

I agree that the strenght of mysql is it's wide installed base.
But when, for example, you want to include a simple cache mechanism, do you consider that a lightweight engine, like sqlite could be more efficient ?

I tend to use sqlite for small sites, or for "secondary" systems.
It's marvelously practical for a caching mechanism, or when you don't need intensive query to be run.
The best thing being that it's 100% maintenance free, as there is no server, just a file...

VanGogh, yeah, the base of the syntax is pretty the same. 1 difference that bother e in postgres though is that the fields are all in minor case. Even if you define them using camel case, you receive them in minor when you do a select.
A bit annoying, I find, but not that much a problem.

It seems like the biggest difference is usually how your server side language connects to the db, and even that is pretty minor in most cases.

Like I said for me it's simply what I learned first and what was most readily available. My development needs haven't required me to look elsewhere. However, I usually hear people who use both MySQL and postresql preferring postre. My server has it now so it'll be worth experimenting with.

sqlite sounds interesting. I guess the name describes it well in that it's a lite version of a db for smaller projects. Is it easier to work with? or is the savings more in the resources required to use it?

Is it easier to work with? or is the savings more in the resources required to use it?

It's easier in the sens that the database is contained in a binary file, and that maintenance is reduced to 0.
http://www.sqlite.org

It's much used in portable devices, support acid transaction and partial triggers supports, the engine itself takes less than 300Ko of ram.
As there is no network connection there is no risk of outside leakage (if you keep the db file outside the site accessible files).
It allows you to move the db at the same time as the rest of the file, in case you must change host, which can be handy.
It allows you to to have a db without the need for your user to configure anything on their side.
And it have bindings in a lot of languages, which make the db able to be shared between different process (but take care of locking issues)

I had to do this once between PHP and Python. Php was reading the db, but it was a Python daemon that was writing infos into it.
It ran well, and still is, as far as I know...

I honestly love to use it, when I don't need features like replications, or referential constraints.

Thanks for the info. Page bookmarked for further reading.

Databases aren't something I've gone heavily into. I use them for what I need to, but know more knowledge would be beneficial. Sqlite sounds like a good solution to many db related issues.

Originally Posted by tripy

I'm curious...
When you start a project, given that you can use several different SQL engines, what makes you choose one over the other ?

The customer paying for the programming effort. I've had jobs writing reports, and also new features, against Oracle. Generally, as a consultant, I'm hired to fix and extend, not desing and build from the ground up.

Originally Posted by tripy

But what I'm wondering, is if you take 1 because you know it, and don't bother to look elsewhere, or if the features of the db (replication, acid compliance, networked/network less, hot backup possibility) are something you consider.

99 % of the jobs I've had were with SQL Server, the rest mostly Oracle. That reminds me of the chicken and the egg. Do I know SQL Serv because I'm always getting paid to work on it, or do I get paid to do it because I know it so well? (Almost completely down to the WMI level.)

Economists and historians tell us the key is specialization. That's not why I wound up being specialized in SQL Server, but it takes me to the same end point. Rather than knowing the ins and outs of all RDBMS servers fairly well, and knowing instantly which is best suited to a particular job, I know most of what can be known about one of them in particular. In the end, it's served me well, in that even when the most complex needs arise, making them happen in T-SQL code is short work.

I had to write a very complex system of storing and enforcing business rules, with a limited tool set. SQL Server and a Windows Service, but no dynamic SQL. (Which I'm glad for, D-SQL must die.) The "main" stored procedure joins 9 tables, has operators like "left semi anti join" in the query plan, which is as big as I've ever seen. The customer's requirement is that they can fully execute 2 rules per second. With current hardware and the new code, they can do 50. The service is only acting as a gate, exposing the database, which isn't talked to directly. I can't go into more detail because of a strict NDA, but most everyone thought this wouldn't be possible at all without dynamic SQL, let alone perform well.

I never done consulting, so I never had to think it that way.
I ever used what the project managers had decided.
But, on my first developer job, my contact told me "get yourself acquainted with the SQL language" some month before I started (I was still in school).
But on my first important project, swissfriends.ch, a dating site, the project manager had an oracle background, which led me on the postgresql side.

After having finished the 1st phase of that project, working with mysql seemed just plain wrong, and I started examining other db engines.
This led me to realize that some tools where better fited for the job, and I started to choose between them when I started personal projects.
But there where always personal projects, I never have been asked what I would prefer working with

but most everyone thought this wouldn't be possible at all without dynamic SQL, let alone perform well.

Yeah, but don't forget you're like a wizard in a robe when you approach sql server ;-)
I have to say that I find the product in itself great, but I am irritated at my company handling the licences right now.

I'm working for a company that wrote a program to handle a pharmacy. It's a very complicated business, mainly because many laws are related to the client personal life protection and the exchange of the datas between the insurances and the pharmacies.
The usual setup is 1 server on a central, and several point of sale, which are running ms sql express edition.
Now, for a pos and daily operations, this is perfect.

Today, we had a meeting about how to handle the migration of a customer to our software, because he has 13 years of history (customers, commands, delivery, orders, files....) that makes the DB close to 3.8 Go.
Sql server express edition support only 4Gb per instances....

But rather than explaining it to the customer, and investigating into a licensed sql server, the import procedure will be modified to retain the last 5 years.

As I'm relatively new in that enterprise, I dared asking "Why not upgrade the customer db to a retail version".
My team manager had a short smile, but the department manager not.
Simply put, money put on ms licence is money not coming to the enterprise, so we will avoid it.

I later, when talkin with my team manager, learnt that it's a long debate between him and the dept manager, and from nearly every db guy that worked into this enterprise.

This made me wondering what kind of reflection goes before choosing a technology as crucial as the db engine.
I know that I would probably have chosen an oss engine for that job, but I'm very biased on that question. I know it, and I assume it.

Originally Posted by tripy

Today, we had a meeting about how to handle the migration of a customer to our software, because he has 13 years of history (customers, commands, delivery, orders, files....) that makes the DB close to 3.8 Go.

Sql server express edition support only 4Gb per instances....

That 4 GB includes indexes. I wish it were at least 4 GB of data, but it can be worse than that!

Originally Posted by tripy

But rather than explaining it to the customer, and investigating into a licensed sql server, the import procedure will be modified to retain the last 5 years.

That's pretty common. Maybe you can talk them into at least running 2 instances, and instead of deleting anything older than 5 years, moving it to the other database? I personally hate to see data be deleted. If it has a value, it should be kept available.

Maybe you can talk them into at least running 2 instances

Yes, it's what it's going to be.
An full instance, with all the datas, for reference, and a shortened instance for the day to day operations...

But still, it's weeks of work just for that, and it rather bothers me.
Maybe I mind too much, I don't know, but as a lazy developper, I hate doing work to circumvent something that could be lifted with the right tool...

Tripy, have you ever had any security concerns with SQLite? I took a look at it a while back ago (evaluating it for a distributable app) and the fact that it doesn't support password access was a little off-putting to me. I know that you can restrict access by .htaccess or put it below the root directory, but not everyone has those options. I'm a little curious at your experience in that area.

Tripy, have you ever had any security concerns with SQLite?

No.
I own my own server, and administer it.
I'm used to basic security forensics, and apply them. I run an hardened server, and I monitor it.

It's true that the point of not being able to put the file outside of the web server access never hit me.
It can be a risk, it's true. But frankly, any server left unmonitored can be a risk as big. So no, it never stopped me.

And for the usage I did made of it, it never was a big deal.
It was mostly for a link db (url shortener) or a cache system (page generated where saved in it).
So, no big deal if it leaked...

Frankly, I would not trust much neither an unmonitored/unpatched mysql server.
It's up to you to know what you can trust the most.
Now, I don't know if it can help, but usually, I put my sqlite files into an

PHP Code:

define('SQLITE_DB','something.sql3');
define('SQLITE_DIR',$_SERVER['DOCUMENT_ROOT']."../sqlite/");
if(!is_dir(SQLITE_DIR)){
  mkdir(SQLITE_DIR,0777,TRUE);
    or die('SQLITE folder could not be created');
}
if(!is_writable(SQLITE_DIR))}
  die('DB folder not writable');
}
//....
//And later, in a PDO db handler
$_db = new PDO("sqlite:".SQLITE_DB);
$_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 


Originally Posted by tripy

No.
I own my own server, and administer it.
I'm used to basic security forensics, and apply them. I run an hardened server, and I monitor it.

Frankly, I would not trust much neither an unmonitored/unpatched mysql server.

This is the best approach. I wouldn't run a free for all SQL Server from Microsoft, either, or from Oracle. Especially with password authentication - has to be Active Directory, and the server should almost always be in the demilitarized zone of your network.

There are two competing ethics here. One is that things should only ever be done the cheapest and easiest way. The other is that things should be done the right or best way. Both are valid approaches, depending on the specific requirements of the situation. Saying that MySQL is free, so it's the only answer, is not valid. If I needed to fly to Europe, I wouldn't do it in the $300 Ford Escort someone is selling on Craigslist just because it's cheap. With databases, too, hammers are matched to nails and screw drivers to screws. Paying devs to reinvent the wheel is far and away more expensive and more error prone than buying a license.

Thanks Tripy. The reason I'm not using it for my distributable app is because some people will probably be installing it on a shared server and I didn't want to have to deal with the support for that. I agree, though, on your own server where you have options to secure, it might be a good fit depending on the situation.