Download ColdFusion Datasource Templates (MySQL SQL Server PostgreSQL)

Whether you’re bootstrapping a new CFML application or standardizing multiple environments, downloadable ColdFusion Datasource templates remove guesswork from connecting to MySQL, SQL Server, and PostgreSQL. This package provides ready-to-use configurations, secure JDBC connection strings, and copy-paste examples you can import into Adobe ColdFusion (2018/2021/2023) or adapt for Lucee. The result is faster setup, fewer driver errors, and consistent, production-ready DSNs across teams.

---

Overview

These ColdFusion Datasource templates are pre-validated JSON and CFML snippets that define DSNs with sane defaults for pooling, health checks, and SSL. Each template contains a fully formed JDBC URL, recommended driver class, validation query, and Performance-safe pooling parameters. You’ll also find scripts and instructions for importing them via the ColdFusion CLI (cfsetup), setting them manually in the Administrator (CFAdmin), or using the Admin API in code.

The goal is to help developers and DevOps engineers establish reliable database connections quickly—without sifting through driver documentation, forum threads, or mismatched environment settings.

---

What You’ll Get

• A single ZIP archive: coldfusion-datasource-templates.zip
• File structure:
  • /cfsetup/adobe-cf2021/mysql.json
  • /cfsetup/adobe-cf2021/sqlserver.json
  • /cfsetup/adobe-cf2021/postgresql.json
  • /cfsetup/adobe-cf2018/… (same three)
  • /cfadmin-api/create-dsn-mysql.cfm (example Admin API usage)
  • /cfadmin-api/create-dsn-sqlserver.cfm
  • /cfadmin-api/create-dsn-postgresql.cfm
  • /examples/cfquery/verify-connection.cfm
  • /docker/snippets/docker-compose.yml (env vars + CF + DB)
  • /docs/validation-queries.md
  • /docs/jdbc-ssl-notes.md (truststore, certificates, encrypt flags)
  • /docs/Troubleshooting.md
• Detailed README with:
  • Steps for importing the templates using cfsetup
  • Manual setup instructions for the ColdFusion Administrator
  • Notes on driver versions and JDBC URLs
  • Security guidelines for secrets and SSL

Highlights:

• Templates for MySQL 5.7/8.0 and MariaDB 10.x (com.mysql.cj.jdbc.Driver)
• Templates for SQL Server 2017/2019/2022 (com.microsoft.sqlserver.jdbc.SQLServerDriver)
• Templates for PostgreSQL 12–16 (org.postgresql.Driver)
• Example code using and a minimal Application.cfc

---

Supported Environments

Component	Versions	Notes
Adobe ColdFusion	2018, 2021, 2023	cfsetup supported in 2021+
Lucee (adaptation)	5.3, 6.x	Use this.datasources or admin; see docs
Java (JDK)	11, 17	Match CF’s supported JDK
MySQL/MariaDB	5.7/8.0 / 10.x	SSL and time zone options included
SQL Server	2017–2022	Encryption enabled by default
PostgreSQL	12–16	Prefer verify-full with SSL

Tip: Adobe ColdFusion typically ships with JDBC drivers for these databases, but the templates note when you may need to add or update JARs for newer versions.

---

Benefits

• Speed: Drop in a JSON or CFML snippet and have a working DSN in minutes.
• Reliability: Pre-tested JDBC URLs, validation queries (e.g., SELECT 1), and connection pooling defaults reduce runtime errors.
• Security: Examples include SSL/TLS, encrypted connections, and patterns for environment variable secrets.
• Consistency: Standardized DSNs across local, staging, and production environments, including cloud databases (AWS RDS, Azure SQL, Google Cloud SQL).
• Maintainability: Clear separation of concerns: Configuration via cfsetup, code via Admin API or UI, and environment overrides via variables.

---

How to Use the Downloaded Templates

Option A: Import via ColdFusion cfsetup (Adobe CF 2021+)

1. Unzip coldfusion-datasource-templates.zip.

2. Open a terminal and authenticate cfsetup to your server:

   cfsetup login add –name local –host http://127.0.0.1:8500 –user admin –password “yourAdminPassword”

3. Validate a template before applying (example for MySQL on CF2021):

   cfsetup validate cfsetup/adobe-cf2021/mysql.json

4. Apply the Configuration:

   cfsetup apply cfsetup/adobe-cf2021/mysql.json –target local

5. Restart the CF service if prompted.

6. Test with the provided verify-connection.cfm:

   http://localhost:8500/examples/cfquery/verify-connection.cfm?dsn=mysql_demo

Notes:

• Replace DSN names, hosts, and credentials in the JSON before applying.
• You can use environment variable references (e.g., “password”: “env:CF_MYSQL_PW”).

---

Option B: Use the Admin API (CFML)

Use the provided /cfadmin-api/create-dsn-.cfm scripts. They demonstrate the CFIDE.adminapi. components to add DSNs programmatically.

Example pattern (simplified):

admin = createObject(“component”, “CFIDE.adminapi.administrator”);
admin.login(“yourAdminPassword”);

ds = createObject(“component”, “CFIDE.adminapi.datasource”);

// Example: create or update a PostgreSQL DSN
dsnName = “pg_demo”;
jdbcUrl = “jdbc:postgresql://dbhost:5432/mydb?sslmode=require”;
username = “cfuser”;
password = server.system.environment.CF_PG_PW; // env var
driverClass = “org.postgresql.Driver”;

ds.setOther(
name=dsnName,
driver=”PostgreSQL”,
url=jdbcUrl,
username=username,
password=password,
clazz=driverClass,
pooling=true,
maxpooledstatements=100,
validationQuery=”SELECT 1″
);

Important: Parameter names may vary slightly by CF version; use the exact examples in the package for your target version.

---

Option C: Configure Manually in the ColdFusion Administrator

1. Go to Data & Services > Data Sources.
2. Enter a DSN name (e.g., mysql_demo).
3. Choose the appropriate driver (MySQL, Microsoft SQL Server, or PostgreSQL).
4. Paste the JDBC URL and other settings from the relevant template:
   • Validation query: SELECT 1
   • Min/Max pool size: start with min 5, max 50
   • Login/Connection timeout: 30 seconds
5. Enter username and password.
6. Enable SSL/encryption if supported and the database requires it.
7. Submit and verify connection.

---

Lucee-specific Adaptation

While the templates target Adobe CF, you can adapt them for Lucee:

• Configure via the Lucee Administrator or define this.datasources in Application.cfc (Lucee supports in-app definitions).
• Use the same JDBC URLs, driver classes, and validation queries from the templates.
• Restart Lucee after adding/updating drivers.

---

Configuration Details by Database

MySQL / MariaDB

• Driver: com.mysql.cj.jdbc.Driver

• Sample JDBC URL:

  jdbc:mysql://dbhost:3306/mydb?useSSL=true&requireSSL=true&serverTimezone=UTC&allowPublicKeyRetrieval=false

• Validation query: SELECT 1

• Notes:

  • For Amazon RDS or Cloud SQL, ensure the CA certificate is in your Java truststore if using verifyCertificate.
  • Use serverTimezone=UTC to prevent timestamp warnings.

---

Microsoft SQL Server

• Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

• Sample JDBC URL:

  jdbc:sqlserver://dbhost:1433;databaseName=mydb;encrypt=true;trustServerCertificate=false;loginTimeout=30;

• Validation query: SELECT 1

• Notes:

  • If using Azure SQL Database, encrypt=true and trustServerCertificate=false are recommended.
  • Integrated security requires native libraries and is platform-specific; prefer SQL auth for portability.

---

PostgreSQL

• Driver: org.postgresql.Driver

• Sample JDBC URL:

  jdbc:postgresql://dbhost:5432/mydb?sslmode=require&ApplicationName=CFMLApp

• Validation query: SELECT 1

• Notes:

  • Consider sslmode=verify-full with proper certificates in production.
  • ApplicationName helps identify connections in pg_stat_activity.

---

Best practices

• Prefer environment variables for credentials (avoid hardcoding). Templates use env: Syntax where possible.
• Enable encryption/SSL on the JDBC URL and manage certificates in the Java truststore (docs/jdbc-ssl-notes.md).
• Set a validation query to avoid stale connections in pools.
• Right-size connection pooling:
  • min: 5–10
  • max: based on DB capacity (commonly 20–100)
• Use read-only DSNs for reporting when appropriate.
• Keep JDBC drivers up to date and aligned with DB versions.
• Test with the provided verify-connection.cfm after any change.
• For containerized deployments (Docker/Kubernetes), store secrets in orchestrator-managed secret stores and inject via env vars.

Pros:

• Faster Onboarding, fewer runtime errors, consistent configs.
  Cons:
• You still must manage certs/secrets and adapt to network/firewall constraints.

---

Benefits and Use Cases

• Rapid Environment setup for local development, CI/CD, and staging.
• Standardize DSN creation across multiple teams and Microservices.
• Reduce production incidents caused by misconfigured JDBC URLs or disabled SSL.
• Smooth migrations to cloud databases (AWS RDS, Azure SQL, Cloud SQL) with recommended flags.
• Simplify Infrastructure-as-code by using cfsetup JSON templates in pipelines.

By delivering secure defaults, tuned pooling, and validated JDBC strings, these templates help you avoid the classic pitfalls that cause intermittent failures, connection leaks, or Performance bottlenecks.

---

Troubleshooting and Tips

• Connection refused/timeouts:
  • Check firewall rules and security groups for DB ports (3306, 1433, 5432).
• SSL handshake failures:
  • Import DB CA cert into the JVM truststore and ensure encrypt/sslmode flags align with server policy.
• Driver not found:
  • Confirm the appropriate JDBC driver JAR is on the CF classpath; restart the service after adding.
• Authentication errors:
  • Verify user privileges and that the user’s default database exists and is reachable.
• Intermittent pool errors:
  • Ensure validation query is set and that idle timeouts are not too aggressive.

---

Key Takeaways

• Use the templates to create secure, production-ready DSNs for MySQL, SQL Server, and PostgreSQL in minutes.
• Import via cfsetup for Automation, use the Admin API for scripted provisioning, or configure via CFAdmin manually.
• The package includes JDBC URL examples, validation queries, pooling defaults, and SSL guidance to minimize connection issues.
• Works across Adobe ColdFusion 2018/2021/2023 and adapts easily to Lucee.

---

FAQ

How do I change the DSN name or database host before importing?

Edit the JSON file in /cfsetup/adobe-/ and replace the values for name, jdbcurl, username, and password. You can set “password”: “env:MY_SECRET” to pull from an environment variable.

Do I need to install JDBC drivers manually?

Adobe ColdFusion typically includes drivers for MySQL, SQL Server, and PostgreSQL. If you’re targeting newer DB versions, see the README for the recommended driver JAR and where to place it. Always restart CF after adding a driver.

Can I use these templates with Lucee?

Yes, adapt the JDBC URL and driver class to Lucee’s datasource configuration. You can define this.datasources in Application.cfc on Lucee or use the Lucee Administrator. The templates provide the essential settings.

What’s the best validation query?

For all three databases, SELECT 1 is lightweight and reliable. It ensures the pool can detect and discard dead connections.

How should I handle SSL certificates?

Import the issuing CA or server certificate into the JVM truststore used by ColdFusion. Then enable SSL in the JDBC URL (encrypt=true for SQL Server, sslmode=require or verify-full for PostgreSQL, useSSL=true for MySQL). The package includes docs/jdbc-ssl-notes.md with step-by-step guidance.