SQL::Shell(3)

📦

version 1.18 · 3 versions

1.18 · fedora 43 (latest)
1.17 · fedora 43
1.18 · fedora 44

💖 SQL::Shell 💖

ok bestie, welcome to SQL::Shell — a command interpreter for DBI shells, straight out of your friendly neighbourhood Perl docs. This is the part where I tell you everything, so grab a boba and let's go 🧋✨

✨ NAME ✨

SQL::Shell — command interpreter for DBI shells. that's it, that's the vibe. 👑

✨ SYNOPSIS ✨

        use SQL::Shell;

        #Initialise and configure
        my $sqlsh = new SQL::Shell(\%settings);
        $sqlsh->set($setting, $new_value);
        $value = $sqlsh->get($setting);

        #Interpret commands
        $sqlsh->execute_command($command);
        $sqlsh->run_script($filename);

✨ DESCRIPTION ✨

so SQL::Shell is a command-interpreter API for building shells and batch scripts. it's giving framework energy, not just a lil script. 💅

and here's the tea: a command-line interface with readline support — sqlsh.pl — is included as part of the CPAN distribution. also, go read SQL::Shell::Manual for a user guide. it's the companion piece, the main character's bestie, iykyk. 🫶

SQL::Shell offers features similar to the mysql or sql*plus client programs but it's database independent. the default command syntax is arguably more user-friendly than dbish — no cap — because you don't need any go, do or slashes to fire SQL statements at the database. you just... type them. iconic behaviour honestly. 🔥

Features include:

issuing common SQL statements by simply typing them
command history
listing drivers, datasources, tables
describing a table or the entire schema
dumping and loading data to/from delimited text files
character set conversion when loading data
logging of queries, results or all commands to file
a number of formats for display/logging data (sql, xml, delimited, boxed)
executing a series of commands from a file

You can also install custom commands, rendering formats and command history mechanisms. All the commands run by the interpreter are available via the API so if you don't like the default command syntax you can replace the command regexes with your own. it's your shell now, you can customise the whole wardrobe. 🎀

It's been developed and used in anger with Oracle and mysql but should work with any database with a DBD:: driver. any database. periodt. 🌈

✨ METHODS ✨

$sqlsh = new SQL::Shell(\%settings); — Constructs a new object and initialises it with a set of settings. See "SETTINGS" for a complete list. (we're building the whole personality here, bestie 🌸)
$sqlsh->set($setting, $new_value) — Changes a setting once the object has been constructed. See "SETTINGS" for a complete list.
$value = $sqlsh->get($setting) — Fetches a setting. See "SETTINGS" for a complete list. ✨

🎀 Commands 🎀

$sqlsh->execute_cmd($command) — Executes a command ($command is a string).

Returns 1 if the command was successful. Returns 0 if the command was unsuccessful. so it's a lil pass/fail, very honest of it. 💯

$sqlsh->run_script($filename) — Executes a sequence of commands in a file. Dies if there is a problem. 💀 (fr, it dies, so keep an eye on it)
$sqlsh->install_cmds(\%additional_commands) — %additional_commands should contain a mapping of regex to coderef. See "INSTALLING CUSTOM COMMANDS" for more information.
$sqlsh->uninstall_cmds(\@commands) — @additional_commands should contain a list of regexes to remove. If uninstall_cmds is called with no arguments, all commands will be uninstalled. bye bye commands 👋
$sqlsh->set_param($param, $value) — Equivalent to the set <param> <value> command. In many cases this will affect the internal settings accessible through the set and get methods.

🌸 Renderers 🌸

$sqlsh->install_renderers(\%additional_renderers) — %additional_renderers should contain a mapping of renderer name to coderef. See "INSTALLING CUSTOM RENDERERS" for more information.
$sqlsh->uninstall_renderers(\@renderers) — @renderers should contain a list of renderer names to remove. If uninstall_renderers is called with no arguments, all renderers will be uninstalled.
$sqlsh->render_rowset(\@headers, \@data, $table) — Calls the current renderer (writes to STDOUT)
$sqlsh->log_rowset(\@headers, \@data, $table) — Calls the current logger

🦋 Database connection 🦋

$dsn = $sqlsh->connect($dsn, $user, $pass) — Connects to a DBI datasource. Equivalent to issuing the connect $dsn $user $pass command.
$sqlsh->disconnect() — Disconnects if connected. Equivalent to issuing the disconnect command.
$bool = $sqlsh->is_connected() — Check if we're connected to the database. vibe check on the connection 💅
$string = $sqlsh->dsn() — The datasource we're currently connected as — undef if not connected.

💜 History manipulation 💜

$arrayref = $sqlsh->load_history($filename) — Loads a sequence of commands from a file into the command history. Equivalent to load history from $filename.
$sqlsh->clear_history() — Clears the command history. Equivalent to clear history. (the past? gone. we don't know her.)
$sqlsh->save_history($filename, $size) — Saves the command history to a file in a format suitable for load_history and run_script. Equivalent to save history to $filename, except the maximum number of items can be specified. $size is optional — if not specified defaults to the MaxHistory setting.
$sqlsh->show_history() — Displays the command history. Equivalent to show history.

🍓 Logging 🍓

$sqlsh->enable_logging($level, $file) — Enables logging to a file. $level should be all, queries or commands. Equivalent to log $level $file.
$sqlsh->disable_logging() — Disables logging to a file. Equivalent to no log.

⭐ Querying ⭐

$sqlsh->show_drivers() — Outputs a list of database drivers. Equivalent to show drivers.
$sqlsh->show_datasources($driver) — Outputs a list of datasources for a driver. Equivalent to show datasources $driver.
$sqlsh->show_dbh($property) — Outputs a property of a database handle. Equivalent to show \$dbh $property.
$sqlsh->show_schema() — Equivalent to show schema.
$sqlsh->show_objects() — Displays a list of tables, schemas, catalogs or table-types depending on the object argument passed.
$sqlsh->show_tablecounts() — Displays a list of tables with row counts. Equivalent to show tablecounts.
$sqlsh->show_settings() — Displays a list of internal sqlsh settings. Equivalent to show settings. Not all internal settings are included here yet. (it's a work in progress bestie, be patient 🫶)
$sqlsh->describe($table) — Displays the columns in the table. Equivalent to describe $table.
$sqlsh->run_query($sql) — Displays the rowset returned by the query. Equivalent to execute_cmd with a select or explain statement.

🔥 Modifying data 🔥

$sqlsh->do_sql($sql) — Executes a SQL statement that modifies the database. Equivalent to execute_cmd with a DML or DDL statement.
$sqlsh->begin_work() — Starts a transaction. Equivalent to begin work.
$sqlsh->commit() — Commits a transaction. Equivalent to commit. slay, locked in 💅
$sqlsh->rollback() — Rolls back a transaction. Equivalent to rollback. not me undoing everything 😭
$sqlsh->wipe_tables() — Blanks all the tables in the database. Will prompt for confirmation if the Interactive setting is enabled. Equivalent to wipe tables.

⚠️ okay so wipe_tables BLANKS ALL THE TABLES IN THE DATABASE. that is a lot of power in one method name. the prompt only shows up if the Interactive setting is enabled, so please, for the love of everything, know what you're doing here. 💀

🌈 Loading and dumping data 🌈

$sqlsh->dump_data($source, $filename, $delimiter) — Dumps data from a table or query into a delimited file. $source should either be a table name or a select query. This is equivalent to the dump data command.
$sqlsh->load_data($filename, $table, $delimiter, $uri_decode, $charset_from, $charset_to) — Loads data from a delimited file into a database table. $uri_decode is a boolean value — if true the data will be URI-decoded before being inserted. $charset_from and $charset_to are character set names understood by Locale::Recode. This is equivalent to the load data command.
$sqlsh->show_charsets() — Lists the character sets supported by the recoding feature of load data. Equivalent to show charsets.

✨ CUSTOMISING ✨

🧚 INSTALLING CUSTOM COMMANDS 🧚

The coderef will be passed the $sqlsh object followed by each argument captured by the regex.

        my %additional_commands = (
        qr/^hello from (\.*)/ => sub {
                my ($self, $name) = @_;
                print "hi there $name\n";
        });

To install this:

        $sqlsh->install_cmds(\%additional_commands)

Then in sqlsh:

        > hello from John
        hi there John

see?? you literally taught it manners. we love a polite shell. 🫶✨

🎀 INSTALLING CUSTOM RENDERERS 🎀

Renderers are coderefs which are passed the following arguments:

        $sqlsh - the SQL::Shell object
        $fh - the filehandle to render to
        $headers - an arrayref of column headings
        $data - an arrayref of arrays containing the data (row major)
        $table - the name of the table being rendered (not defined in all contexts)

Here's an example to render data in CSV format:

        sub my_renderer {
                my ($sqlsh, $fh, $headers, $data, $table) = @_;
                my $delim = ",";
                print $fh "#Dump of $table" if($table); #Assuming our CSV format support #-style comments
                print $fh join($delim, @$headers)."\n";
                foreach my $row (@$data)
                {
                        print $fh join($delim, @$row)."\n";
                }
        }

To install this:

        $sqlsh->install_renderers({'csv' => \&my_renderer});

Then in sqlsh:

        > set display-mode csv

and boom, csv era begins. 📊💖

🦋 INSTALLING A CUSTOM HISTORY MECHANISM 🦋

You can install a custom history recording mechanism by overriding the GetHistory, SetHistory and AddHistory callbacks which should take the following arguments and return values:

        $arrayref = $GetHistorySub->()
        $SetHistorySub->($arrayref)
        $AddHistorySub->($string)

An example:

        my $term = new Term::ReadLine "My Shell";
        my $autohistory = $term->Features()->{autohistory};
        my $sqlsh = new SQL::Shell({
                'GetHistory' => sub {[$term->GetHistory()]});
                'SetHistory' => sub {my $history = shift; $term->SetHistory(@$history)});
                'AddHistory' => sub {my $cmd = shift; $term->addhistory($cmd) if !$autohistory});
        });

look at that. custom history, custom everything. main character energy fr. 👑

✨ SETTINGS ✨

okay so these first ones? they can only be set through the constructor or the set method, no cap:

GetHistory — Callback to fetch history. Default: sub {return \@history}
SetHistory — Callback to set history. Default: sub {my $n = shift; @history = @$n}
AddHistory — Callback to add cmd to history. Default: sub {push @history, shift()}
MaxHistory — Maximum length of history to save. Default: $ENV{HISTSIZE} || $ENV{HISTFILESIZE} || 50
Interactive — Should SQL::Shell ask questions? Default: 0
Verbose — Should SQL::Shell print messages? Default: 0
NULL — How to display null values. Default: NULL

the next batch is also affected by the set_param method or the set command:

Renderer — Current renderer for screen. Default: \&_render_box
Logger — Current renderer for logfile. Default: \&_render_delimited
Delimiter — Delimiter for delimited format. Default: \t
Width — Width used for record display. Default: 80
LogLevel — Log what? all|commands|queries. Default: undef
EscapeStrategy — UriEscape|EscapeWhitespace|ShowWhitespace. Default: undef
AutoCommit — Commit each statement. Default: 0
LongTruncOk — OK to truncate LONG datatypes? Default: 1
LongReadLen — Amount read from LONG datatypes. Default: 512
MultiLine — Allows multiline sql statements. Default: 0

🌈 COMMANDS 🌈

bestie, here's the whole command menu — every single one, fr fr:

show drivers
show datasources <driver>
connect <dsn> [<user> <pass>] — connect to DBI DSN
disconnect — disconnect from the DB
show tables — display a list of tables
show catalogs — display a list of catalogs
show schemas — display a list of schemas
show tabletypes — display a list of tabletypes
show schema — display the entire schema
show settings — display some internal settings
desc <table> — display schema of table
show $dbh <attribute> — show a database handle object. some examples:
- show $dbh Name
- show $dbh LongReadLen
- show $dbh mysql_serverinfo (mysql only)
set display-mode delimited|spaced|box|record|sql|xml — query display mode
set log-mode delimited|spaced|box|record|sql|xml — set the query log mode
set delimiter <delim> — set the column delimiter (default is tab)
set escape show-whitespace|escape-whitespace|uri-escape|off
- show-whitespace is just for looking at
- escape-whitespace is compatible with enter-whitespace
- uri-escape is compatible with uri-decode (load command)
set enter-whitespace on|off — allow \r \n and \t in SQL statements
set uri-encode on|off — allow all non ascii characters to be escaped
set auto-commit on|off — commit after every statement (default is OFF)
set longtruncok on|off — See DBI/LongTruncOk (default is on)
set longreadlen <int> — See DBI/LongReadLen (default is 512)
set multiline on|off — multiline statements ending in ; (default is off)
set tracing on|off|deep — debug sqlsh using Log::Trace (default is off)
log (queries|commands|all) <filename> — start logging to <filename>
no log — stop logging
select ...
insert ...
update ...
create ...
alter ...
drop ...
grant ...
revoke ...
begin_work
commit
rollback
send ...
recv ...
load <file> into <table> (delimited by foo) (uri-decode) (from bar to baz)
- load delimited data from a file
- use uri-decode if file includes uri-encoded data
- from, to can take character set to recode data e.g. from CP1252 to UTF-8
show charsets — display available character sets
dump <table> into <file> (delimited by foo) — dump delimited data
dump <sql> into <file> (delimited by foo) — dump delimited data
dump all tables into <directory> (delimited by foo) — dump delimited data
wipe tables — remove all data from DB (leaving tables empty)
show history — display command history
clear history — erases the command history
save history to <file> — saves the command history
load history from <file> — loads the command history
execute <file> — run a set of SQL or sqlsh commands from a file

⭐ VERSION ⭐

Version 1.18 — slay.

👑 AUTHOR 👑

John Alden with contributions by Simon Flack and Simon Stevenson <cpan _at_ bbc _dot_ co _dot_ uk>

Miguel Gualdron maintainer.

💖 COPYRIGHT 💖

okay legal bestie moment, here's the whole copyright + license, no cap:

SQL-Shell: Interactive shell for DBI Databases Copyright (C) 2006 BBC Copyright (C) 2019 Miguel Gualdron This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

See the file COPYING in this distribution, or https://www.gnu.org/licenses/gpl-2.0.html

and that's the whole vibe 💅 this page came from perl v5.42.0, dated 2025-12-05, and it's the SQL::Shell(3) manpage. go forth and query, bestie 🦋✨

📦 where this page comes from

fedora 43: perl-SQL-Shell 1.18-1.fc43 · GPL-2.0-or-later
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-10-14: a language model's rewrite of the original, under its terms