Connecting to SQLite
An Ignition Gateway can create a SQLite database, allowing for data collection without installing a separate SQL database. This is ideal for small scale applications, as well as testing and demonstrations.
SQLite connections offer convenience, but Relational Database Management Systems (RDBMS) generally offer better performance, especially in cases where queries are frequently executed such as systems utilizing a historian system. As a result, the SQLite connection is not recommended for production systems as a historian database. Learn more about appropriate uses of SQLite in their official documentation.
Unlike other database connections, SQLite databases only support a single connection at a time. Configuring this type of Database Connection means the Gateway will constantly be connected to the SQLite database, preventing other systems from connecting.
SQLite Connect URL​
Ignition Gateways can configure a SQLite database connection without any additional installation. This is because the driver will create a file (which is the SQLite database) at the directory specified in the Connect URL property. Pointing the Connect URL to an existing IDB file will cause the connection to access the existing tables within the file. All Connect URLs must start with the following pattern:
jdbc:sqlite:
Followed by the destination to the database file.
# On Windows
jdbc:sqlite:C:/Path/To/File.db
# On Mac
jdbc:sqlite:/path/on/mac/File.bd
# On Linux
jdbc:sqlite:/path/on/linux/File.db
For Windows machines, either forward / or backslashes\ can be used.
Storing to Memory​
Instead of creating a file, the Connect URL can specify that data should be stored in memory. As expected, this is not an ideal solution for systems that require any sort of long term storage, but can be useful in cases where data should be ephemeral.
jdbc:sqlite::memory
Relative Paths​
| Keyword | Description |
|---|---|
| ${data} | Represents the Gateway's "data" directory at /usr/local/ignition/data/. |
| ${local} | Represents the Gateway's "local" directory at /usr/local/. Note: SQLite files created in this directory will not be included in gateway backup files. If you wish for a GWBK to include the SQLite file, then set the path to another location within the data directory, but outside of the local file. |
In both cases, additional folders can be added after the keyword. The example below would create "Folder" in the gateway's data directory, and place the DB file in the Folder.
jdbc:sqlite:${data}/Folder/File.db
Connect Ignition to SQLite Database​
On the Gateway Webpage, go to the Config section.
Scroll down to Databases > Connections.
The Database Connections page is displayed. Click on Create new Database Connection....
Select the SQLite driver and click Next.
On the New Database Connection page, enter a name for your connection. We used SQLite_Connect.
Next, enter the connect URL for the database. For SQLite this setting must lead to a location on a filesystem. In our example here we used jdbc:sqlite:C:Program Files/SQLite/File.db. This path ultimately must lead to a local directory, or a locally mapped drive.
Click on Create New Database Connection at the bottom of the form.
Your connection is now created and the Database Connections page is displayed showing the Status of your connection as Valid.
