django - raw SQL queries compatibility

Question

I have a raw SQL query with more than 80 lines and which results I can't reproduce using querysets. I want now to make this query compatible with both SQLite and PostgreSQL, so it can be run on development and production environments.

Is there any recommended or built-in way to proceed?

Example

So, for example, there's this PostgreSQL syntax error that I'm getting:

operator does not exist: boolean = integer

I adjusted the syntax and used some conditions to get the right SQL string for each case, but that just feels wrong to me:

db_engine = ''
engine = settings.DATABASES['default']['ENGINE']
if re.match('.*sqlite3$', engine):
    db_engine = 'sqlite3'
elif re.match('.*psycopg2$', engine):
    db_engine = 'postgresql'
elif re.match('.*mysql$', engine):
    db_engine = 'mysql'

cursor = connection.cursor()
cursor.execute("""
    ... some big query here ...
    WHERE 1=1
        AND some_boolean_field = %(value)s
""" % {
    'value': 'TRUE' if db_engine == 'postgresql' else '1'
})

The way I would do it - use sring formatting to change only selected parts of your queries depending on if settings.DEBUG is True or False — yuvi, Dec 12 '13 at 15:50

score 2 · Accepted Answer · edited May 23 '17 at 11:49

SQLite doesn't have boolean. So if you want queries that will work well in both databases, I recommend avoiding the boolean type if you're writing raw SQL.

boolean doesn't actually gain you anything much anyway, unless you have lots of boolean fields packed together, because they're already one byte each and will tend to get padded out to a word for alignment reasons.

Observe the raw type sizes:

regress=> SELECT pg_column_size(TRUE), pg_column_size(SMALLINT '1');
 pg_column_size | pg_column_size 
----------------+----------------
              1 |              2
(1 row)

and rather smaller difference after you consider row headers:

regress=> SELECT 
             pg_column_size( ROW(SMALLINT '1', SMALLINT '1', SMALLINT '0', SMALLINT '1') ),
             pg_column_size( ROW(true, true, false, true) );
 pg_column_size | pg_column_size 
----------------+----------------
             32 |             28
(1 row)

That's for the optimum of four consecutive boolean fields. In reality alignment restrictions will tend to mean that you might as well just use a smallint most of the time anyway.

If you do want to use boolean then to play well with SQLite, you'll need to cast everything to integers. That'll be a no-op on SQLite (so it'll work there) and on PostgreSQL will convert boolean to int.

 AND CAST(some_boolean_field AS integer) = CAST (%(value)s AS integer)

That lets you get rid of the special case for PostgreSQL booleans too; just pass 1 for true / 0 for false and use:

 AND CAST(some_boolean_field AS integer) = %(value)s

.... or, of course, you could just test against PostgreSQL, using the same engine in development and production. That way you won't get nasty surprises when you push changes live. I wrote a bit about ways to tweak a PostgreSQL install for use in throw-away testing in this earlier post.

Awesome. Thanks. And what about the booleans performance issues if I get to have tons and tons of rows in my tables. That'll play any diference? — vmassuchetto, Dec 15 '13 at 14:08
@ViniciusMassuchetto What performance issue do you envisage? Unless the rows are very wide (i.e. lots of adjacent boolean columns) you're not going to see much difference. If you're storing very wide rows with lots of booleans that might be a "design smell" anyway. Look into `varbit` or similar (and give up on testing with SQLite if you decide to use it). — Craig Ringer, Dec 15 '13 at 16:53

django - raw SQL queries compatibility

Example

1 Answers1