How to connect On-Premise MySQL database using SAP CAPM NodeJS app?

ramana_saps4hana43 — Sun, 09 Mar 2025 04:57:19 GMT

Hi Everyone,

we have a on-premise My SQL database where the data exists and we need to fetch data from MySQL DB and store into HANA Cloud DB using CAPM NodeJS application on interval basis liek once in a day.

I'm able to connect to On-Premise MYSQL DB in Locally installed VS Code using NodeJS but when I'm trying the same in SAP BAS then getting error like "Error connecting to MySQL: Error: connect ECONNREFUSED 127.0.0.1:3306". I have update my etc file locally but still error persists.

Also I maintained Cloud Connector configuration as below

also created a destination on top of it in BTP cockpit.

I created a function in service.js file under srv folder

const { cds } = require('@sap/cds');

const { getDestination } = require('@sap-cloud-sdk/connectivity');

const mysql = require('mysql2/promise');

module.exports = async (srv) => {

srv.on('loadReadingsData', async (req) => {

try {

// Get the destination from BTP Cockpit

const destination = await getDestination({ destinationName: 'MySQLDB' });

const { URL, username, password, database } = destination.originalProperties;

console.log("connecting to EnerChart DB start");

// // Connect to the MySQL database using the details from the destination

const connection = await mysql.createConnection({

host: '127.0.0.1',

port: 3306,

// host: URL,

user: username,

password: password,

database: database,

// ssl: {

// ca: fs.readFileSync('/path/to/ca-cert.pem'),

// cert: fs.readFileSync('/path/to/client-cert.pem'),

// key: fs.readFileSync('/path/to/client-key.pem'),

// ciphers: 'TLS_AES_128_GCM_SHA256',

// rejectUnauthorized: true

// }

});

// console.log("Connected to MySQL!", connection.threadId);

// Query MySQL (example)

// const [rows, fields] = await connection.execute('SELECT * FROM MeterReadings');

// console.log(rows.length);

// Close the connection

// await connection.end();

} catch (error) {

console.error('Error connecting to MySQL:', error);

}

})

}

Not able to connect with URL : http:mysql:3306 so i tried with direct IP address above.

Still not able to connect to On-premise MySQL DB. Also Can anyone suggest any approach for better security like Certificates or principle propagation etc.. in both BTP & MySQL DB to implement.

Can anyone please help/guide me to achieve the above scenario with best practices.

Thank you in advance.

Regards,

Ramana.

Re: How to connect On-Premise MySQL database using SAP CA...

Yogananda — Sun, 09 Mar 2025 08:14:06 GMT

@ramana_saps4hana43

MySQL is not supported and will not be made available for support for CAP based applications with Nodejs or Java based..

Refer List of supported and Built in Database support for CAP

https://cap.cloud.sap/docs/guides/databases

Re: How to connect On-Premise MySQL database using SAP CA...

ramana_saps4hana43 — Sun, 09 Mar 2025 11:04:04 GMT

Hi yogananda,

Re: How to connect On-Premise MySQL database using SAP CA...

ramana_saps4hana43 — Sun, 09 Mar 2025 11:06:02 GMT

Hi yogananda, Thanks for your kind response. I'm not using MySQL DB as backend DB for CAP service rather I'm fetching data from MySQL DB and storing into HANA Cloud DB. As NodeJS can connect to any DB to read and same I'm trying. I'll insert the data finally into HANA Cloud DB and UI on top of it for all CRUD and Analytics.

Re: How to connect On-Premise MySQL database using SAP CA...

Yogananda — Sun, 09 Mar 2025 11:10:00 GMT

thanks for altering the questions posted with provided more revised content... As I see your Destination is still not be able reachable to the cloud to get the data from your local db

Question Re: How to connect On-Premise MySQL database using SAP CA... in Technology Q&A

How to connect On-Premise MySQL database using SAP CAPM NodeJS app?

Re: How to connect On-Premise MySQL database using SAP CA...

Re: How to connect On-Premise MySQL database using SAP CA...

Re: How to connect On-Premise MySQL database using SAP CA...

Re: How to connect On-Premise MySQL database using SAP CA...