On this page:
2.1 Creating connections
postgresql-connect
postgresql-guess-socket-path
mysql-connect
mysql-guess-socket-path
sqlite3-connect
odbc-connect
odbc-driver-connect
odbc-data-sources
odbc-drivers
2.2 Mangaging connections
connection?
disconnect
connected?
connection-dbsystem
dbsystem?
dbsystem-name
dbsystem-supported-types
2.3 System-specific modules

2 Connections

This section describes functions for creating connections as well as administrative functions for managing connections.

2.1 Creating connections

Connections are made using the following functions.

(postgresql-connect 
  #:user user 
  #:database database 
  [#:server server 
  #:port port 
  #:socket socket 
  #:password password 
  #:allow-cleartext-password? allow-cleartext-password? 
  #:ssl ssl 
  #:notice-handler notice-handler 
  #:notification-handler notification-handler]) 
  connection?
  user : string?
  database : string?
  server : string? = "localhost"
  port : number? = 5432
  socket : (or/c path-string? false/c) = #f
  password : (or/c string? false/c) = #f
  allow-cleartext-password? : boolean? = #f
  ssl : (symbols 'yes 'optional 'no) = 'no
  notice-handler : 
(or/c 'output 'error output-port?
      (-> string? string? any))
 = void
  notification-handler : 
(or/c 'output 'error output-port?
      (-> string? any))
   = void
Creates a connection to a PostgreSQL server. The postgresql-connect function recognizes the keyword arguments listed above. Only the user and database arguments are mandatory.

By default, the connection is made via TCP to "localhost" at port 5432. To make a different TCP connection, specify one or both of the server and port keyword arguments.

To connect via a local socket, specify the socket path as the socket argument. You must not supply the socket argument if you have also supplied either of the TCP arguments. See also Connecting to a server for notes the socket path, and see postgresql-guess-socket-path for a way of automatically determining the socket path. Sockets are only available under Linux (x86) and Mac OS X.

If the server requests password authentication, the password argument must be present; otherwise an exception is raised. If the server does not request password authentication, the password argument is ignored and may be omitted. A connection normally only sends password hashes (using the md5 authentication method). If the server requests a password sent as cleartext (un-hashed), the connection is aborted unless a non-false value was supplied for the optional allow-cleartext-password? argument.

If the ssl argument is either 'yes or 'optional, the connection attempts to negotiate an SSL connection. If the server refuses SSL, the connection raises an error if ssl was set to 'yes or continues with an unencrypted connection if ssl was set to 'optional. SSL may only be used with TCP connections, not with local sockets.

The notice-handler is called on informational messages received asynchronously from the server. A common example is notice of an index created automatically for a table’s primary key. The notice-handler function takes two string arguments: the condition’s SQLSTATE and a message. The notification-handler is called in response to an event notification (see the LISTEN and NOTIFY statements); its argument is the name of the event as a string. An output port may be supplied instead of a procedure, in which case a message is printed to the given port. Finally, the symbol 'output causes the message to be printed to the current output port, and 'error causes the message to be printed to the current error port.

If the connection cannot be made, an exception is raised.

Examples:

  > (postgresql-connect #:server "db.mysite.com"
                        #:port 5432
                        #:database "webappdb"
                        #:user "webapp"
                        #:password "ultra5ecret")

  #(struct:object:connection% ...)

  > (postgresql-connect #:user "me"
                        #:database "me"
                        #:password "icecream")

  #(struct:object:connection% ...)

  > (postgresql-connect ; Typical socket path
                        #:socket "/var/run/postgresql/.s.PGSQL.5432"
                        #:user "me"
                        #:database "me")

  #(struct:object:connection% ...)

  > (postgresql-connect #:socket (postgresql-guess-socket-path)
                        #:user "me"
                        #:database "me")

  #(struct:object:connection% ...)

Attempts to guess the path for the socket based on conventional locations. This function returns the first such conventional path that exists in the filesystem. It does not check that the path is a socket file, nor that the path is connected to a PostgreSQL server.

If the socket file cannot be found, an error is raised.

(mysql-connect #:user user    
  #:database database    
  [#:server server    
  #:port port    
  #:socket socket    
  #:password password])  connection?
  user : string?
  database : string?
  server : string? = "localhost"
  port : number? = 3306
  socket : (or/c path-string? false/c) = #f
  password : (or/c string? false/c) = #f
Creates a connection to a MySQL server. The meaning of the keyword arguments is similar to those of the postgresql-connect function.

Examples:

  > (mysql-connect #:server "db.mysite.com"
                   #:port 3306
                   #:database "webappdb"
                   #:user "webapp"
                   #:password "ultra5ecret")

  #(struct:object:connection% ...)

  > (mysql-connect #:user "me"
                   #:database "me"
                   #:password "icecream")

  #(struct:object:connection% ...)

  > (mysql-connect ; Typical socket path
                   #:socket "/var/run/mysqld/mysqld.sock"
                   #:user "me"
                   #:database "me")

  #(struct:object:connection% ...)

  > (mysql-connect #:socket (mysql-guess-socket-path)
                   #:user "me"
                   #:database "me")

  #(struct:object:connection% ...)

Attempts to guess the path for the socket based on conventional locations. This function returns the first such conventional path that exists in the filesystem. It does not check that the path is a socket file, nor that the path is connected to a MySQL server.

If the socket file cannot be found, an error is raised.

(sqlite3-connect #:database database    
  [#:mode mode])  connection?
  database : (or/c path-string? 'memory 'temporary)
  mode : (or/c 'read-only 'read/write 'create) = 'read/write
Opens the SQLite database at the file named by database, if database is a string or path. If database is 'temporary, a private disk-based database is created. If database is 'memory, a private memory-based database is created.

If mode is 'read-only, the database is opened in read-only mode. If mode is 'read/write (the default), the database is opened for reading and writing (if filesystem permissions permit). The 'create mode is like 'read/write, except that if the given file does not exist, it is created as a new database.

Examples:

  > (sqlite3-connect #:database "/path/to/my.db")

  #(struct:object:connection% ...)

  > (sqlite3-connect #:database "relpath/to/my.db"
                     #:mode 'create)

  #(struct:object:connection% ...)

(odbc-connect #:database database    
  [#:user user    
  #:password password    
  #:notice-handler notice-handler])  connection?
  database : string?
  user : (or/c string? #f) = #f
  password : (or/c string? #f) = #f
  notice-handler : 
(or/c output-port? 'output 'error
      (-> string? string? any))
 = void
Creates a connection to the ODBC Data Source named database. The user and password arguments are optional.

The notice-handler argument behaves the same as in postgresql-connect.

(odbc-driver-connect connection-string 
  [#:notice-handler notice-handler]) 
  connection?
  connection-string : string?
  notice-handler : 
(or/c output-port? 'output 'error
      (-> string? string? any))
 = void
Creates a connection using a connection string containing a sequence of keyword and value connection parameters.

Returns a list of known ODBC Data Sources. Each data souce is represented by a list of two strings; the first string is the name of the data source, and the second is the name of its associated driver.

Returns a list of known ODBC Drivers. Each driver is represented by a list, the first element of which is the name of the driver. The contents of the rest of each entry is currently undefined.

2.2 Mangaging connections

(connection? x)  boolean?
  x : any/c
Returns #t if x is a connection, #f otherwise.

(disconnect connection)  void?
  connection : connection?
Closes the connection.

(connected? connection)  boolean?
  connection : connection?
Returns #t if connection is connected, #f otherwise.

(connection-dbsystem connection)  dbsystem?
  connection : connection?
Gets an object encapsulating information about the database system of connection.

(dbsystem? x)  boolean?
  x : any/c
Predicate for objects representing database systems.

(dbsystem-name sys)  symbol?
  sys : dbsystem?
Returns a symbol that identifies the database system. Currently one of the following:
  • 'postgresql

  • 'mysql

  • 'sqlite3

  • 'odbc

Returns a list of symbols identifying types supported by the database system. See Type correspondences.

2.3 System-specific modules

The (planet ryanc/db:1:0) module exports all of the functions listed in this manual except those described in Utilities. The database system-specific connection modules are loaded lazily to avoid unnecessary dependencies on foreign libraries.

The following modules provide subsets of the bindings described in this manual.

 (require (planet ryanc/db:1:0/base))

Provides all generic connection operations (those described in Mangaging connections and Queries) and SQL data support (SQL types and conversions).

 (require (planet ryanc/db:1:0/postgresql))

Provides only postgresql-connect and postgresql-guess-socket-path.

 (require (planet ryanc/db:1:0/mysql))

Provides only mysql-connect and mysql-guess-socket-path.

 (require (planet ryanc/db:1:0/sqlite3))

Provides only sqlite3-connect. In contrast to (planet ryanc/db:1:0), this module immediately attempts to load the libsqlite3 foreign library when required, and it raises an error if the foreign library cannot be found.

 (require (planet ryanc/db:1:0/odbc))

Provides only odbc-connect, odbc-driver-connect, odbc-data-sources, and odbc-drivers. In contrast to (planet ryanc/db:1:0), this module immediately attempts to load the libodbc foreign library when required, and it raises an error if the foreign library cannot be found.