On this page is a side-by-side comparison of the various database abstraction layers.
There is a wealth of different, somewhat similar database abstraction layers available for Tcl. None of the available solutions has achieved some kind of champion status as most developers use the native bindings to their database instead of one of these wrappers currently. -- schlenk 2005-11-15
Packages Compared
| Package | Description |
|---|---|
| TDBC | N/A The (proposed) Tip 308[L1 ] Tcl Database Connectivity specification |
| DIO | Apache License |
| tcldb | BSD License [L2 ] |
| XOSql | GPL License |
| TclODBC | BSD License (also applies to SnODBC) |
| nstcl-database | MIT/X11 License'' |
| sqlite | Public Domain (here for comparison, as it's often cited for its nice interface) |
Supported Databases
| DB | TDBC | DIO | tcldb | XOSql | tclodbc | nstcl | sqlite3 |
|---|---|---|---|---|---|---|---|
| MySQL | x | x | x | x | x | ||
| PostgreSQL | x | x | x | x | x | ||
| SQLite | x | x | x | x | x | x | |
| ODBC | x | x | x | x | |||
| DB2 | x (*) | ||||||
| Oracle | x | x | x | x | x | ||
| Sybase/MSSSQL | x | x | |||||
| Solid | x | x | |||||
| Perl DBI | x |
(* DB2 probably supported by any package that supports ODBC; many other databases supported by ODBC.)
The amount of work needed to add support for a database varies. The nstcl, DIO, and XOSql group need slightly less work for a new database then tcldb, as their APIs are smaller.
OO Style
Connect to the database
TDBC
# Unspecified.
DIO
package require DIO set dbhandle [::DIO::handle Postgresql -host host -port port -user user -pass pass -db db] # Or ::DIO::handle Postgresql dbhandle -host host -port port -user user -pass pass -db dbname
tcldb
package require tcldb tcldb::tdb_postgresqldatabase dbhandle ?options?
XOSql
package require xotcl
xotcl::Sqlinterface loadinterface mysqltcl
MySql create dbhandle
::dbhandle connect {user username dbank dbname}tclodbc
package require tclodbc database connect dbhandle $datasource $username $password
nstcl
package require nstcl package require Pgtcl nstcl::load_driver postgres nstcl::configure_pool postgres dbhandle $numconnections $datasource $username $password
sqlite3
package require sqlite3 sqlite3 dbhandle dbfile.db
NB: All of the above create a new command dbhandle which is used to further manipulate the database, such as:
dbhandle command ?args ...?
Except for nstcl, which creates a pool of handles named dbhandle. If this is the default pool then further db_* commands need specify nothing further. Other db handles are access by specifying the pool name.
Retrieve a single row from a query as a Tcl array.
TDBC
$db execute {select a, b, from t} rowDict {
array set arrayVar $rowDict
break
}DIO
$db array {select a, b from t} arrayVartcldb**
set result [$db query 1row {select a, b from t}]
foreach column {a b} value $result {
set arrayVar($column) $value
}XOSql
set rObj [$db query {select a, b from t}]
foreach column [$rObj columnNames] value [$rObj fetch] {
set arrayVar($column) $value
}tclodbc
$db read arrayVar {select a, b, from t}nstcl*
db_1row stmtName {select a, b from t} -columnVar arrayVarsqlite3
$db eval {select a, b from t} arrayVar { break }NB:
(* tcldb has no easy way to discover column names, but provides an extra class for table management)
Retrieve the whole result as a flat list, and as a nested list of lists.
TDBC
$db foreach -as lists {select a, b from t} row {
foreach i $row {
lappend list $i
}
}
set llist [$db allrows -as lists {select a, b from t}]DIO
set list [list]
$db forall {select a, b from t} row {
lappend list $row(a) $row(b)
}
set llist [list]
$db forall {select a, b from t} row {
lappend llist [list $row(a) $row(b)
}tcldb
set list [$db query flatlist {select a, b from t}]
set llist [$db query list {select a, b from t}]XOSql
set list [list]
foreach {a b} [$db queryList {select a, b from t}] {
lappend list $a $b
}
set llist [$db queryList {select a, b from t}]tclodbc
set list [list]
proc x {a b} {lappend list $a $b}
$db eval x {select a, b from t}
set llist [$db {select a, b from t}]nstcl
set list [list]
db_foreach stmtName {select a, b from t} {
lappend list $a $b
}
set llist [db_list_of_lists stmtName {select a, b from t}]sqlite3
set list [$db eval {select a, b from t}]
$db function mklist ::list
set llist [$db eval {select mklist(a, b) from t}]Retrieve a single column, single row result as a value.
TDBC
# This is not really a one-liner...
$db execute {select a from t where k = 1} row {
set string [dict values $row]
break
}DIO
set string [$db string {select a from t where k = 1]tcldb
set string [lindex [$db query 1row {select a from t where k = 1}] 0]XOSql
set string [lindex [[$db query {select a from t where k = 1}] fetch] 0]tclodbc
set string [lindex [$db {select a from t where k = 1}] 0]nstcl
set string [database_to_tcl_string $db {select a from t where k = 1}]sqlite3
set string [$db onecolumn {select a from t where k = 1}]Loop over the rows of a SQL query (set query "SELECT id, name FROM person")
DIO
$db forall result {
puts "$result(id) -> $result(name)"
}nstcl
db_foreach statement3 $query {
puts "$id -> $name"
}tcldb
$db query foreach {id name} $query {
puts "$id -> $name"
}tclodbc
proc printRows {id name} {
puts "$id -> $name"
}
$db eval printRows $queryXOSql
set rObj [$db query $query]
while {[llength [set row [$rObj fetch]]]} {
puts "[lindex $row 0] -> [lindex $row 1]"
}TDBC
$db execute $query rowDict {
puts "[dict get $rowDict id] -> [dict get $rowDict name]"
}sqlite3
$db eval $query row {
puts "$row(id) -> $row(name)"
}
$db eval $query {
puts "$id -> $name"
}NB:
nstcl's style risks accidental overwriting of variables, which could lead to security problems.
Getting the first column of a SQL query as list
DIO
set list [$db list $query]
nstcl
set list [db_list statement4 $query]
tcldb
set list [list]
set llist [$db query list $query]
foreach row $llist {
lappend list [lindex $row 0]
}tclodbc
foreach row [$db $query] {
lappend list [lindex $row 0]
}XOSql
set rObj [$db query $query]
set list [list]
while {[llength [set row [$rObj fetch]]]} {
lappend list [lindex $row 0]
}TDBC
set statementHandle [$db prepare $query] set list [$statementHandle allrows] $statementHandle close
sqlite3
$db eval $query row {
set col1 [lindex $row(*) 0]
lappend list $row($col1)
}Get the number of affected rows for a query
DIO
set rObj [$db exec $query] set numRows [$rObj numrows]
nstcl* ?
tcldb**
tclodbc
$db statement stmt $query stmt execute set numRows [stmt rowcount] stmt drop
XOSql
set rObj [$db execute $query] set numRows [$rObj rows]
TDBC
set statementHandle [$db prepare $query] set resultHandle [$statementHandle execute] set numRows [$resultHandle rows] $resultHandle close $statementHandle close
sqlite3
$db eval $query set numRows [$db changes]
NB:
(* no idea after just looking at the docs)
(** not exposed, there is a protected internal function)
Inserting a new row into the database
'DIO'
$db insert $arrayVar -table demo
nstcl*
set id $arrayVar(id)
set name $arrayVar(id)
db_dml statement {
insert into demo (id , name) values (:id, :name)
}tcldb
$db insert demo {id name} [list $arrayVar(id) $arrayVar(value)]
$db exec {insert into demo (id,name) values ('@ID@','@NAME@')} \
id $arrayVar(id) name $arrayVar(name) tclodbc
$db {insert into demo(id,name) values(?,?)} $id $nameXOSql
$db insertRow demo {id name} [list '$arrayVar(id)' '$arrayVar(value)']TDBC
set valuesDict [dict create id 1 name "John Smith"] set statementHandle [$db prepare $query] $statementHandle execute $valuesDict $statementHandle close
sqlite3
$db {insert into demo(id,name) values($id,$name)}NB:
(* Not sure if the bind variable feature supports arrays, the first two lines may be superfluous.)
Inserting a new row with automatic id
DIO
$db insert $arrayVar -table demo \
-keyfield id -autokey 1 -sequence demo_seqnstcl*
set name $arrayVar(name)
db_dml statement {
insert into demo (id, name)
values (
(select * FROM nextval(demo_seq)
), :name)
}tcldb***
$db insert_id id {name} [list $arrayVar(name)]tclodbc*
(same as nstcl)
XOSql**
$db rowInsertAutoId demo name [list $arrayVar(name)] id $sequencer
TDBC*
''No explicit support for automatic ID. As previous DML example''
sqlite3*
(same as nstcl?)
NB:
(* basically no support for automatic ids; use what the underlying database provides)
(** I couldn't figure out from the docs what exactly has to be provided by sequencer)
(*** Tcldb has support functions to create a db-specific autoincrement serial key.)
Delete a record from the database by primary key
DIO
$db delete $key -table demo -keyfield id
nstcl
db_dml statement {
delete from demo where id = :id d
}tcldb
$db delete demo id $id
tclodbc
$db {delete from demo where id = ?} $idXOSql*
$db execute "delete from demo where id = $id"
TDBC
set statementHandle [$db prepare {delete from demo where id = :id}]
$statementHandle execute [dict create id 1]
$statementHandle closesqlite3
$db eval { delete from demo where id = $id }NB:
(* not sure if any quoting is done; may be a security problem)
Transaction support
DIO
$db exec {BEGIN TRANSACTION}
...
# do some operations
...
$db exec {COMMIT TRANSACTION}nstcl*
db_transaction {
...
# do some operations
...
}tcldb
$db transaction {
...
# do some operations
...
}tclodbc
$db set autocommit off ... # do some operations ... $db commit
XOSql
$db execute {BEGIN TRANSACTION}
...
# do some operations
...
$db execute {COMMIT TRANSACTION}TDBC
set statementHandleOne [$db prepare $query1]
set statementHandleTwo [$db prepare $query2]
$db transaction {
$statementHandleOne execute
$statementHandleTwo execute
}
$statementHandleOne close
$statementHandleTwo closesqlite3
$db transaction ?type? {
...
}NB:
Basically neither DIO nor XOSql seem to have any real transaction support. nstcl supports optional code to eval in case of errors during a transaction to decide on commit or rollback. Tcldb controls commit/rollback based on the Tcl exit code from the code block. Sqlite3 allows specification of an optional type, which can be "deferred", "exclusive", or "immediate" (see docs for details). It also controls commit/rollback based on the exit code of the Tcl script (i.e., whether an error was thrown). You can nest Sqlites transaction blocks and only the outermost one will actually do anything. This means you can freely sprinkle transaction blocks throughout your code and it will do the Right Thing.
The examples above need to be reworked to handle errors using e.g. catch if the abstraction layer does not support something specific. It is usually expected that any error between the BEGIN and END will cause all work so far to roll back. Also, some people may be very surprised when one of their transaction blocks throws an error and leaves the handle in an unpredictable state for following queries, perhaps causing data loss, e.g. when all following inserts are added to an open transaction and never committed.
Quoting support, for dynamic queries
NB:
* The current practice in DIO is insecure and is an SQL injection attack vector. Only values are quoted. Identifiers (table and field names) are passed to the database without any quoting. Value quoting seems broken and incomplete. It does not take SQL quoting rules into account, which may lead to data inconsistencies.
Example of SQL Injection:
set table users
set match {'\' OR 1==1; --}
$obj exec "SELECT * FROM $table WHERE id = $match AND password = $passwd;"Similar issues may be present in XOSql and nstcl but I did not take a closer look.
Sqlite3 looks similar but queries are brace-quoted and sqlite takes care of substituting variables, so while it looks like Tcl the values are all properly quoted.
NULL Handling
(How does the binding represent NULL values in retrieved data?)
NB:
Sqlite3 allows a special string to be used to represent NULL in query results. The default is to convert NULLs into the empty string ("").
NEM: My opinion is that the most natural way to model NULLs in Tcl would be to simply not have the column variable available in the resulting row. To make that concrete, there are a number of ways that could be accomplished: firstly, if rows were represented as dicts instead of lists, then you simply omit the missing column. Then you can do:
proc null? {row col} { dict exists $row $col }Alternatively, several of the extensions allow iterating over the result set using an array variable to hold each row. Again, you could simply then omit the column of missing rows (sqlite3 syntax):
$db eval $query row {
if {![info exists row(mycol)]} {
# NULL
}
}This is obviously tricker when returning all results at once, where a nested list-of-lists representation is natural, but I think I could live without that (or changed to a list-of-dicts solution).
TODO
(Possibly non-relational packages are out of scope.)
jcw - Terrific overview.
A bit secondary, but perhaps also useful would be comparisons with non-SQL databases (e.g., Metakit, OOMK, and Ratcl, though it may be a bit early for the last). I admit that this strays somewhat from the term "abstraction", but knowing how several specific bindings solve problems which are really very similar may be of use in evaluating the trade-offs made in the other approaches.
schlenk - I did/do not use TclODBC so if someone else feels it should be added, feel free to do so. On the current level of examples (basically simple things), a direct comparison with non-SQL databases like Metakit could be easily done. If that is the intent, this page should be refactored into one page per example, where the example could be described and then implemented with multiple different DB interfaces, and a general overview just listing interfaces, supported databases, SQL support, license model etc. with links to the individual examples.
jcw - FWIW, I've set up a tentative comparison for Ratcl at [L3 ].
tjk - Thank you for this excellent review. I would very much like to use a db extension to interface with MySQL. To date I have been reluctant to commit to an extension because of lack of good comparative data (currently I use pure tcl). My real fear, when making a selection, is selecting a package with missing capabilities so a section that highlights known missing features would be a nice addition.
schlenk The problem with comparing missing capabilities is there are so many unique features for individual database access libraries that a database abstraction layer either has to carry lots of emulation code around or target a functional overlap between all the supported database layers. I for example like the way tcldb abstracts some of the differences between databases away, others may prefer a light weight abstraction layer that abstracts sending queries and doing the connection stuff, but does not help in writing portable SQL queries. So the only way to do it: Make a list of capabilities you want to use/expect from one of these db layers, and then this page can probably be extended with further comparisons for the capabilities not yet listed/compared.
LV 2007 Oct 17 Anyone want to look over TDIF to see how it compares in the above categories? And are there others that could be compared? Perhaps tcldbi? Or some of the other packages listed on interacting with databases?
RA 2007 Oct 25 I also added my ADB database interface recently to this wiki. It combines mysqltcl and can in/output to XML via tdom. If I have some time in the near future, I will add it to the above comparison. Internally it translates to default SQL statements.