Datasource per environment ACF

Communities ColdBox HMVC
1

Hi all,

I’m looking for some guidance on how to best manage datasources on a per environment basis when it comes to deployments.

I have a Coldbox application that will be deployed to an already running instance of ACF 2023.

The source is in GitHub and I keep the entire code base in the repository.

Deploying to our staging environment is no problem because it uses the same datasource as the dev environment so all I need to do is get the code onto the server and everything works without issue.

The datasource will need to change when I deploy to production though.

Currently the datasource is set in application cfc:

this.datasource = “my_dev_db”;

This is nice because I don’t have to set the datasource attribute on cfquery tags for example.

Is there a way to setup my datasource in Coldbox on ACF that will make the config more flexible when changing environments?

I’m already using Coldbox environments for various other env specific configurations.

Is it possible to make use of environments to set the datasource also?

Perhaps not setting the datasource in application cfc and having a Coldbox setting for the datasource name and setting the datasource for each cfquery might work but I’d have to update many queries in that scenario.

Alternatively I’ve considered fetching the Coldbox environment setting in application cfc and switching the datasource there but I wasn’t sure if that could break the upgrade path and it doesn’t feel like the correct way to handle it. emphasized text

If you could point me in the right direction I’d appreciate any guidance on the best way to manage this.

Thanks in advance.

2

Same question for me here too. I’m running Lucee 6 and the latest version of Coldbox.

I have this code that seems to force me to set the datasource here and I’m not sure how to make it dynamic. I thought maybe injecting the datasource into Application.cfc, but that doesn’t feel right and likely wouldn’t work anyways unless I’m mistaken.

I too use environment settings in Coldbox.cfc @b_w , but when I remove “this.datasource”, I get an error because of my CBORM config.

Maybe @lmajano or @bdw429s know? Apologies for tagging you, but this would be good to understand how to do as I’m getting ready to deploy my app soon.

This is a snippet from my Application.cfc in the root of my site.

	this.datasource = "myds";
	this.ormEnabled       = true;
	this.ormSettings      = {
			cfclocation = "models",
			dbcreate    = "update",
			logSQL         = true,
			flushAtRequestEnd = false,
			autoManageSession = false,
			eventHandling       =  true,
			eventHandler      = "cborm.models.EventHandler"
	};
3

There are many ways to skin the cat. However, the way we do it is by using cfconfig and seeding the engine on a per environment basis. Meaning, each environment/tier we deploy to has a different cfconfig and secrets. This allows us to switch data sources, caches, etc.

4

I ended up not setting the datasource in Application.cfc and always use the datasource attribute/property on queries. E.g.

<cfquery … datasource=“#getSetting(“dataSource”)#”>…

queryExecute(‘SQL…’, params, { datasource : getSetting(“dataSource”) })

I used the custom settings in config/Coldbox.cfc and set the default setting to my production datasource name.

settings = {
…
dataSource: “production_datasource_name”
}

The I used the development settings override function to apply a different datasource on DEV:

function development() {
…
settings.dataSource = “dev_datasource_name”;
}

If using CommandBox , you have to add the datasources in .cfconfig.json and add env vars to dotenv to populate the connection string.

  1. box install commandbox-cfconfig
  2. box dotenv populate --new (creates the dotenv file)
  3. box install
  4. add env vars
  5. box start
5

I’ve done this before @b_w , but I don’t use the standard query syntax and have been using the qb (query builder) module which I find easier to use to get data in and out of my database and it doesn’t seem to have a datasource attribute or else I would just inject the datasource into my services and do as you’re doing.

6

Lots of good thoughts in this thread. For anyone reading this later, I thought I’d add my 2 cents

  1. Using EnvVars with CFConfig is pretty straight forward. In .cfconfig, everything you want replaced by a .env variable has the “${BLAH:default_if_blah_isnt_set}” syntax. In the .env file just put BLAH=MyValue on it’s own line and restart your server.

If you aren’t sure of all the keys and values to make a datasource in cfconfig, make the datasource using the Administrator and either use cfconfig the view it or, in Lucee, you an even see the JSON that defines the datasource right in the Administrator. Replace the bits you want to keep hidden with env variables and you’re good to go. I put an example down below but didn’t want to clutter the post.

  1. Putting this.datasource=MyDatasource in your Application.cfc will set that datasource for all queries by default in your whole app. This includes for QB.

  2. However, you can also put a “custom” datasource into QB like this:

qb
.from(“mytable”)
.get(options={datasource:myNonDefaultDatasource"});

  1. To sum up - sensitivedata in .env. Use the envVariables in the .cfconfig.json file. Set the default datasource in the Application.cfc OR drop the name of the datasource in the options={} in qb.

CFConfig node:

“datasources”:{
“myDatasource”:{
“allowAlter”:true,
“allowCreate”:true,
“allowDelete”:true,
“allowDrop”:true,
“allowGrant”:true,
“allowInsert”:true,
“allowRevoke”:true,
“allowSelect”:true,
“allowUpdate”:true,
“class”:“${CNE_DB_CLASS}”,
“custom”:“DATABASENAME=${CNE_DB_DBNAME}&sendStringParametersAsUnicode=false&SelectMethod=direct”,
“database”:“${CNE_DB_DBNAME}”,
“dbdriver”:“${CNE_DB_TYPE}”,
“dsn”:“jdbc:sqlserver://${CNE_DB_HOST}:${CNE_DB_PORT}”,
“host”:“${CNE_DB_HOST}”,
“password”:“${CNE_DB_PASS}”,
“port”:“${CNE_DB_PORT}”,
“sendStringParametersAsUnicode”:“false”,
“username”:“${CNE_DB_USER}”
}
},

.env file
CNE_DB_DBNAME=myDB
CNE_DB_PORT=1433
CNE_DB_USER=myUserName
CNE_DB_HOST=myDBHost
and so on.

1 Like