DBInterface.jl Documentation

DBInterface.connect(DB, args...; kw...) => DBInterface.Connection
DBInterface.connect(f::Callable, DB, args...; kw...)

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.

When f is provided, the connection is passed to f, closed upon exit, and the result of f is returned.

Declare the string as written in SQL.

The macro does not parse, escape, validate, or sanitize the string.

DBInterface.getconnection(::DBInterface.Statement)

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
DBInterface.prepare(f::Callable, conn::DBInterface.Connection, sql::AbstractString; kw...)

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.

When both f and conn are provided, the prepared statement is passed to f, closed upon exit, and the result of f is returned.

DBInterface.@prepare f sql

Takes a zero-argument DBInterface.Connection-retrieval function f and SQL statement sql and returns a prepared statement via DBInterface.prepare. Each call site caches one statement per connection object. A statement is reused while its SQL text remains unchanged. If the SQL changes, the old statement for that connection is closed and replaced. Cached entries are retained, so use this macro with a bounded set of long-lived connection objects. The cache is synchronized, but it does not make a connection or statement safe for concurrent use.

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 connection signature is defined in DBInterface.jl using DBInterface.prepare), which takes an optional params argument. Parameters should be an indexable collection (AbstractVector or Tuple) for positional parameters, or a NamedTuple or AbstractDict for named parameters. Alternatively, the parameters could be specified as keyword arguments of DBInterface.execute.

Placeholder syntax and named-parameter support are driver-specific. Each placeholder normally binds one scalar value. DBInterface does not parse or sanitize SQL, and bound parameters cannot replace identifiers, keywords, or other SQL fragments.

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 and implement DBInterface.close!. For DDL/DML SQL statements, which typically do not return results, an empty iterator is still expected.

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 returns the result of applying f to the cursor and closes the cursor upon exit. The connection form also closes the statement that it prepares internally.

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. The default definition executes BEGIN TRANSACTION, COMMIT, and, after an error, ROLLBACK. Database packages should overload this method when those commands do not match the database's transaction behavior. DBInterface.executemany uses this method because a transaction often makes repeated statements much faster. If both the transaction and its rollback fail, a CompositeException reports both errors, with the original error first.

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 AbstractVector, Tuple, NamedTuple, or AbstractDict, 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. For compatibility, a NamedTuple or keyword batch is passed to each execution positionally in field order. Use an AbstractDict batch when each execution must retain parameter names.

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.

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

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

Error for signaling that parameters are used inconsistently or incorrectly.

Fallback, generic error object for database operations

Standard warning object for various database operations

Database packages should subtype DBInterface.Connection which represents a connection to a database

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.

The container types for positional statement parameters supported by DBInterface.execute

The container types for named statement parameters supported by DBInterface.execute

The container types for statement parameters supported by DBInterface.execute