Connect to SQLite database (FireDAC)
Go Up to Database Connectivity (FireDAC)
This topic describes how to connect to SQLite database files.
Contents
Supported Versions
The FireDAC native driver supports SQLite database version 3.0 and later. For a detailed discussion on SQLite usage in FireDAC for a Delphi application, read the "Using SQLite with FireDAC" article.
Client Software
FireDAC supports two SQLite library linking modes:
- Static linking -- the following client libraries are statically linked into the application:
- Win32 - sqlite3_x86.obj
- Win64 - sqlite3_x64.obj
- macOS64 - libcgsqlite3.dylib (requires deployment)
- iOSDevice64 - libsqlite.a
- Android32 - libsqlite.a
- Android64 - libsqlite.a
FireDAC provides SQLite binaries v 3.31.1.
- Dynamic linking -- the following client libraries must be available in order to open a SQLite database:
- Win32 - x86 SQLITE3.DLL
- Win64 - x64 SQLITE3.DLL
- macOS64 - libsqlite3.dylib
- iOSDevice64 - libsqlite3.dylib
- Android32 - libsqlite.so
- Android64 - libsqlite.so
SQLITE_ENABLE_COLUMN_METADATA
is undefined). As a result, FireDAC may fail to detect if the column auto-incremental mode is available or whether it is enabled or disabled.You can download:
- The latest x86 DLL version from here, Chapter "Precompiled Binaries For Windows", item "This is a DLL" and place it in a folder listed in your PATH environment variable (for example, System32 folder) or in your application EXE folder.
- The x64 DLL version from here as "sqlite-netFx40-binary-x64-xxxxx.zip". Extract to a folder, copy SQLite.Interop.DLL into SQLITE3.DLL, then place it as above.
For SQLite, FireDAC adds the ability to choose static vs dynamic linkage of SQLite engine. To use the static linkage of SQLite engine it is required to add the unit FireDAC.Phys.SQLiteWrapper.Stat to the uses clause of one of the units of your project. Otherwise dynamical linkage will be chosen.
The TFDPhysSQLiteDriverLink.EngineLinkage property controls the linkage mode. When the value is slDefault or slStatic, then the FireDAC.Phys.SQLiteWrapper.Stat unit will be added at design-time to the unit hosting the component. If the application defines the connection at run-time, then the FireDAC.Phys.SQLiteWrapper.Stat unit must be added manually.
If the SQLite client library has not been installed properly, an exception is raised when you try to connect:
Linux Client Software
FireDAC on Linux supports only dynamic linking and requires the SQLite 3 client library. To install it:
-
On Ubuntu Server 16.04 LTS, run:
sudo apt-get install libsqlite3-0 sudo ln -s /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 /usr/lib/x86_64-linux-gnu/libsqlite3.so
-
On Red Hat Enterprise Linux 7, run:
sudo yum install sqlite sudo ln -s /usr/lib64/libsqlite3.so.0 /usr/lib64/libsqlite3.so
Driver Linkage
To link the driver:
- drop a TFDPhysSQLiteDriverLink component from the "FireDAC Links" palette page
- or include the FireDAC.Phys.SQLite unit in an uses clause.
Connection Definition Parameters
To connect to a SQLite database, most applications require that you specify DriverID and Database (see Defining Connection (FireDAC) for details).
DriverID=SQLite
Parameter | Description | Example value |
---|---|---|
Database |
A path to a database. Use ':memory:' or an empty string to create and connect to an empty in-memory database. A path can include path variables. |
|
OpenMode |
A mode to open a database:
|
ReadOnly |
Encrypt | Specifies a default encryption mode for a database. The mode may be overridden with an optional password prefix. If it is not specified, then the mode specified by this parameter is used. Otherwise, aes-256 is used. | |
Password | Specifies a password for an encrypted database. The value may have the following form:
[ aes-128 | aes-192 | aes-256 | aes-ctr-128 | aes-ctr-192 | aes-ctr-256 | aes-ecb-128 | aes-ecb-192 | aes-ecb-256 :] <password> The optional prefix controls the cipher algorithm to be used. The default value is an empty string, which means the unencrypted mode. |
|
NewPassword |
Specifies a new password for a database and performs an encryption operation:
|
|
BusyTimeout | Sets an "ms" milliseconds to sleep when a table is locked and UpdateOptions.LockWait is set to True. Zero means do not wait. The default value is 10000. | 5000 |
CacheSize | Changes the maximum number of database disk pages that SQLite holds in memory at once. Each page uses about 1.5K of memory. The default value is 10000. | 10000 |
SharedCache | Enables or disables the SQLite shared cache feature. For more details, read this. The default value is True. | False |
LockingMode |
Sets the database connection locking-mode. The value is one of the following:
The default value is Exclusive, because it allows you to get maximum read/write speed for single user applications. |
Exclusive |
Synchronous |
Sets the database connection synchronization mode of the in-memory cache with database files. The value is one of the following:
|
Off |
ForeignKeys |
Enables foreign key usage for the database connection, when the application uses SQLite v 3.6.19 or later. The value is one of the following:
|
Off |
StringFormat |
Defines how to represent String values:
|
Unicode |
GUIDFormat |
Defines how to store GUID values:
|
Binary |
DateTimeFormat |
Defines how to store date and time values:
|
Binary |
Extensions |
Enables, disables, or specifies the SQLite engine extensions to load:
|
MyExt.dll;FullTS.dll |
SQLiteAdvanced | Additional SQLite database connection options. For details, see the Pragma statements supported by SQLite. | auto_vacuum = 1;page_size = 4096;temp_store = FILE |
MetaDefCatalog | Default database name. The Design time code excludes the catalog name from the object name if it is equal to MetaDefCatalog. Setting MetaDefCatalog does not change the current database in the SQLite session. The default value is 'MAIN'. | MyDB |
Use Cases
- Connect to a local database in exclusive mode:
DriverID=SQLite Database=$(FDHOME)\DB\Data\FDDemo.sdb
- Connect to a shared database (it is not recommended to store SQLite databases on Windows shared folders for multi user read-write access):
DriverID=SQLite Database=\\srv\mydb.sqlite LockingMode=Normal Synchronous=Normal
- Encrypt an unencrypted database:
DriverID=SQLite Database=$(FDHOME)\DB\Data\FDDemo.sdb NewPassword=aes-256:123qwe
- Open an encrypted database:
DriverID=SQLite Database=c:\temp\test.db Password=123qwe
- Connect to an in-memory database:
DriverID=SQLite Database=:memory:
- Connect to a database file in the Documents folder on the iOS Device in exclusive mode:
DriverID=SQLite Database=$(DOC)/test.sdb
See Also
- Common Connection Parameters
- FAQs
- How to configure FireDAC Drivers
- How to manage FireDAC Connection Definitions
- Mobile Tutorial: Using FireDAC and SQLite (iOS and Android)
- Mobile Tutorial: Using FireDAC in Mobile Applications (iOS and Android)
Samples
- FireDAC SQLite sample