Creating In-Reminiscence SQLite Databases Utilizing JDBC In Lucee CFML

In my first have a look at connecting to SQLite databases utilizing JDBC in Lucee CFML, I used to be creating bodily database recordsdata and synchronizing them between my Docker container and my host machine. However, in an experimentation context, there might not be any must persist the database state throughout container restarts. In such a context, I may have used SQLite’s in-memory database mode to discover the SQLite area with out having to fret about persisting information to disk.

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

For those who recall from my earlier submit, I used to be ready to hook up with a SQLite database utilizing the next JDBC (Java Database Connectivity) connection string:

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

The act of opening this database connection implicitly precipitated the SQLite driver (offered by Xerial) to create the check.db file on the server.

If I did not need to create a bodily file, I may have used this connection string:

jdbc:sqlite::reminiscence:

Through the use of :reminiscence: because the “path”, the SQLite driver will signify the database in reminiscence; however, it can by no means persist the database to disk (for all intents and functions). Which suggests, this database disappears when the ColdFusion server restarts or when the final connection to the datasource is closed.

That is nice; however, there can solely be one :reminiscence: database in use at one time (because of the generic definition of the connection string). If I wanted to create a number of in-memory databases on the identical time, akin to in my earlier demo, I’ve to make use of a unique fashion of connection string.

As a way to give the non permanent, in-memory SQLite database a singular identifier, I’ve to make use of the file: scheme. This enables me to:

1. Present a path to a uniquely named database.

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

With this method, I exploit a connection string that appears like this:

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

On this connection string, the ?mode=reminiscence query-string parameter tells SQLite to signify the database solely in-memory. Which suggests, you possibly can consider the filepath, my-database.db, as being nothing greater than the database “identifier”. And, now that we will uniquely determine an in-memory database, we will create a number of connection swimming pools, every to a unique in-memory database, inside the identical ColdFusion software.

To show, I will create two in-memory databases—temp-1.db and temp-2.db—after which learn and write to each SQLite databases on the identical time.

<cfscript>

	// To create an in-memory SQLite database, you should use `jdbc:sqlite::reminiscence:` as your
	// connection string. Nonetheless, if you could have a number of in-memory databases, you
	// can use the `file:` scheme and the `?mode=reminiscence` query-string flag. On this case,
	// we'll create two non permanent, in-memory databases and making an attempt writing to and
	// studying from every of them.
	// --
	// NOTE: I am utilizing a full listing path to a server listing, however this is not
	// strictly vital - the databases shall be created in-memory it doesn't matter what path
	// you level to. Nonetheless, if I modify from "?mode=reminiscence" to "?mode=rwc" (learn, write,
	// create), then a bodily database file shall be created.
	temp1Datasource = {
		class: "org.sqlite.JDBC",
		connectionString: "jdbc:sqlite:file:/var/www-databases/temp-1.db?mode=reminiscence",
		idleTimeout: 1 // In minutes.
	};
	temp2Datasource = {
		class: "org.sqlite.JDBC",
		connectionString: "jdbc:sqlite:file:/var/www-databases/temp-2.db?mode=reminiscence",
		idleTimeout: 1 // In minutes.
	};

	// NOTE: The in-memory database shall be dropped when the final connection to it's
	// closed. On this, with an `idleTimeout` of 1-minute, these databases will stop to
	// exist after a minute of no exercise.

</cfscript>

<!--- Parameterize a desk in every of the non permanent, in-memory SQLite databases. --->
<cfquery datasource="#temp1Datasource#">
	CREATE TABLE IF NOT EXISTS `tokens` (
		`worth` TEXT NOT NULL
	);
</cfquery>
<cfquery datasource="#temp2Datasource#">
	CREATE TABLE IF NOT EXISTS `tokens` (
		`worth` TEXT NOT NULL
	);
</cfquery>

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

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

<!--- Output the outcomes side-by-side utilizing CSS Flexbox. --->
<physique fashion="show: flex ; hole: 20px ; align-items: flex-start ;">
	<cfdump
		label="SQLite: Temp-1 Database"
		var="#temp1Results#"
	/>
	<cfdump
		label="SQLite: Temp-2 Database"
		var="#temp2Results#"
	/>
</physique>

The database schema in every database is similar—only a desk of arbitrary tokens. However, in a single database, the tokens are prefixed with temp1 and within the different database, the tokens are prefixed with temp2.

Now, if we run this Lucee CFML code and refresh the web page a number of instances, we get the next output:

As you possibly can see, the info in every in-memory SQLite database persists throughout web page requests because of the connection pooling; however, every CFQuery tag is hitting a unique database (because of the distinctive identification offered within the related JDBC connection strings).

On this demo, I am utilizing an idleTimeout: 1, which implies that ColdFusion will shut the connection pool if no queries are run inside a 1-minute time-window. That is why the info is continued if I refresh the web page with fast succession. And, why if I am going to refresh the web page after a couple of minutes of no exercise, the info resets:

That is actually cool! This takes SQLite, which is already an especially lightweight database implementation, and permits it to be much more lightweight by eradicating the bodily file constraint. This appears prefect for small demos and different non-production contexts.

Need to use code from this submit?
Try the license.

https://bennadel.com/4655