New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

New series on creating a REST API with Node.js and Oracle Database #888

Closed
dmcghan opened this Issue Mar 29, 2018 · 25 comments

Comments

Projects
None yet
6 participants
@dmcghan
Member

dmcghan commented Mar 29, 2018

I've started a new series: Creating a REST API with Node.js and Oracle Database

The current plan is to touch on the following core topics before going into details on specific features and techniques:

  • Web Server Basics
  • Database Basics
  • Handling GET requests
  • Handling PUT, POST, and DELETE requests
  • Adding pagination, sorting, and filtering to GET request

I'm making the code available in our oracle-db-examples repo.

Your questions and comments are welcome!

@jgcmarins

This comment has been minimized.

Show comment
Hide comment
@jgcmarins

jgcmarins Mar 29, 2018

@dmcghan nice approach.
How about a series with GraphQL?

jgcmarins commented Mar 29, 2018

@dmcghan nice approach.
How about a series with GraphQL?

@dmcghan

This comment has been minimized.

Show comment
Hide comment
@dmcghan

dmcghan Mar 29, 2018

Member

@jgcmarins Thanks!

How about a series with GraphQL?

That's a great idea, though I'd have to learn a lot more about GraphQL myself! Maybe by the time I'm finished with this series, I'll be ready to take that one on... :)

Member

dmcghan commented Mar 29, 2018

@jgcmarins Thanks!

How about a series with GraphQL?

That's a great idea, though I'd have to learn a lot more about GraphQL myself! Maybe by the time I'm finished with this series, I'll be ready to take that one on... :)

@danilohgds

This comment has been minimized.

Show comment
Hide comment
@danilohgds

danilohgds Mar 29, 2018

@dmcghan I can help you with that one if you would lilke. I'm pretty sure your page will get more views than mine :)

danilohgds commented Mar 29, 2018

@dmcghan I can help you with that one if you would lilke. I'm pretty sure your page will get more views than mine :)

@dmcghan

This comment has been minimized.

Show comment
Hide comment
@dmcghan

dmcghan Mar 29, 2018

Member

@danilohgds

I can help you with that one if you would lilke.

Sure! Have you done some work with GraphQL and Oracle Database? How have you approached it so far?

Member

dmcghan commented Mar 29, 2018

@danilohgds

I can help you with that one if you would lilke.

Sure! Have you done some work with GraphQL and Oracle Database? How have you approached it so far?

@danilohgds

This comment has been minimized.

Show comment
Hide comment
@danilohgds

danilohgds Mar 29, 2018

@dmcghan It was easier than expected.

  1. We built a backend in node 6.9.5 that exposes the GraphQL endpoint to consumers.
  2. Then we built the GQL queries for all selects
  3. All mutations for DML statements.
  4. Repositories based on what data they retrieve, userRepository, BooksRepository etc.
  5. Build interfaces that describe your database data.
  6. Everything above is injected in GraphQL through the context, and the queries defined will route the graphQL request to the repository class:
  7. Call GraphQLAPI > Resolve function in GraphQLFieldConfig returns the oracle call as a Promise.
  8. Return from oracle call is an Observable Objects with the response.

I will upload something to my github repo so you can take a look. We also considered KNEX as a possible aid for easily building queries, but the lib itself is very new (not production ready if I may say so myself).

danilohgds commented Mar 29, 2018

@dmcghan It was easier than expected.

  1. We built a backend in node 6.9.5 that exposes the GraphQL endpoint to consumers.
  2. Then we built the GQL queries for all selects
  3. All mutations for DML statements.
  4. Repositories based on what data they retrieve, userRepository, BooksRepository etc.
  5. Build interfaces that describe your database data.
  6. Everything above is injected in GraphQL through the context, and the queries defined will route the graphQL request to the repository class:
  7. Call GraphQLAPI > Resolve function in GraphQLFieldConfig returns the oracle call as a Promise.
  8. Return from oracle call is an Observable Objects with the response.

I will upload something to my github repo so you can take a look. We also considered KNEX as a possible aid for easily building queries, but the lib itself is very new (not production ready if I may say so myself).

@dmcghan

This comment has been minimized.

Show comment
Hide comment
@dmcghan

dmcghan Mar 29, 2018

Member

@danilohgds Very interesting. Let me know when there's something on GitHub that we can look at. My thinking right now is that it would be very cool to create a GraphQL API on the HR schema, similar to what I'm doing with the REST API.

Member

dmcghan commented Mar 29, 2018

@danilohgds Very interesting. Let me know when there's something on GitHub that we can look at. My thinking right now is that it would be very cool to create a GraphQL API on the HR schema, similar to what I'm doing with the REST API.

@danilohgds

This comment has been minimized.

Show comment
Hide comment
@danilohgds

danilohgds Mar 29, 2018

@dmcghan I will work on it, I have the sandbox environment setup in my machine from previous posts you wrote :)

danilohgds commented Mar 29, 2018

@dmcghan I will work on it, I have the sandbox environment setup in my machine from previous posts you wrote :)

@jeffm13

This comment has been minimized.

Show comment
Hide comment
@jeffm13

jeffm13 Mar 29, 2018

We've also done a fair amount with graphql. In fact, most of our new clients for existing API's are now using graphql. We took a slightly different approach though. We already have RESTful APIs, giving us access to pretty much everything in our custom order management app. The resolvers in our graphql schema simply invoke those APIs. It's actually really simple, and probably done in fewer than 100 lines of code for a dozen API's--excluding the type definitions of course. Unfortunately, it's not destined for Github.

jeffm13 commented Mar 29, 2018

We've also done a fair amount with graphql. In fact, most of our new clients for existing API's are now using graphql. We took a slightly different approach though. We already have RESTful APIs, giving us access to pretty much everything in our custom order management app. The resolvers in our graphql schema simply invoke those APIs. It's actually really simple, and probably done in fewer than 100 lines of code for a dozen API's--excluding the type definitions of course. Unfortunately, it's not destined for Github.

@dmcghan

This comment has been minimized.

Show comment
Hide comment
@dmcghan

dmcghan Mar 29, 2018

Member

@jeffm13 Sounds interesting...

We already have RESTful APIs, giving us access to pretty much everything in our custom order management app.

How were those implemented?

The resolvers in our graphql schema simply invoke those APIs.

How did this benefit the consumers?

Unfortunately, it's not destined for Github.

Aww man... :)

Member

dmcghan commented Mar 29, 2018

@jeffm13 Sounds interesting...

We already have RESTful APIs, giving us access to pretty much everything in our custom order management app.

How were those implemented?

The resolvers in our graphql schema simply invoke those APIs.

How did this benefit the consumers?

Unfortunately, it's not destined for Github.

Aww man... :)

@cjbj

This comment has been minimized.

Show comment
Hide comment
@cjbj

cjbj Mar 29, 2018

Member

@danilohgds @jeffm13 To take the discussion in a different direction: what do you need/want from node-oracledb?

Member

cjbj commented Mar 29, 2018

@danilohgds @jeffm13 To take the discussion in a different direction: what do you need/want from node-oracledb?

@jgcmarins

This comment has been minimized.

Show comment
Hide comment
@jgcmarins

jgcmarins Mar 30, 2018

I can also share some cool stuff, based on what we've done in production.
In our case, we've implemented a generic Oracle loader/connection handler/pagination handler, based on Facebook's dataloader concept.
Those loaders are consumed by GraphQL resolvers which will compose querys, with Oracle's bind variables concept.
Then, GraphQL can solve multiple querys "asynchronously" and return a single result.

Most of our clients prefer this approach, which leads us to use Relay on frontend and make things more lazy, resulting on better users experiences.

jgcmarins commented Mar 30, 2018

I can also share some cool stuff, based on what we've done in production.
In our case, we've implemented a generic Oracle loader/connection handler/pagination handler, based on Facebook's dataloader concept.
Those loaders are consumed by GraphQL resolvers which will compose querys, with Oracle's bind variables concept.
Then, GraphQL can solve multiple querys "asynchronously" and return a single result.

Most of our clients prefer this approach, which leads us to use Relay on frontend and make things more lazy, resulting on better users experiences.

@cjbj

This comment has been minimized.

Show comment
Hide comment
@cjbj

cjbj Mar 30, 2018

Member

This is all really cool. And seeing the word 'production' is great. Keep letting us know what you are doing - this helps a lot when upper management are deciding resource allocation across products. Post here or send me private email. (Any accolades for node-oracledb are also welcome!)

Member

cjbj commented Mar 30, 2018

This is all really cool. And seeing the word 'production' is great. Keep letting us know what you are doing - this helps a lot when upper management are deciding resource allocation across products. Post here or send me private email. (Any accolades for node-oracledb are also welcome!)

@dmcghan

This comment has been minimized.

Show comment
Hide comment
@dmcghan

dmcghan Mar 30, 2018

Member

@jgcmarins That does sound interesting - I'd love to see an example!

Member

dmcghan commented Mar 30, 2018

@jgcmarins That does sound interesting - I'd love to see an example!

@danilohgds

This comment has been minimized.

Show comment
Hide comment
@danilohgds

danilohgds Mar 31, 2018

@dmcghan My experience comes from a production implementation, so does the pain of using GraphQL...

@cjbj Things, I'm looking for in the future

  1. More Test focused posts, an entire section dedicated on testing strategies that will cover classes that use node-oracledb
  2. Easy way to patch rows data to pretty Interface objects.
  3. Typescript examples
  4. Better coverage on RAC based environments, I know my production app gives me trouble every single time they shift RAC instances for maintenance.

danilohgds commented Mar 31, 2018

@dmcghan My experience comes from a production implementation, so does the pain of using GraphQL...

@cjbj Things, I'm looking for in the future

  1. More Test focused posts, an entire section dedicated on testing strategies that will cover classes that use node-oracledb
  2. Easy way to patch rows data to pretty Interface objects.
  3. Typescript examples
  4. Better coverage on RAC based environments, I know my production app gives me trouble every single time they shift RAC instances for maintenance.
@dmcghan

This comment has been minimized.

Show comment
Hide comment
@dmcghan

dmcghan Mar 31, 2018

Member

@danilohgds

More Test focused posts

I can do some test focused posts as part of the series I'm doing on REST.

Easy way to patch rows data to pretty Interface objects.

Can you explain this in more detail?

Typescript examples

I don't use TypeScript myself, though it's probably a good idea to add some examples...

Better coverage on RAC based environments

Great idea!

Member

dmcghan commented Mar 31, 2018

@danilohgds

More Test focused posts

I can do some test focused posts as part of the series I'm doing on REST.

Easy way to patch rows data to pretty Interface objects.

Can you explain this in more detail?

Typescript examples

I don't use TypeScript myself, though it's probably a good idea to add some examples...

Better coverage on RAC based environments

Great idea!

@jgcmarins

This comment has been minimized.

Show comment
Hide comment
@jgcmarins

jgcmarins Mar 31, 2018

@danilohgds, posts about tests sounds really good.

jgcmarins commented Mar 31, 2018

@danilohgds, posts about tests sounds really good.

@danilohgds

This comment has been minimized.

Show comment
Hide comment
@danilohgds

danilohgds Mar 31, 2018

@dmcghan

Here's an example:

Let's say I am fetching some rows from countries table in HR schema.
In order to get my rows into a interface object, I have to do some iteration and build such object.
I get it that the effort of doing the iteration is somewhat minimal, but what about extra options that lets you the outFormat is an interface.

 return connection.execute(countryQuery,params).then((result: any) =>{
                    console.log(result.rows[0]);
                    let cList:CountryList = {
                            countryId: result.rows[0][0],
                            countryName:  result.rows[0][1],
                            regionId:  result.rows[0][2]
                    }
                    doRelease(connection);
                    return cList;
                }
            );

///And I want to make the rows come as an array of the following interface 
interface CountryList {
    countryId: number;
    countryName: string;
    regionId: number;
}

danilohgds commented Mar 31, 2018

@dmcghan

Here's an example:

Let's say I am fetching some rows from countries table in HR schema.
In order to get my rows into a interface object, I have to do some iteration and build such object.
I get it that the effort of doing the iteration is somewhat minimal, but what about extra options that lets you the outFormat is an interface.

 return connection.execute(countryQuery,params).then((result: any) =>{
                    console.log(result.rows[0]);
                    let cList:CountryList = {
                            countryId: result.rows[0][0],
                            countryName:  result.rows[0][1],
                            regionId:  result.rows[0][2]
                    }
                    doRelease(connection);
                    return cList;
                }
            );

///And I want to make the rows come as an array of the following interface 
interface CountryList {
    countryId: number;
    countryName: string;
    regionId: number;
}
@cjbj

This comment has been minimized.

Show comment
Hide comment
@cjbj

cjbj Apr 2, 2018

Member

@danilohgds I know @anthony-tuininga and @dmcghan have talked about something like this. In Python cx_Oracle @anthony-tuininga has row factories and output type converters.

Member

cjbj commented Apr 2, 2018

@danilohgds I know @anthony-tuininga and @dmcghan have talked about something like this. In Python cx_Oracle @anthony-tuininga has row factories and output type converters.

@anthony-tuininga

This comment has been minimized.

Show comment
Hide comment
@anthony-tuininga

anthony-tuininga Apr 2, 2018

Member

One possibility would be to add support for a callable in the "outFormat" parameter to execute. This callable would accept one parameter which is the array of data that would ordinarily be returned. The return value of this function would be what is returned to the calling application instead. This would give you the ability to do anything you wished inside that callable and is the equivalent to cx_Oracle's row factory. Do you think this would address your request?

Member

anthony-tuininga commented Apr 2, 2018

One possibility would be to add support for a callable in the "outFormat" parameter to execute. This callable would accept one parameter which is the array of data that would ordinarily be returned. The return value of this function would be what is returned to the calling application instead. This would give you the ability to do anything you wished inside that callable and is the equivalent to cx_Oracle's row factory. Do you think this would address your request?

@dmcghan

This comment has been minimized.

Show comment
Hide comment
@dmcghan

dmcghan Apr 2, 2018

Member

@danilohgds I tried to create a mockup of what @anthony-tuininga was suggesting, but we're not clear about your example (keep in mind we are not TypeScript programmers).

You have an interface called CountryList, but its properties are scalars, not arrays.

In your comments, you say you have to do some iteration, but your example doesn't iterate.

Could you please clarify? Are you looking for the results to be either:

  • A custom list type - CountryList
  • An array of custom types - [Country, Country, Country, ...]
Member

dmcghan commented Apr 2, 2018

@danilohgds I tried to create a mockup of what @anthony-tuininga was suggesting, but we're not clear about your example (keep in mind we are not TypeScript programmers).

You have an interface called CountryList, but its properties are scalars, not arrays.

In your comments, you say you have to do some iteration, but your example doesn't iterate.

Could you please clarify? Are you looking for the results to be either:

  • A custom list type - CountryList
  • An array of custom types - [Country, Country, Country, ...]
@danilohgds

This comment has been minimized.

Show comment
Hide comment
@danilohgds

danilohgds Apr 2, 2018

@dmcghan

I took this example from my graphql github project. The idea would be to have some fetch option where you pass an interface, and the object returned is based on it.

The interface only defines the properties that you have in a object, for better readability purposes.
You can see that I am doing index acess on my row object to compose the Array of Countries.

My idea would be a simple clean way, where you provide the interface as a fetch parameter, and you get the object back without the hassle of having to iterate. The results would be an Array of custom types [ Country, Country, Country ]

Nonetheless, after some thought I think that this would make more sense to be developed as an additional lib, instead of being a feature from node-oracledb itself.

@anthony-tuininga That would certainly help, cleaner code for sure.

  getAllCountriesPooled(){
        return this.oraclePool.getConnection().then((connection:any) => {
            connection.clientIdentifier = 'getAllCountriesPooled';
            return connection.execute(allCountriesQuery).then((result: any) =>{
                    let cList:CountryList[] = Array<Countries>();                    
                    result.rows.forEach((dbRow:any) => {
                        cList.push({
                            countryId : dbRow[0],countryName : dbRow[1],regionId:dbRow[2]                            
                        })
                    });
                    doRelease(connection);
                    return cList;
                }
            );
        });
    }```

danilohgds commented Apr 2, 2018

@dmcghan

I took this example from my graphql github project. The idea would be to have some fetch option where you pass an interface, and the object returned is based on it.

The interface only defines the properties that you have in a object, for better readability purposes.
You can see that I am doing index acess on my row object to compose the Array of Countries.

My idea would be a simple clean way, where you provide the interface as a fetch parameter, and you get the object back without the hassle of having to iterate. The results would be an Array of custom types [ Country, Country, Country ]

Nonetheless, after some thought I think that this would make more sense to be developed as an additional lib, instead of being a feature from node-oracledb itself.

@anthony-tuininga That would certainly help, cleaner code for sure.

  getAllCountriesPooled(){
        return this.oraclePool.getConnection().then((connection:any) => {
            connection.clientIdentifier = 'getAllCountriesPooled';
            return connection.execute(allCountriesQuery).then((result: any) =>{
                    let cList:CountryList[] = Array<Countries>();                    
                    result.rows.forEach((dbRow:any) => {
                        cList.push({
                            countryId : dbRow[0],countryName : dbRow[1],regionId:dbRow[2]                            
                        })
                    });
                    doRelease(connection);
                    return cList;
                }
            );
        });
    }```
@cjbj

This comment has been minimized.

Show comment
Hide comment
@cjbj

cjbj May 16, 2018

Member

@danilohgds I'll close this announcement post now, since it was created > 1 month ago. If you want to propose feature ideas, open new issues - or new PRs, now that you have the OCA sorted!

Member

cjbj commented May 16, 2018

@danilohgds I'll close this announcement post now, since it was created > 1 month ago. If you want to propose feature ideas, open new issues - or new PRs, now that you have the OCA sorted!

@cjbj cjbj closed this May 16, 2018

@cjbj

This comment has been minimized.

Show comment
Hide comment
@cjbj

cjbj Jun 25, 2018

Member

For people finding this issue via search, I posted a quick-and-dirty demo using GraphQL and Oracle Database with node-oracledb at https://blogs.oracle.com/opal/demo%3a-graphql-with-node-oracledb

It won't teach you GraphQL & Oracle experts anything but may help beginners.

Member

cjbj commented Jun 25, 2018

For people finding this issue via search, I posted a quick-and-dirty demo using GraphQL and Oracle Database with node-oracledb at https://blogs.oracle.com/opal/demo%3a-graphql-with-node-oracledb

It won't teach you GraphQL & Oracle experts anything but may help beginners.

@cjbj

This comment has been minimized.

Show comment
Hide comment
@cjbj

cjbj Jun 26, 2018

Member

To add to the GraphQL breadcrumb trail, keep an eye on any Primsma GraphQL Oracle Connector work at prisma/prisma#1644

Member

cjbj commented Jun 26, 2018

To add to the GraphQL breadcrumb trail, keep an eye on any Primsma GraphQL Oracle Connector work at prisma/prisma#1644

@cjbj

This comment has been minimized.

Show comment
Hide comment
@cjbj

cjbj Jun 27, 2018

Member

Via @dmcghan, for a bigger & presumably better GraphQL demo, also check out https://github.com/unofficialoraclecloudhub/oracledb-graphql-demo

Member

cjbj commented Jun 27, 2018

Via @dmcghan, for a bigger & presumably better GraphQL demo, also check out https://github.com/unofficialoraclecloudhub/oracledb-graphql-demo

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment