jump to navigation

Advice on MySQL: Don’t. February 17, 2012

Posted by PythonGuy in PostgreSQL.
add a comment

So, I ran into a new bug I had never before seen with MySQL today.

This kind of bug is the sort of thing that would make me tell other developers to never use MySQL.

Except I already tell them that, so nothing changes.

Please give PostgreSQL a chance. If you can’t figure it out after 10 minutes, you haven’t tried hard enough and should send out a call for help on any of the public forums out there. You’ll find you won’t be playing games with silly bugs that will give you pause about whether you made the right decision.

Ideal PostgreSQL Interface January 2, 2006

Posted by PythonGuy in PostgreSQL, PyGreSQL.
add a comment

I am pretty familiar with PyGreSQL (http://www.pygresql.org/) and psycopg (http://initd.org/projects/psycopg2). Unfortunately, neither of these are very well documented. If it isn’t documented, it really doesn’t exist for the rest of the world.

I’d like to talk about my ideal interface to the PostgreSQL database. First, let me trash the concept of DB-API. I don’t believe that there can have a standard interface for the major databases, nor should there be one. Fundamentally, the databases are so different that to try and shoehorn them into a single compatible featureset is to limit their capabilities.

One of the arguments is that you can write code for one database, and if you only use the compatible featureset of DB-API, it can port directly to another database. This just doesn’t work in practice. The behavior of the databases is so different that even if this did work, it wouldn’t be a good idea.

Which brings me to my point: Each database should have a unique interface suited specifically for that database. The compatibility layer should be something above the database interface. That is, I should provide object interfaces to the underlying data, and then write a customized data access layer for each database people want implemented. This allows you to be truly innovative in what kinds of databases you use: traditional SQL, filesystem, BDB, or even CORBA or ICE interface layers.

What does PostgreSQL do well? PostgreSQL has a strong type system. Any interface to the PostgreSQL system should allow users to specify wrappers for new types. When an integer comes down the pipeline, it should appear as an integer in the Python program. The same goes for the other types as well. PostgreSQL has array values. These should work in Python as well. That is, if I select a column that is an array value, I should get an array in Python.

PostgreSQL also has an expensive connection phase. That means any PostgreSQL interface should cache connections and share them so that when you want a new connection, an existing one can be used.

PostgreSQL only stores prepared statements for the connection they were prepared in. In reality, almost every query you make should be prepared and remembered by the client program. That means you shouldn’t be allowed to write queries that have values in them, such as “id=5”. Instead, you should be strongly encouraged to write “id=$1” and then pass in 5 as “$1”.

PostgreSQL has nested transactions. They should be used, because they make the life of the user easier. You should have transaction objects that when they are deleted, commit the transaction.

These are some of the features that make PostgreSQL unique and preferable over another database. I am going to write my own interface to do this one of these days.