DBInterface.jl Documentation

DBInterface.jl provides interface definitions to allow common database operations to be implemented consistently across various database packages.

Functions

DBInterface.connectFunction
DBInterface.connect(DB, args...; kw...) => DBInterface.Connection

Database packages should overload DBInterface.connect for a specific DB DBInterface.Connection subtype that returns a valid, live database connection that can be queried against.

source
DBInterface.getconnectionFunction
DBInterface.getconnection(::DBInterface.Statement)

For a valid DBInterface.Statement, return the DBInterface.Connection the statement is associated with.

source
DBInterface.prepareFunction
DBInterface.prepare(conn::DBInterface.Connection, sql::AbstractString) => DBInterface.Statement
DBInterface.prepare(f::Function, sql::AbstractString) => DBInterface.Statement

Database packages should overload DBInterface.prepare for a specific DBInterface.Connection subtype, that validates and prepares a SQL statement given as an AbstractString sql argument, and returns a DBInterface.Statement subtype. It is expected that DBInterface.Statements are only valid for the lifetime of the DBInterface.Connection object against which they are prepared. For convenience, users may call DBInterface.prepare(f::Function, sql) which first calls f() to retrieve a valid DBInterface.Connection before calling DBInterface.prepare(conn, sql); this allows deferring connection retrieval and thus statement preparation until runtime, which is often convenient when building applications.

source
DBInterface.@prepareMacro
DBInterface.@prepare f sql

Takes a DBInterface.Connection-retrieval function f and SQL statement sql and will return a prepared statement, via usage of DBInterface.prepare. If the statement has already been prepared, it will be re-used (prepared statements are cached).

source
DBInterface.executeFunction
DBInterface.execute(conn::DBInterface.Connection, sql::AbstractString, [params]) => DBInterface.Cursor
DBInterface.execute(stmt::DBInterface.Statement, [params]) => DBInterface.Cursor
DBInterface.execute(f::Callable, conn::DBInterface.Connection, sql::AbstractString, [params])
DBInterface.execute(f::Callable, stmt::DBInterface.Statement, [params])

Database packages should overload DBInterface.execute for a valid, prepared DBInterface.Statement subtype (the first method signature is defined in DBInterface.jl using DBInterface.prepare), which takes an optional params argument, which should be an indexable collection (Vector or Tuple) for positional parameters, or a NamedTuple for named parameters. Alternatively, the parameters could be specified as keyword agruments of DBInterface.execute.

DBInterface.execute should return a valid DBInterface.Cursor object, which is any iterator of "rows", which themselves must be property-accessible (i.e. implement propertynames and getproperty for value access by name), and indexable (i.e. implement length and getindex for value access by index). These "result" objects do not need to subtype DBInterface.Cursor explicitly as long as they satisfy the interface. For DDL/DML SQL statements, which typically do not return results, an iterator is still expected to be returned that just iterates nothing, i.e. an "empty" iterator.

Note that DBInterface.execute returns a single DBInterface.Cursor, which represents a single resultset from the database. For use-cases involving multiple result-sets from a single query, see DBInterface.executemultiple.

If function f is provided, DBInterface.execute will return the result of applying f to the DBInterface.Cursor object and close the prepared statement upon exit.

source
DBInterface.transactionFunction
DBInterface.transaction(f, conn::DBInterface.Connection)

Open a transaction against a database connection conn, execute a closure f, then "commit" the transaction after executing the closure function. The default definition in DBInterface.jl is a no-op in that it just executes the closure function with no transaction. Used in DBInterface.executemany to wrap the individual execute calls in a transaction since this often leads to much better performance in database systems.

source
DBInterface.executemanyFunction
DBInterface.executemany(conn::DBInterface.Connection, sql::AbstractString, [params]) => Nothing
DBInterface.executemany(stmt::DBInterface.Statement, [params]) => Nothing

Similar in usage to DBInterface.execute, but allows passing multiple sets of parameters to be executed in sequence. params, like for DBInterface.execute, should be an indexable collection (Vector or Tuple) or NamedTuple, but instead of a single scalar value per parameter, an indexable collection should be passed for each parameter. By default, each set of parameters will be looped over and DBInterface.execute will be called for each. Note that no result sets or cursors are returned for any execution, so the usage is mainly intended for bulk INSERT statements.

source
DBInterface.executemultipleFunction
DBInterface.executemultiple(conn::DBInterface.Connection, sql::AbstractString, [params]) => Cursor-iterator
DBInterface.executemultiple(stmt::DBInterface.Statement, [params]) => Cursor-iterator

Some databases allow returning multiple resultsets from a "single" query (typically semi-colon (;) separated statements, or from calling stored procedures). This function takes the exact same arguments as DBInterface.execute, but instead of returning a single Cursor, it returns an iterator of Cursors. This function defines a generic fallback that just returns (DBInterface.execute(stmt, params),), a length-1 tuple for a single Cursor resultset.

source
DBInterface.close!Function
DBInterface.close!(conn::DBInterface.Connection)

Immediately closes a database connection so further queries cannot be processed.

source
DBInterface.close!(stmt::DBInterface.Statement)

Close a prepared statement so further queries cannot be executed.

source
DBInterface.close!(x::Cursor) => Nothing

Immediately close a resultset cursor. Database packages should overload for the provided resultset Cursor object.

source
DBInterface.lastrowidFunction
DBInterface.lastrowid(x::Cursor) => Int

If supported by the specific database cursor, returns the last inserted row id after executing an INSERT statement.

source

Types

DBInterface.StatementType

Database packages should provide a DBInterface.Statement subtype which represents a valid, prepared SQL statement that can be executed repeatedly

source
DBInterface.CursorType

Any object that iterates "rows", which are objects that are property-accessible and indexable. See DBInterface.execute for more details on fetching query results.

source