How to specify schema while DB connection in typeORM for oracle DB

Question

I am a beginner with TypeORM, I am using oracle DB with node js in the backend. I have managed to connect the db with typeORM using createConnection(). But I am not able to specify the schema while creating connection

Another way is adding schema to each entity like this

@Entity({schema:"Users"})

But I cant use this as the schema will be changing according to the dev/test/prod environment. Only way can be specifying it during creating connection

I checked typeORM connection options documentation https://github.com/typeorm/typeorm/blob/master/docs/connection-options.md It has schema option for postgres and MySQL but it doesn't specify any options for oracle.

can't you use an environment variable to configure this?: @Entity({schema:"Users"}) ? — juztcode
– juztcode, Commented Jun 30, 2022 at 7:38

pmdba · Accepted Answer · 2021-12-23 23:18:44Z

1

In Oracle, "user" and "schema" are effectively the same thing. In most cases (unless you are a DBA or have other specific elevated privileges) you must connect directly to the user/schema you want to work with. There is no way to change user/schema once your connection is established; you would have to reconnect with new credentials.

If you do have elevated privileges to work with objects in schemas other than the one you connected to (e.g. create any table), you can do that by fully qualifying the object name (e.g. schema_name.table_name) in your DDL, DML, or SQL. You would not need to reconnect first.

In cases where there is a shared application schema/user, proxy user access can be configured by the DBA that will allow you to connect as another user using your personal credentials and avoid having to share common passwords or keys, but you would still be connected to a specific schema/user.

answered Dec 23, 2021 at 23:18

pmdba

7,0832 gold badges10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Christopher Jones Over a year ago

If there is access to the node-oracledb connection, the connection.currentSchema attribute can be used instead of explicitly changing SQL statements.

pmdba Over a year ago

Yes - this would change the default schema for purposes of fully qualified names, but would not change the user/schema to which you are actually connected. You would still need elevated privileges to access any schema other than the one you are connected to.

Roh1 Over a year ago

I tried using schema : schema_name in typeorm createConnection() and it seems to query the table like this schema_name.table_name . But oracle gives an error saying "table or view does not exist" so I might not have access to use object of another schema as @pmdba mentioned

pmdba Over a year ago

By default you will not have access to any other schemas. Access must be granted by the other user/schema account, or by a DBA.

Collectives™ on Stack Overflow

How to specify schema while DB connection in typeORM for oracle DB

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related