Database Translation Guide

October 13, 2010 — Code

Web frameworks that come with database abstraction layers like ActiveRecord have given us a degree of freedom in the database software we use. Database portability increases the number of platforms on which we can deploy our apps, but you still need to know how to use the different databases. For those of you who are used to MySQL or PostgreSQL and need to learn SQLite (or some other combination of those), here are some rough command equivalents in each to get you started.

Basic Administration

Some of these commands are given at your operating system command prompt and others are given at the database client command prompt. I have indicated the prompt in each command below: $ is your OS, and db> is your database client.

Start the Database Client

SQLite

$ sqlite <db file>

MySQL

$ mysql -u<user> -p <db name>

PostgreSQL

$ psql -U <user> <db name>

List Available Databases

SQLite

Databases are stored in separate files, usually with a .sqlite3 or .db extension. SQLite cannot find these files for you.

MySQL

db> SHOW DATABASES;

PostgreSQL

db> SELECT datname FROM pg_database;

Create a Database

SQLite

$ sqlite3 <db file>

MySQL

$ mysqladmin -u<user> -p create <db name>

PostgreSQL

$ createdb <db name>

Change the Current Database

SQLite

Close the client and re-open with another file.

MySQL

db> USE <db name>

PostgreSQL

db> \connect <db name>

List Tables in a Database

SQLite

db> .schema

MySQL

db> SHOW tables;

PostgreSQL

db> \dt

Display Schema for a Table

SQLite

db> .schema <table>

MySQL

db> DESCRIBE <table>;

PostgreSQL

db> \d <table>

Functions

Where there is more than one way to accomplish the same thing I have chosen the most generally-usable functions since that is normally what you’ll want to use for cross-database compatibility. There are some common things which SQLite cannot do out of the box, but a lot of useful functions can be added with the extension-functions. Compilation instructions are in the downloaded file. Load the extension at the SQLite prompt:

.load <compiled extension filename>

Select a Substring

SQLite, MySQL, and PostgreSQL

SUBSTR(<string>, <start>, <length>) # length argument is optional

Find the Position of a Substring

SQLite

CHARINDEX(<sta>, <haystack>) # requires extension-functions

MySQL, and PostgreSQL

POSITION(<sta> IN <haystack>)

Raise a Number to a Power

SQLite, MySQL, and PostgreSQL

POWER(<base>, <exponent>) # requires extension-functions in SQLite

Get the Year From a Date

SQLite

STRFTIME("%Y", <date>)

MySQL

YEAR(<date>)

PostgreSQL

EXTRACT(YEAR FROM <date>)

If/Then/Else

MySQL provides an IF() function that many people are used to:

MySQL

IF(<condition>, <then-expression>, <else-expression>)

This is convenient but not supported by other databases. It’s better to use the more widely-implemented CASE statement:

SQLite, MySQL, and PostgreSQL

CASE WHEN <condition> THEN <expression> ELSE <expression> END

Select an Alternative to Null

This is commonly achieved in MySQL through the use of IFNULL(x, y), but the COALESCE function is equivalent in the case of two arguments, more general, and more widely implemented. It takes any number of arguments and returns the first one which is not null.

SQLite, MySQL, and PostgreSQL

COALESCE(<value1>, <value2>, ...)

That’s all for now, though I will probably add to this list in the future. If there is anything you would like to see added, please let me know.