Translate SQL Syntax Between Databases


SQL::Translator is an interesting Perl module that, among other things, can convert database table definitions to and from several DB platforms. Essentially, this could allow you to write and maintain your table definition code for a single platform, say MySQL, and then use Translator to output table structure into Oracle, Sybase, or PostgreSQL dialects.

Manipulation of data, such as SELECT, INSERT, UPDATE and DELETE are not yet supported, so you’re still on your own there if you’re writing platform agnostic code. That said, this is an incredibly useful tool. Just consider this example that Chris Dolan posted on use Perl:

MySQL understands this syntax:

create table book (
id int,
author_id int,
FOREIGN KEY fk_author_id (author_id) REFERENCES author (id)
) TYPE=InnoDB;

but not this nicer syntax (it silently ignores the “references” clause):

create table book (
id int,
author_id int references author (id),
) TYPE=InnoDB;

Perl to the rescue! I can write my schema in the latter syntax and use SQL::Translator to rewrite into the supported syntax.


Discuss this article with the rest of the community on our Discord server!


Maker Faire Bay Area 2023 - Mare Island, CA

Escape to an island of imagination + innovation as Maker Faire Bay Area returns for its 15th iteration!

Buy Tickets today! SAVE 15% and lock-in your preferred date(s).