Experimenting With SQLite JDBC Connections In Lucee CFML
Although SQLite has been around for almost 25-years, it seems to be having a moment. In the past year or two, I've heard many people discuss the power of embedding SQLite databases within an application. I've never looked at SQLite before; and, I don't think it necessarily makes sense in the context of a ColdFusion web application; but, as a fun exploration, I wanted to see if I could get ColdFusion to connect to a SQLite database.
View this code in my ColdFusion-SQLite project on GitHub.
One of the compelling features of SQLite is the fact that you don't need to have a separate database engine running—such as Microsoft SQL Sever or MySQL. Instead, SQLite databases are represented as physical, server-local .db files. And, all interfacing with these database files is done entirely through database drivers.
In ColdFusion, consuming a database usually means creating a datasource that uses a JDBC (Java Database Connectivity) driver. Some initial searching seemed to indicate that Java has a built-in SQLite driver. However, when I tried to create a JDBC datasource without an additional driver, I received the following ColdFusion error:
lucee.commons.lang.ClassException: cannot load class through its string name, because no definition for the class with the specified nameorg.sqlite.JDBCcould be found.
I ended up downloading the jdbc-sqlite driver by Xerial from Maven Repository. Its only dependency is the SLF4J logging library; and, since Lucee CFML already ships with SLF4J, I only needed the one JAR file.
Normally, when pulling custom JAR files into a Lucee CFML context, I use Lucee's ability to load JAR files via createObject(). In this case, however, the JAR files needed to be made available outside of my application code. As such, I needed to place the sqlite-jdbc-3.45.3.0.jar file in Lucee's lib-equivalent directory. And, to do that, I created a Dockerfile:
FROM lucee/lucee:5.4.4.38
# Setup the SQLite JDBC driver.
COPY ./docker/cfml/lib/sqlite-jdbc-3.45.3.0.jar /opt/lucee/server/lucee-server/bundles
# Note: We didn't need to copy the SLF4J JAR because Lucee already has one.
# COPY ./docker/cfml/lib/slf4j-api-1.7.36.jar /opt/lucee/server/lucee-server/bundles
This Dockerfile creates a container image for Lucee CFML 5.4.4.38 with the SQLite JDBC driver installed. I then created a docker-compose.yaml file to orchestrate a running instance of this CFML server:
version: "2.4"
services:
cfml:
build: "."
ports:
- "8888:8888"
volumes:
- ./app:/var/www
# Don't put the databases in a publicly accessible directory!
- ./databases:/var/www-databases
At this point, running docker compose up in the project directory built the Lucee CFML image, created a local network, and spun-up the ColdFusion server at http://localhost:8888. Finally it was time to try connecting to a SQLite database.
According to the Xerial site, the jdbc-sqlite driver works by defining a connection string that points directly to the physical database file on the server. In the following example, sample.db is the SQLite database file:
jdbc:sqlite:sample.db
So, to try this out, I created a per-application datasource in my Application.cfc component. And, since I know that my application code is located inside the Docker container at /var/www, I used the fully-qualified path, /var/www/test.db:
component {
this.name = "ColdFusionSqlite";
this.applicationTimeout = createTimeSpan( 1, 0, 0, 0 );
this.sessionManagement = false;
this.setClientCookies = false;
// A SQLite database connection pointing to `test.db`.
this.datasources[ "test" ] = {
class: "org.sqlite.JDBC",
connectionString: "jdbc:sqlite:/var/www/test.db"
};
}
Then, I created a test ColdFusion page to see if I could perform a SELECT against this datasource:
<cfquery name="results" datasource="test">
SELECT
( 1 ) AS helloWorld
;
</cfquery>
<cfdump var="#results#" />
And it worked! The CFQuery tag returned a 1-row query. And, when I looked at the local file system, I could see a 0kb file named test.db.
Now, one of the features that people often discuss in the context of SQLite is how lightweight it is. In fact, it's so lightweight (apparently) that you can completely change the way you think about architecting your data. For example, instead of creating a multi-tenant database in which every row has some sort of userID, you can literally create a separate database for every user.
But, if the physical filepath is part of the JDBC connection string, how would that work? I suppose I could use the ColdFusion Administrator component to programmatically add new datasources to the web context. But, that seemed like a lot of work for this simple exploration.
Thankfully, I came across this page in the CommandBox manual by Ortus Solutions. It turns out, in Lucee CFML, you can provide the CFQuery datasource attribute as a struct. Meaning, you can provide the database connection string directly to the CFQuery tag.
Aside: Providing the connection string directly to the
CFQueryis probably a bad idea. It almost certainly doesn't play nicely with connection pooling. And, I have no idea if it's thread-safe. It also doesn't seem to be well documented (other than the attribute being defined asanyin the CFML documentation). That said, as an exploration, I'm OK with using this. Check the CFML source code if you want to see what this is doing under the hood.
With the ability to provide a per-CFQuery datasource, I can, in theory, provide a unique database file for every single query. Which means, I can play around with creating a per-user database.
To explore this idea, I revamped by Application.cfc to use a per-application path mapping. For security purposes, I wanted to move my database files out of the web-root. But, I didn't want my code to be tightly coupled to the file location. So, I created a mapping for /databases that points to a secure location inside the Docker container, but outside of the /app/www root:
Note: If you look back at my
docker-compose.yamlfile, you'll see that I'm mounting this path as a Volume. This way, when the database files are created by the JDBC driver, Docker will sync the files back to my host machine. Which allows the databases to survive Docker container rebuilds.
component {
this.name = "ColdFusionSqlite";
this.applicationTimeout = createTimeSpan( 1, 0, 0, 0 );
this.sessionManagement = false;
this.setClientCookies = false;
// In order to decouple the ColdFusion application from the physical location of the
// SQLite databases, we're going to use a per-application mapping.
this.mappings = {
"/databases": "/var/www-databases"
};
}
As a context for exploration, I'm going to create task lists in which each user receives their own SQLite database with their own unique task table. But, in order to keep track of the users in the system, I also need to create a root / master database that has a user table.
To hide some of the redundant logic around the SQLite JDBC connection details, I created an abstract base component for my Data Access Gateway (DAO) layer. This base component accepts a filename and generates a private variable called, datasource, to be used in the subsequent CFQuery tags.
You'll notice that, internally, this ColdFusion component is using the expandPath() method in order to transform the /databases per-application mapping into the physical filepath on the server:
component
output = false
hint = "I provide the abstract base component for a SQLite data access layer."
{
/**
* I initialize the data access object (DAO) with the given database file.
*/
package void function init( required string sqliteFilename ) {
variables.sqliteFilename = arguments.sqliteFilename;
// NOTE: We're using a per-application mapping to decouple the actual storage
// location of the database files. That's why we're using expandPath() - so that
// Lucee resolves to server's root path.
variables.sqliteFilepath = expandPath( "/databases/#sqliteFilename#" );
variables.datasource = {
class: "org.sqlite.JDBC",
connectionString: "jdbc:sqlite:#sqliteFilepath#"
};
}
// ---
// PUBLIC METHODS.
// ---
/**
* I determine if the physical SQLite database file exists.
*/
public boolean function databaseExists() {
return fileExists( sqliteFilepath );
}
/**
* I delete the file representing the current database.
*/
public void function dropDatabase() {
if ( databaseExists() ) {
fileDelete( sqliteFilepath );
}
}
}
With this abstract base component in place, I then created two concrete ColdFusion components to implement all of the database access logic. I created one component for the "master" database (with the list of users) and one component for the "user" database (with the list of tasks).
Each of these components extends the above SQLiteDAO and then calls super.init() in order to provide the filename for the SQLite database. For the master database, I'm just hard-coding the name, master.db. But, for the user data, each user is going to receive their own SQLite database. As such, the user DAO provides a dynamic expression as the database filename: user_#userID#.db.
Since these databases are being created on-the-fly, each DAO has an ensureDatabase() method which creates the necessary database tables. In this exploration, this is relatively simple since each database only has one table. In a production context, I imagine that managing and migrating data must get quite a bit more complicated.
Anyway, let's look at the user management workflow first, which consumes a MasterDAO.cfc ColdFusion component. For the most part, this is just traditional CRUD (Create, Read, Update, Delete) SQL. But, there are some SQLite-specific things that are non-traditional. For example, SQLite only has four data types (INTEGER, REAL, TEXT, BLOB, and NULL, which is a lack of data). And, when inserting a row, the primary key isn't automatically returned as the generatedKey such as it is with other SQL database drivers. Instead, you have to append RETURNING ROWID to the SQL statement.
In the following Lucee CFML code, I'm using tag islands to author the SQL statements in a CFScript context. Take note that each CFQuery tag is including the attribute:
datasource="#datasource#"
This datasource is the private variable created by the abstract base SQLiteDAO.cfc that each of my data access components is extending. This attribute is what allows each CFQuery tag instance to operate using its own SQLite database connection.
component
extends = "SQLiteDAO"
hint = "I provide data access methods for the master database (ie, the list of users)."
{
/**
* I initialize the data access object (DAO) for the root experience.
*/
public void function init() {
super.init( "master.db" );
}
// ---
// PUBLIC METHODS.
// ---
/**
* I add a new user with the given name. The auto-generated ID is returned.
*/
public numeric function addUser( required string name ) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: masterDAO.addUser(). */
INSERT INTO user
(
name
) VALUES (
<cfqueryparam value="#name#" sqltype="varchar" />
)
RETURNING
ROWID
;
</cfquery>
```
// NOTE: SQLite doesn't return a "generatedKey". However, when a query ends with
// "RETURNING ROWID", it will return a query with a single column, id, which
// contains the generated value.
return val( results.id );
}
/**
* I delete the users that match the given filters.
*/
public void function deleteUsersByFilter(
numeric id
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: masterDAO.deleteUsersByFilter(). */
DELETE FROM
user
WHERE
TRUE
<cfif arguments.keyExists( "id" )>
AND
id = <cfqueryparam value="#id#" sqltype="integer" />
</cfif>
;
</cfquery>
```
}
/**
* I ensure that the database tables exist.
*/
public void function ensureDatabase() {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: masterDAO.ensureDatabase(). */
CREATE TABLE IF NOT EXISTS `user` (
`id` INTEGER PRIMARY KEY,
`name` TEXT NOT NULL
);
</cfquery>
```
}
/**
* I get the users that match the given filters.
*/
public array function getUsersByFilter(
numeric id
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#" returnType="array">
/* DEBUG: masterDAO.getUsersByFilter(). */
SELECT
u.id,
u.name
FROM
user u
WHERE
TRUE
<cfif arguments.keyExists( "id" )>
AND
u.id = <cfqueryparam value="#id#" sqltype="integer" />
</cfif>
ORDER BY
u.id ASC
;
</cfquery>
```
return results;
}
}
With this data access object, I'm able to create a root page in my ColdFusion application that manages the list of users. To keep this application super simple, all of the CFML pages just POST back to themselves in order to change the application state. As such, both the root page and the user-detail page are broken up (logically) into two sections:
The state change logic at the top.
The view rendering logic at the bottom.
I'm doing zero data validation in this demo. The point here isn't to create a robust application, it's to explore SQLite in the context of ColdFusion.
The root page has two functions: it provides a way to add new users at the top; and, it provides a way to delete individual users from the list.
<cfscript>
param name="form.action" type="string" default="";
param name="form.userID" type="numeric" default=0;
param name="form.userName" type="string" default="";
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
// In the root page, we're going to use a master database to track the list of users.
// Then, each user will receive their OWN DATABASE to record tasks.
masterDAO = new lib.MasterDAO();
masterDAO.ensureDatabase();
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
if ( form.action.len() ) {
// CAUTION: I'm not doing any data massaging or validation here - I just want to
// keep the code really simple so as to focus on the SQLite aspects.
switch ( form.action ) {
case "addUser":
masterDAO.addUser( form.userName );
break;
case "deleteUser":
user = masterDAO
.getUsersByFilter( id = val( form.userID ) )
.first()
;
userDAO = new lib.UserDAO( user.id );
userDAO.dropDatabase();
masterDAO.deleteUsersByFilter( id = user.id );
break;
}
// BEST PRACTICE: Always refresh the page so that a CMD+R doesn't resubmit the
// form and perform the same action twice.
location(
url = "./index.cfm",
addToken = false
);
}
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
users = masterDAO.getUsersByFilter();
</cfscript>
<cfoutput>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" type="text/css" href="./public/main.css" />
</head>
<body>
<h1>
Lucee CFML SQLite Exploration
</h1>
<form method="post">
<input
type="text"
name="userName"
placeholder="Name..."
value=""
autofocus
autocomplete="off"
size="20"
data-1p-ignore
/>
<button type="submit" name="action" value="addUser">
Add User
</button>
</form>
<h2>
Users
</h2>
<cfloop index="i" value="user" array="#users#">
<form method="post" class="item">
<input
type="hidden"
name="userID"
value="#encodeForHtmlAttribute( user.id )#"
/>
<a href="./tasks.cfm?userID=#encodeForUrl( user.id )#">
#encodeForHtml( user.name )#
</a>
<button
type="submit"
name="action"
value="deleteUser"
class="push-right">
Delete
</button>
</form>
</cfloop>
<cfif ! users.len()>
<p>
<em>No users.</em>
</p>
</cfif>
</body>
</html>
</cfoutput>
With this code in place, I can start fleshing out my user list:
Every time I add a new user to the application, I'm creating a new user record in the master database. But, this doesn't actually create a user-specific SQLite database yet. The user-specific code is handled in the task-management workflow.
But, before we look at the task workflow, let's look at the UserDAO.cfc ColdFusion component. Just like the MasterDAO.cfc, this component also extends the base SQLiteDAO.cfc. It accepts, as a constructor argument the userID; and, it turns around a passes a user-specific SQLite database filename, user_#userID#.db to the super constructor. This allows every instance of this CFC to operate on a different database.
The rest of the code in this DAO consists of straightforward CRUD operations:
component
extends = "SQLiteDAO"
hint = "I provide data access methods for a specific user database."
{
/**
* I initialize the data access object (DAO) for the given user.
*/
public void function init( required numeric userID ) {
// Each user gets their own, unique SQLite database.
super.init( "user_#userID#.db" );
}
// ---
// PUBLIC METHODS.
// ---
/**
* I add a new task with the given name. The auto-generated ID is returned.
*/
public numeric function addTask( required string name ) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: userDAO.addTask(). */
INSERT INTO task
(
name,
isComplete,
createdAt
) VALUES (
<cfqueryparam value="#name#" sqltype="varchar" />,
FALSE,
datetime( "now" )
)
RETURNING
ROWID
;
</cfquery>
```
// NOTE: SQLite doesn't return a "generatedKey". However, when a query ends with
// "RETURNING ROWID", it will return a query with a single column, id, which
// contains the generated value.
return val( results.id );
}
/**
* I delete the tasks that match the given filters.
*/
public void function deleteTasksByFilter(
numeric id,
boolean isComplete
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: userDAO.deleteTasksByFilter(). */
DELETE FROM
task
WHERE
TRUE
<cfif arguments.keyExists( "id" )>
AND
id = <cfqueryparam value="#id#" sqltype="integer" />
</cfif>
;
</cfquery>
```
}
/**
* I ensure that the database tables exist.
*/
public void function ensureDatabase() {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: userDAO.ensureDatabase(). */
CREATE TABLE IF NOT EXISTS `task` (
`id` INTEGER PRIMARY KEY,
`name` TEXT NOT NULL,
`isComplete` INTEGER NOT NULL CHECK ( isComplete IN ( 0, 1 ) ),
`createdAt` TEXT NOT NULL
);
</cfquery>
```
}
/**
* I get the tasks that match the given filters.
*/
public array function getTasksByFilter(
numeric id,
boolean isComplete
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#" returnType="array">
/* DEBUG: userDAO.getTasksByFilter(). */
SELECT
t.id,
t.name,
t.isComplete,
t.createdAt
FROM
task t
WHERE
TRUE
<cfif arguments.keyExists( "id" )>
AND
t.id = <cfqueryparam value="#id#" sqltype="integer" />
</cfif>
<cfif arguments.keyExists( "isComplete" )>
AND
t.isComplete = <cfqueryparam value="#isComplete#" sqltype="integer" />
</cfif>
ORDER BY
t.id ASC
;
</cfquery>
```
return results;
}
/**
* I update the task with the given ID.
*/
public void function updateTask(
required numeric id,
boolean isComplete
) {
```
<cfquery name="local.results" result="local.metaResults" datasource="#datasource#">
/* DEBUG: userDAO.updateTask(). */
UPDATE
task
SET
<cfif arguments.keyExists( "isComplete" )>
isComplete = <cfqueryparam value="#isComplete#" sqltype="integer" />,
</cfif>
id = id
WHERE
id = <cfqueryparam value="#id#" sqltype="integer" />
;
</cfquery>
```
}
}
Since this ColdFusion component creates and caches the datasource variable, I need to create a unique instance of the CFC on each page request. However, doing so will automatically create a SQLite database file on disk. As such, in my task management workflow, it's insufficient to only use the UserDAO.cfc. I have to first check with the MasterDAO.cfc to see if the user exists. And, if so, only then go about instantiating my UserDAO.cfc with the validated user ID:
<cfscript>
param name="url.userID" type="numeric" default=0;
param name="form.action" type="string" default="";
param name="form.taskID" type="string" default=0;
param name="form.taskName" type="string" default="";
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
users = new lib.MasterDAO()
.getUsersByFilter( id = val( url.userID ) )
;
if ( ! users.len() ) {
location(
url = "./index.cfm",
addToken = false
);
}
user = users.first();
userDAO = new lib.UserDAO( user.id );
userDAO.ensureDatabase();
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
if ( form.action.len() ) {
// CAUTION: I'm not doing any data massaging or validation here - I just want to
// keep the code really simple so as to focus on the SQLite aspects.
switch ( form.action ) {
case "addTask":
userDAO.addTask( form.taskName );
break;
case "deleteTask":
userDAO.deleteTasksByFilter( id = val( form.taskID ) );
break;
case "toggleTask":
task = userDAO
.getTasksByFilter( id = val( form.taskID ) )
.first()
;
userDAO.updateTask(
id = task.id,
isComplete = ! task.isComplete
);
break;
}
// BEST PRACTICE: Always refresh the page so that a CMD+R doesn't resubmit the
// form and perform the same action twice.
location(
url = "./tasks.cfm?userID=#encodeForUrl( user.id )#",
addToken = false
);
}
// ------------------------------------------------------------------------------- //
// ------------------------------------------------------------------------------- //
tasks = userDAO.getTasksByFilter();
</cfscript>
<cfoutput>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" type="text/css" href="./public/main.css" />
</head>
<body>
<h1>
#encodeForHtml( user.name )#
</h1>
<p>
← <a href="./index.cfm">Back to users</a>
</p>
<form method="post" action="./tasks.cfm?userID=#encodeForUrl( user.id )#">
<input
type="text"
name="taskName"
placeholder="Task..."
value=""
autofocus
autocomplete="off"
size="40"
data-1p-ignore
/>
<button type="submit" name="action" value="addTask">
Add Task
</button>
</form>
<h2>
Tasks
</h2>
<cfloop index="i" value="task" array="#tasks#">
<form method="post" action="./tasks.cfm?userID=#encodeForUrl( user.id )#" class="item">
<input
type="hidden"
name="taskID"
value="#encodeForHtmlAttribute( task.id )#"
/>
<button
type="submit"
name="action"
value="toggleTask">
Toggle
</button>
<span data-completed="#yesNoFormat( task.isComplete )#">
#dateFormat( task.createdAt, "yyyy-mm-dd" )#:
#encodeForHtml( task.name )#
</span>
<button
type="submit"
name="action"
value="deleteTask"
class="push-right">
Delete
</button>
</form>
</cfloop>
<cfif ! tasks.len()>
<p>
<em>No tasks.</em>
</p>
</cfif>
</body>
</html>
</cfoutput>
With this CFML code in place, I can now go in and start creating unique tasks for users:
Each of these users is being given their own SQLite database, which is auto-generated by the JDBC driver when we make the first SQL query against a given database. And, since I went it an touched all four task lists, I can see all four databases in the local file system:
bennadel@KXYFL7MY7G databases % pwd
/Users/bennadel/Sites/ColdFusion-Sqlite/databases
bennadel@KXYFL7MY7G databases % ls -l
total 80
-rw-r----- 1 bennadel staff 8192 May 12 07:00 master.db
-rw-r----- 1 bennadel staff 8192 May 12 07:15 user_1.db
-rw-r----- 1 bennadel staff 8192 May 12 07:16 user_2.db
-rw-r----- 1 bennadel staff 8192 May 12 07:15 user_3.db
-rw-r----- 1 bennadel staff 8192 May 12 07:15 user_4.db
How cool is that?!
I'm not sure how relevant SQLite is for a ColdFusion application; but, it's great to see that it's feasible for a ColdFusion application to create and connect to a SQLite database. Of course, passing on-the-fly datasource values to every CFQuery tag is probably not a scalable solution. Instead, I'd probably want to dynamically add datasources to the Admin; and then use simple datasource names per CFQuery tag. This way, you get proper connection pooling and all the other goodness that's baked into whatever ColdFusion does behind the scenes with datasources.
But, a fun exploration nonetheless!
Want to use code from this post? Check out the license.
Reader Comments
After writing this post, I went back and listened to a Changelog podcast on SQLite from 2016. It's a fun interview with the creator of SQLite - he wasn't even a database guy, he was a server admin. And, he created SQLite because the DB they were using at work wasn't always coming online after a server reboot.
In my
Dockerfile, I was saving the SQLite JAR file into the/bundlesfolder. I have updated that to use the/libfolder. So, I made this change, from:/opt/lucee/server/lucee-server/bundles... to:
/opt/lucee/server/lucee-server/context/libThis is based on this Lucee Dev forum post, in which they detail the locations of the lib folders inside a Docker based setup:
I am not entirely sure what the
bundlesfolder is - I get lost pretty quickly in the low-level server stuff.Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →