DBInterface.jl Documentation

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


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.


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

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.

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

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.

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.

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.

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.


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


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(x::Cursor) => Int

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




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


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