Small. Fast. Reliable.
Choose any three.
Beginning after version 3.3.6, SQLite has the ability to load new SQL functions and collating sequences from shared libraries and DLLs. This means that you no longer have to recompile SQLite in order to add new functions and collations.

There is a new experimental API call sqlite3_load_extension() that does the loading. This API is experimental meaning that it is not guaranteed to be supported in future releases in a backwards compatible way. We reserve the right to make changes to the interface. After gaining some experience with the interface, we will probably remove the "experimental" label.

The new API is accessed from the shell using the ".load" command:

.load filename  ?entrypoint?

The new API can also be accessed from SQL using the load_extension() function:

SELECT load_extension('filename');
SELECT load_extension('
filename','entrypoint');

The filename is the name of the shared library or DLL. The entrypoint is the name of an initialization function within the shared library. If the entry point is omitted then a default entry point function named sqlite3_extension_init is called. Use of the default entry point name is recommended.

The entry point must be a function with the following prototype:

int sqlite3_extension_init(
  sqlite3 *db,          /* The database connection */
  char **pzErrMsg,      /* Write error messages here */
  const sqlite3_api_routines *pApi  /* API methods */
);
The db parameter is the database connection pointer returned from sqlite3_open(). The extension will likely want to pass this argument through to routines like sqlite3_create_function() and sqlite3_create_collation(). If an error occurs and pzErrMsg is not NULL, then the extension should use sqlite3_mprintf() to generate an error message and store that message at *pzErrMsg. The pApi argument contains pointers back to all of the APIs in the calling library. Extensions should reference the SQLite API through these pointers.

Security Considerations

Some programs allow users to enter SQL statements then check those statements using sqlite3_set_authorizer() to prevent attacks against the program. The new load_extension() SQL function described above could circumvent this protection and open holes in legacy applications. To avoid this, the entire extension loading mechanism is turned off by default. To enable the extension loading mechanism, first invoke this API:

int sqlite3_enable_load_extension(sqlite3 *db, int onoff);

The onoff parameter is true to enable extension loading and false to disable it. This allows programs that want to run user-entered SQL to do so safely by first turning off extension loading. Extension loading is off by default so that if an older program links against a newer version of SQLite it will not open a potential exploit.

Example Extension

The following code is an example of how to build a loadable extension:


#include <sqlite3ext.h>
SQLITE_EXTENSION_INIT1

/*
** The half() SQL function returns half of its input value.
*/
static void halfFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}

/* SQLite invokes this routine once when it loads the extension.
** Create new functions, collating sequences, and virtual table
** modules here.  This is usually the only exported symbol in
** the shared library.
*/
int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
  SQLITE_EXTENSION_INIT2(pApi)
  sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
  return 0;
}

In the example above, the green text is boiler-plate that should appear in every loadable extension. The blue text is code you add to implement your extension. In the example, a single SQL function that multiplies its input by 0.5 is shown. A real loadable extension would probably do something more useful.

A extension is not limited to creating a single function or collating sequence. We expect that typically extensions will create multiple user functions, collating sequences, and/or virtual-table modules.

Note that the extension uses the header file "sqlite3ext.h" instead of "sqlite3.h". This is an important difference. Dynamically loaded extensions should always use "sqlite3ext.h" and statically linked additions to the library should use "sqlite3.h". If you want your code to work as either a statically linked or a dynamically loaded module, the you will need to use #ifdefs to #include the appropriate header file.

The SQLITE_EXTENSION_INIT1 and SQLITE3_EXTENSION_INIT2 symbols are C preprocessor macros that deal with redirecting the API routines through the function pointers in sqlite3_api_routines structure. You can look at the definitions of these macros in the sqlite3ext.h header file to find out exactly what they do, if you are curious. The simplest approach is just to use them as shown above.

How To Build a Loadable Extension Shared Library

You need the sqlite3ext.h header file available on your system. It may already be installed or you can find it in the SQLite amalgamation downloads. We will assume it is in a directory named sqlite3. It should be the same version or older than sqlite3.h.

The extension filename can be anything you want from the traditional .so/.dll to something more specific. We will build half.sqlext from the source in half.c.

Using GCC/MinGW on Windows and Linux

gcc -shared -fPIC -Isqlite3 -o half.sqlext half.c

Using GCC on Mac OSX

gcc -bundle -fPIC -Isqlite3 -o half.sqlext half.c

Microsoft Tools on Windows

cl /Gd half.c /I sqlite3 /DDLL /LD /link /export:sqlite3_extension_init /out:half.sqlext

Running it with the shell

SQLite version 3.3.17
Enter ".help" for instructions
sqlite> .load half.sqlext
sqlite> select half(7);
3.5


Wish List

  • Automatic loading of extension shared libraries when a database is first opened. This would benefit SQLite users who do not use the SQLite shell program, and only use the sqlite3 shared library. An sqlite specific table in each database loaded could be consulted with a list of shared library names to load. These extension shared libraries would not have paths or file suffixes (.dll or .so). The user would be expected to set up an appropriate search path via the native OS' shared library loading mechanism (PATH, LD_LIBRARY_PATH, or equivalent). The suffix would be determined at sqlite3 compile time to be .dll, .so, or whatever is appropriate for the platform. Such a table might look like this:
    
      create table sqlite_extensions (
        load INT,  -- the order in which the extension
                   -- is to be loaded/initialized.
                   -- lowest number loads first.
                   -- if tied, order is alphabetical by name.
    
        name TEXT, -- name of the extension shared library.
                   -- Note: both the path and suffix is NOT to be included in name.
    
        init TEXT, -- name of the shared library entry point init function.
                   -- optional, may be null.
                   -- if null, the entry point function will be constructed
                   -- from name.
    
        fini TEXT  -- name of the shared library entry point unload function.
                   -- shared library unloading will be in the reverse order
                   -- of loading.
                   -- optional, may be null.
      );