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.


comments powered by Disqus