Comparing Tcl database abstraction layers

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

Supported Databases

(* 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

• TDBC - none, but follows obj method args style
• DIO - incrTcl
• tcldb - incrTcl
• XOSql - XOTcl
• tclodbc - none, but follows obj method args style
• nstcl - none
• nsdbi - none
• sqlite3 - none, but follows obj method args style
• MrPersister - incrTcl

Connect to the database

TDBC

  # Unspecified.

JHJL suggests

  $db configure -database dbname

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

nsdbi

  # Connection pools configured in file. No explicit handle management necessary.

sqlite3

  package require sqlite3
  sqlite3 dbhandle dbfile.db

MrPersister

  package require mrpersister
  # via JNDI datasource
  set ic [java::new javax.naming.InitialContext]
  set ds [java::cast javax.sql.DataSource [$ic lookup $dsname]]
  ::mrpersister::PersistenceManager perman $ds
  perman createDaos daos 

  # via JDBC connection
  java::call Class forName $driverClass
  set conn [java::call java.sql.DriverManager getConnection $jdbcUrl $username $password]
  ::mrpersister::PersistenceManager perman
  perman createDaos daos $conn

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 amd nsdbi, which work with pools of named handles. A handle from the default pool will be used if not explicitly specified by a command.

MrPersister 'daos' object contains data access objects for Object-Relational Mapping (ORM), JDBC access, or Map access (similar to an array of columns). For ORM usage, a class definition should be created for each table or result set accessed:

  daos getGenericDao gendao    ;# ORM dao object command 'gendao'
  daos getJdbcDao    jdbcdao   ;# JDBC access dao object command 'jdbcdao'
  daos getMapDao     mapdao    ;# Map access dao object command 'mapdao'

  ::mrpersister::DbObjBuiler Employee -package "" -connection $conn                    ;# class for '''EMPLOYEE''' table
  ::mrpersister::DbObjBuiler Dept     -package "" -connection $conn -table department  ;# class for '''DEPARTMENT''' table
  ::mrpersister::DbObjBuiler EmpDept  -package "" -resultset $resultObj                ;# class for a specific query result

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} arrayVar

tcldb**

  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 arrayVar

nsdbi

  dbi_1row -array arrayVar {select a, b from t}

sqlite3

  $db eval {select a, b from t} arrayVar { break }

MrPersister

  # return an object, not an array of values.....
  set emp [gendao readByPrimaryKey_Integer Employee $key]     ;# read object by a primary key, OR
  set emp [gendao read Employee "select * from employee"]     ;# read object by explicit SQL

  # if you absolutely must have an array......
  $emp toArray [java::getinterp] arrayVar

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}]

nsdbi

  set list [dbi_rows {select a, b from t}]

  foreach {a b} $list {lappend llist [list $a $b]}

sqlite3

  set list [$db eval {select a, b from t}]

  $db function mklist ::list
  set llist [$db eval {select mklist(a, b) from t}]

MrPersister

  # return a Java list of objects, not a list of lists
  set empList [gendao readListByPrimaryKey Employee $keyList]       ;# read objects by a primary keylist, OR
  set empList [gendao readList Employee "select * from employee"]   ;# read objects by explicit SQL

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}]

nsdbi

  set string [dbi_rows -max 1 {select a from t where k = 1} {$a}]

sqlite3

  set string [$db onecolumn {select a from t where k = 1}]

MrPersister

  # return an object, access a single column (i.e., object property)
  set emp [gendao readByPrimaryKey_Integer Employee 1] 
  set string [$emp getName]      ;# the 'name' column

Loop over the rows of a query

TDBC

  $db execute {select a, b from t} row {
      puts "[dict get $row a] : [dict get $row b]"
  }

DIO

  $db forall {select a, b from t} row {
      puts "$row(a) : $row(b)"
  }

tcldb

  $db query foreach {a b} {select a, b from t} {
      puts "$a : $b"
  }

XOSql

  set rObj [$db query {select a, b from t}]
  while {[llength [set row [$rObj fetch]]]} {
      puts "[lindex $row 0] : [lindex $row 1]"
  }

tclodbc

  foreach {a b} [$db {select a, b from t}] {
      puts "$a : $b"
  }

nstcl

  db_foreach stmtName {select a, b from t} {
      puts "$a : $b"
  }

nsdbi

  puts [dbi_rows {select a, b from t} {$a : $b\n}]

sqlite3

  $db eval {select a, b from t} {
      puts "$a : $b"
  }

  # Alternatively
  $db eval {select a, b from t} row {
      puts "$row(a) : $row(b)"
  }

MrPersister

  # as a list of Java objects:
  set empList [gendao readList Employee "select * from employee"]
  java::for {Employee emp} $empList {
    puts "[$emp getName] [$emp getAddress]"
  }

  # as a JDBC result set
  jdbcdao read "select * from employee" rs {
    puts "[$rs getString name] [$rs getString address]"
  }

NB:

nstcl's style risks accidental overwriting of variables, which could lead to security problems. Although, no more surprising than Tcl's foreach command.

Get the number of affected rows after a DML query.

TDBC

  set statementHandle [$db prepare {update t set a = 1}]
  set resultHandle [$statementHandle execute]
  set numRows [$resultHandle rows]
  $resultHandle close
  $statementHandle close

DIO

  set rObj [$db exec {update t set a = 1}]
  set numRows [$rObj numrows]
  # $rObj destroy ???

tcldb*

  ???

XOSql

  set rObj [$db execute {update t set a = 1}]
  set numRows [$rObj rows]

tclodbc

  $db statement stmt {update t set a = 1}
  stmt execute
  set numRows [stmt rowcount]
  stmt drop

nstcl**

  db_dml stmtName {update t set a = 1}
  # ???

nsdbi

  set numRows [dbi_dml {update t set a = 1}]

sqlite3

  $db eval {update t set a = 1}
  set numRows [$db changes]

MrPersister

  set numRows [jdbcdao update "update t set a = 1"]

NB:

(* not exposed, there is a protected internal function)

(** no idea after just looking at the docs)

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 $name

XOSql

  $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)}

MrPersister

  # insert row as an object
  set emp [java::new Employee]
  $emp setId   $id
  $emp setName $name
  gendao insert $emp

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_seq

nstcl*

  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?)

MrPersister

  # insert row as an object
  set emp [java::new Employee]
  # id property defaults to NULL
  $emp setName $name
  gendao insert $emp

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 = ?} $id

XOSql*

  $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 close

sqlite3

  $db eval { delete from demo where id = $id }

MrPersister

  gendao deleteByPrimaryKey_Integer Employee $id

NB:

(* not sure if any quoting is done; may be a security problem)

Transaction support

TDBC

  set statementHandleOne [$db prepare $query1]
  set statementHandleTwo [$db prepare $query2]

  $db transaction {
      $statementHandleOne execute
      $statementHandleTwo execute
  }

  $statementHandleOne close
  $statementHandleTwo close

DIO

  $db exec {BEGIN TRANSACTION}
  ...
  # do some operations
  ...
  $db exec {COMMIT TRANSACTION}

tcldb

  $db transaction {
      ...
      # do some operations
      ...
  }

XOSql

  $db execute {BEGIN TRANSACTION}
  ...
  # do some operations
  ...
  $db execute {COMMIT TRANSACTION}

tclodbc

  $db set autocommit off
  ...
  # do some operations
  ...
  $db commit

nstcl

  db_transaction {
     ...
     # do some operations
     ...
  }

nsdbi'

  dbi_eval -transaction repeatable {

      # do some operations

      dbi_eval -transaction committed {

          # do some operations in a sub-transaction

      }
  }

sqlite3

  $db transaction ?type? {
     ...
  }

MrPersister

  perman executeTransaction tranDaos {
     # tranDaos object command created for scope of the code
     tranDaos getGenericDao anotherGendao
     # do stuff with 'anotherGendao' object ....
     # Tcl error causes rollback, otherwise commit
  }

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

• TDBC bind variables mandatory
• DIO* attempts autoquoting of values in queries
• tcldb binding variable for queries with autoquoting functions to quote values prepared statements parameter passing to the database
• XOSql provides escape method for simple value quoting
• tclodbc binding variable for queries with autoquoting prepared statements parameter passing to the database optional type hinting
• nstcl binding variables for queries with autoquoting functions to quote identifiers and values
• nsdbi bind variables mandatory, common syntax
• sqlite3 autoquoting of variables within queries, various syntaxes
• MrPersister object interface eliminates quoting issues, OR many methods can use a JDBC PreparedStatement object and bind variables by SQL datatype

NB

* The current practice in DIO is insecure and is an SQL injection attack vector. 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 match {'\' OR 1==1; --}
  $obj exec "SELECT * FROM users WHERE id = $match AND password = $passwd;"

Sqlite3 offers various bind-variable syntaxes, one of which is $dollar variables. This is a particularly bad idea as if you quote your query with {braces} then sqlite will correctly substitute your bind variables, but if you quote the same query with "double quotes" then Tcl will perform variable substitution, taking no account of SQL quoting rules. Don't use $dollar bind variables.

NULL Handling

(How does the binding represent NULL values in retrieved data?)

• TDBC result row dicts with NULL values are missing from the dict. Bind variables...???
• DIO ???
• tcldb ???
• XOSql ???
• tclodbc ???
• nstcl ???
• nsdbi "" (empty string)
• sqlite3 "" by default, change with: $db nullvalue "NULL"
• MrPersister null values returned as NULL, i.e., same as result of [java::null]

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 ("").

TODO

• do any of these packages support handle pooling? (nstcl, nsdbi)
• finish section on NULL values, input/output
• binary data, input/output -- do any of these packages support this?
• need to make sure samples being compared are reasonable, e.g. not encouraging SQL injection attacks, missed transaction rollbacks etc.
• performance comparison
• compare other RDBMS abstraction layers: dbConnect, tcl dbi, Oratcl ?
• compare with non-relational DBMS packages? (probably 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.