Brian Hanna

Posted on Jul 2, 2019

Connect to OracleDB from NodeJS?

#help #oracle #node

I currently use Java ADF REST web services to access the data in a bunch of legacy Oracle databases. It's slow, static, one dimensional, and a huge pain point. I use NodeJS for the application servers, so I was hoping to implement some cleaner, more dynamic APIs to interface with the Oracle databases there.

However, I cannot, for the life of me, figure out how to connect to this database from NodeJS. I can get to the database via SQL Developer. I have a Data Source set up in the integrated WebLogic Server that ships with JDeveloper for the current web services that (seemingly) uses all the same information. I have all the information: the hostname, the username, the password, the port, and the SID. I've gotten the InstantClient installed and added to my PATH on my Mac. No configuration of this information seems to work properly in NodeJS. I'm using node-oracledb version 3.1.2 on NodeJS v10.15.3. My connection object looks like this:

{
  user: 'my_username',
  password: 'my_password',
  connectString: 'app.company.com:port:database_name'
}

The above configuration results in the error:

Error: ORA-12545: Connect failed because target host or object does not exist

So something is clearly wrong with my connection string. I have tried multiple permutations on the connectString as well, including:

app.company.com:port/database_name
- Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
jdbc:oracle:thin:@app.company.com:port:database_name
- Works with SQL Developer and WebLogic Server Data Source, but produces error ORA-12514 when used in NodeJS

I'm grasping at straws. All of the information I've found has been so dense as to be basically useless. I'm not a Java developer. I've never worked heavily with Oracle databases before. I'm sure there is some strange, arcane configuration thing that I'm missing, but I don't even know what questions to ask. Literally any additional information would be incredibly useful.

Top comments (10)

Jaime Anguiano • Jul 2 '19

Hi Brian,

I think the problem is not the way you are connected from NodeJS to Oracle, but rather the problem is your Oracle host (in this case your macbook or similar). It is not so easy to have an Oracle environment in macOS.

I would try doing the following:
Use Docker to build an Oracle DB locally, so you keep the DB isolated regardless of the base operating system.
Use some instance in the cloud like AWS RDS.

With this we eliminate the possibility that the problem comes from "NodeJS".

Brian Hanna • Jul 2 '19

I have absolutely no control over the DB environment, unfortunately. It's an existing legacy DB that resides in an on-prem data center.

Jaime Anguiano • Jul 2 '19 • Edited

good...try with the embedded connection strings


oracledb.getConnection(
  {
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))"
  }

Brian Hanna • Jul 2 '19

I will definitely give this a try in the morning! Thanks!

Brian Hanna • Jul 2 '19

Jaime, THANK YOU!!! Your connection string was a huge help, I finally connected successfully! I ended up having to modify it a bit to match with a value I got from a coworkers tnsnames.ora entry file for the same database, so it ended up looking like this:

(DESCRIPTION=
  (ADDRESS=
    (COMMUNITY=subdomain.company.com)
    (PROTOCOL=TCP)
    (HOST=db.company.com)
    (PORT=port_number)
  )
  (CONNECT_DATA=
    (SID=database_name)
    (GLOBAL_NAME=database_name.company.com)
    (SERVER=DEDICATED)
  )
)

Ian B • Jan 5 '23

this worked for me, thank you so much.

nigilan • Jul 2 '19

Hi Brian,

Your connectString is not right. It should look like this :

connectString : 'IP_ADDRESS/service_name'

service_name could be orcl or any value present in tnsnames.ora file. The above one will work in all scenarios.

I see that you can connect through SQL Developer. There is another way to make connection, create an environment variable path called TNS_ADMIN and point it to the location of folder location of tnsnames.ora file. Now, you can just specify the entry in tnsnames.ora file in connectString.

Brian Hanna • Jul 2 '19

Thanks for the suggestion! I was able to resolve it using a modified version of the connection string provided by janguianof, but this approach seems like it might be useful when moving into a production environment. Thank you!

MarcGilbertSparte • Sep 9 '20

Hello nigilan,

I've been banging my heads on the walls for a way to access my oracle db without the wallet, and your reply is the closest I've came to finding what I need.

Except whenever I try to access it I get a "ORA-12537: TNS:connection closed".

I tried a lots of things, and here is what my string look like :
"db.eu-zurich-1.oraclecloud.com/service_name.atp.oraclecloud.com"

What am I doing wrong ? Some configuration perhaps ? Any help would be greatly appreciated.

Thanks in advance,

Marc.

James McPherson • Jul 2 '19

I'm glad I read through the comments before jumping in - the connect string needs to match the tnsnames.ora entry. It's a pain, but that's just how Oracle DBs do things :|

For the record, you can dive deep into the package at its homepage github.com/oracle/node-oracledb, and its maintainer is on the east coast of Australia.

DEV Community

Connect to OracleDB from NodeJS?

Top comments (10)

Read next

🥇 Top 10 Node.js Libraries for Web Development 🚀

🛠️ Battle of the Backend: Go vs Node.js vs Python – Which One Reigns Supreme in 2024? 🚀

EXECUTE IMMEDIATE in Oracle SQL

Best Practices for Bulk Optimization of Queries in PostgreSQL