DBInterface.jl Documentation
DBInterface.jl provides interface definitions to allow common database operations to be implemented consistently across various database packages.
Functions
DBInterface.connect
— FunctionDBInterface.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.
DBInterface.getconnection
— FunctionDBInterface.getconnection(::DBInterface.Statement)
For a valid DBInterface.Statement
, return the DBInterface.Connection
the statement is associated with.
DBInterface.prepare
— FunctionDBInterface.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.Statement
s 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.
DBInterface.@prepare
— MacroDBInterface.@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).
DBInterface.execute
— FunctionDBInterface.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.
DBInterface.transaction
— FunctionDBInterface.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.
DBInterface.executemany
— FunctionDBInterface.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.
DBInterface.executemultiple
— FunctionDBInterface.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 Cursor
s. This function defines a generic fallback that just returns (DBInterface.execute(stmt, params),)
, a length-1 tuple for a single Cursor
resultset.
DBInterface.close!
— FunctionDBInterface.close!(conn::DBInterface.Connection)
Immediately closes a database connection so further queries cannot be processed.
DBInterface.close!(stmt::DBInterface.Statement)
Close a prepared statement so further queries cannot be executed.
DBInterface.close!(x::Cursor) => Nothing
Immediately close a resultset cursor. Database packages should overload for the provided resultset Cursor
object.
DBInterface.lastrowid
— FunctionDBInterface.lastrowid(x::Cursor) => Int
If supported by the specific database cursor, returns the last inserted row id after executing an INSERT statement.
Types
DBInterface.Connection
— TypeDatabase packages should subtype DBInterface.Connection
which represents a connection to a database
DBInterface.Statement
— TypeDatabase packages should provide a DBInterface.Statement
subtype which represents a valid, prepared SQL statement that can be executed repeatedly
DBInterface.Cursor
— TypeAny object that iterates "rows", which are objects that are property-accessible and indexable. See DBInterface.execute
for more details on fetching query results.