Creating In-Memory SQLite Databases Using JDBC In Lucee CFML

In my first look at connecting to SQLite databases using JDBC in Lucee CFML, I was creating physical database files and synchronizing them between my Docker container and my host machine. But, in an experimentation context, there may not be any need to persist the database state across container restarts. In such a context, I could have used SQLite's in-memory database mode to explore the SQLite space without having to worry about persisting data to disk.

View this code in my ColdFusion-SQLite project on GitHub.

If you recall from my previous post, I was able to connect to a SQLite database using the following JDBC (Java Database Connectivity) connection string:

jdbc:sqlite:/var/www/test.db

The act of opening this database connection implicitly caused the SQLite driver (provided by Xerial) to create the test.db file on the server.

If I didn't want to create a physical file, I could have used this connection string:

jdbc:sqlite::memory:

By using :memory: as the "path", the SQLite driver will represent the database in memory; but, it will never persist the database to disk (for all intents and purposes). Which means, this database disappears when the ColdFusion server restarts or when the last connection to the datasource is closed.

This is great; but, there can only be one :memory: database in use at one time (due to the generic definition of the connection string). If I needed to create multiple in-memory databases at the same time, such as in my previous demo, I have to use a different style of connection string.

In order to give the temporary, in-memory SQLite database a unique identifier, I have to use the file: scheme. This allows me to:

1. Provide a path to a uniquely named database.

2. Use a query-string flag to denote the mode.

With this approach, I use a connection string that looks like this:

jdbc:sqlite:file:my-database.db?mode=memory

In this connection string, the ?mode=memory query-string parameter tells SQLite to represent the database entirely in-memory. Which means, you can think of the filepath, my-database.db, as being nothing more than the database "identifier". And, now that we can uniquely identify an in-memory database, we can create multiple connection pools, each to a different in-memory database, within the same ColdFusion application.

To demonstrate, I'm going to create two in-memory databases—temp-1.db and temp-2.db—and then read and write to both SQLite databases at the same time.

<cfscript>

	// To create an in-memory SQLite database, you can use `jdbc:sqlite::memory:` as your
	// connection string. However, if you need to have multiple in-memory databases, you
	// can use the `file:` scheme and the `?mode=memory` query-string flag. In this case,
	// we're going to create two temporary, in-memory databases and trying writing to and
	// reading from each of them.
	// --
	// NOTE: I'm using a full directory path to a server directory, but this isn't
	// strictly necessary - the databases will be created in-memory no matter what path
	// you point to. However, if I change from "?mode=memory" to "?mode=rwc" (read, write,
	// create), then a physical database file will be created.
	temp1Datasource = {
		class: "org.sqlite.JDBC",
		connectionString: "jdbc:sqlite:file:/var/www-databases/temp-1.db?mode=memory",
		idleTimeout: 1 // In minutes.
	};
	temp2Datasource = {
		class: "org.sqlite.JDBC",
		connectionString: "jdbc:sqlite:file:/var/www-databases/temp-2.db?mode=memory",
		idleTimeout: 1 // In minutes.
	};

	// NOTE: The in-memory database will be dropped when the last connection to it is
	// closed. In this, with an `idleTimeout` of 1-minute, these databases will cease to
	// exist after a minute of no activity.

</cfscript>

<!--- Parameterize a table in each of the temporary, in-memory SQLite databases. --->
<cfquery datasource="#temp1Datasource#">
	CREATE TABLE IF NOT EXISTS `tokens` (
		`value` TEXT NOT NULL
	);
</cfquery>
<cfquery datasource="#temp2Datasource#">
	CREATE TABLE IF NOT EXISTS `tokens` (
		`value` TEXT NOT NULL
	);
</cfquery>

<!--- Insert a new token into each in-memory SQLite table. --->
<cfquery datasource="#temp1Datasource#">
	INSERT INTO tokens
		( value )
	VALUES
		( 'temp1-hello' ),
		( 'temp1-world' )
	;
</cfquery>
<cfquery datasource="#temp2Datasource#">
	INSERT INTO tokens
		( value )
	VALUES
		( 'temp2-foo' ),
		( 'temp2-bar' )
	;
</cfquery>

<!--- Query for values from each in-memory SQLite database. --->
<cfquery name="temp1Results" datasource="#temp1Datasource#">
	SELECT * FROM tokens ;
</cfquery>
<cfquery name="temp2Results" datasource="#temp2Datasource#">
	SELECT * FROM tokens ;
</cfquery>

<!--- Output the results side-by-side using CSS Flexbox. --->
<body style="display: flex ; gap: 20px ; align-items: flex-start ;">
	<cfdump
		label="SQLite: Temp-1 Database"
		var="#temp1Results#"
	/>
	<cfdump
		label="SQLite: Temp-2 Database"
		var="#temp2Results#"
	/>
</body>

The database schema in each database is the same—just a table of arbitrary tokens. But, in one database, the tokens are prefixed with temp1 and in the other database, the tokens are prefixed with temp2.

Now, if we run this Lucee CFML code and refresh the page a few times, we get the following output:

As you can see, the data in each in-memory SQLite database persists across page requests due to the connection pooling; but, each CFQuery tag is hitting a different database (due to the unique identification provided in the relevant JDBC connection strings).

In this demo, I'm using an idleTimeout: 1, which means that ColdFusion will close the connection pool if no queries are run within a 1-minute time-window. This is why the data is persisted if I refresh the page with quick succession. And, why if I go to refresh the page after a few minutes of no activity, the data resets:

This is really cool! This takes SQLite, which is already an extremely light-weight database implementation, and allows it to be even more light-weight by removing the physical file constraint. This seems prefect for small demos and other non-production contexts.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/4655