Database Translation Guide
October 13, 2010
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 <db file>
$ mysql -u<user> -p <db name>
$ psql -U <user> <db name>
List Available Databases
Databases are stored in separate files, usually with a .sqlite3 or .db extension. SQLite cannot find these files for you.
db> SHOW DATABASES;
db> SELECT datname FROM pg_database;
Create a Database
$ sqlite3 <db file>
$ mysqladmin -u<user> -p create <db name>
$ createdb <db name>
Change the Current Database
Close the client and re-open with another file.
db> USE <db name>
db> \connect <db name>
List Tables in a Database
db> .schema
db> SHOW tables;
db> \dt
Display Schema for a Table
db> .schema <table>
db> DESCRIBE <table>;
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
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
STRFTIME("%Y", <date>)
YEAR(<date>)
EXTRACT(YEAR FROM <date>)
If/Then/Else
MySQL provides an IF() function that many people are used to:
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.
■