Automatically build APIs from your Oracle database using DreamFactory

Table of contents

DreamFactory will save you and your company hundreds of hours by auto-generating an API automatically for every major SQL database, including Oracle. In this tutorial, we’re going to walk through the process of connecting DreamFactory to your Oracle database, review the features automatically built into DreamFactory that’ll help you manage and customize your API, and go over scripting support and security features.

View the full webinar here if you prefer a video format.

Oracle database support is a feature of DreamFactory’s Silver package — you can request a demo here.

Read more about instant API creation with Oracle.

Connecting your Oracle database to DreamFactory

For this tutorial, we’re going to make use of the free hosted version of DreamFactory. Make sure you have an Oracle database with some data in it to start, then sign up for a free 14 day hosted trial.

Once you’re in the DreamFactory dashboard, go to Services > Create and enter the credentials for your Oracle database. DreamFactory will automatically generate an API from your database, leaving the data in place.


Connect your DreamFactory account to your Oracle database.


After you choose Oracle, database fields will pop in below.


Configuration fields are available in the Config tab after choosing “Oracle” under the Info tab.


Your new Oracle service will show up under Manage after saving.

Overview of DreamFactory’s API automation features

Your API, documented with Swagger

DreamFactory will automatically introspect your database to know what schema is available — no need to do any additional coding or configuration.

Let’s check out the schema first. From your dashboard, click Schema and then select your Oracle server. Here, you can see the list of tables associated with your database.


Choosing your Oracle database from the Schema tab


Viewing the list of tables available in DreamFactory from your Oracle server

Note that you can add alias for your database — for example, you’d want to do this if your database is long or complex, or you don’t want to expose the table name for security reasons.

DreamFactory also automatically figures out the foreign key relationships within your database defined by default, which you can see at the bottom of the page:


Foreign key relationships are automatically found and shown.

Next, let’s check out API, documented with Swagger. Click on API Docs, then click on Oracle to see all the operations available generated for you in DreamFactory with Swagger.


Live API documentation within DreamFactory

DreamFactory gives you all the functionality you’d expect and want with an API, such as schema operations for creating, deleting, and updating tables, data operations like updating and deleting individual records, and more.

If you update or change your Oracle database, you don’t need to write any additional code to bring the tables in — you just need to clear your service cache and DreamFactory will automatically import new information about your database.

Testing your DreamFactory-powered API

The structure of your API is very SQL-like, and you can alter the fields and results by changing the offset, adding limits, setting up ordering, and more — all automatically offered by DreamFactory.

You can try out the API calls directly from this documentation. Below, we’re requesting the “contact” table under GET /oracle/_schema/{table_name} by adding “contact” to the table_name input box and clicking the “Try it now” button at the bottom.


Click the “Try it out!” button at the bottom to test API calls and see API responses.


The response is returned as JSON directly within the documentation.

The information is returned as JSON and can be filtered in many ways such as requesting only 10 records at a time). You can also pull in related data — in the related box, add “*”  (without quotes) and click the “Try it now” button again. DreamFactory will bring back your data, this time including foreign key relationships as well.


After putting in * in the related field, the response now includes related data.

Above, we’re filtering for last_name=Huang, which’ll bring back only records with that last name. This is a great way to test out your new API without having to go to the command line.

Virtual foreign key relationships using DreamFactory’s DataMesh

What if you don’t have an established foreign key relationship, but still have a logical relationship? For example, CRM data in one database, and order information in another. DreamFactory’s DataMesh feature can automatically associate relationships between two tables, regardless of database type.

To demonstrate this, we’re going to head to the Schema tab on on DreamFactory dashboard, and pick our table. Then, choose “Virtual Foreign Key” and “Foreign Reference Service” in our list of relationships.


Under Schema, choose your database and table, click your primary key, and you can specify Virtual Foreign Key and Foreign Reference Service to tie information together with DreamFactory’s DataMesh.

Then we can choose the reference service and the appropriate table, and DreamFactory will automatically query both databases to pull in information.

Back in the API Docs area, we can test the connection by clicking the “Try it now!” button. In the JSON results seen below, we can see that it’s pulling in information from the other database. No need to write any additional code!


Inspecting the documentation, we can see that MySQL’s information is automatically related.

Adding custom scripting with pre- and post-process scripts

DreamFactory also allows you to add pre- and post-process scripts in multiple programming languages, including JavaScript using V8Js, Node.js, PHP, and Python.


This post-process scripts sets up a call to Amazon’s SNS service.

For example, with pre-process scripts you can add additional validation checks. Leverage post-process scripts to set up external API calls to services like Amazon SNS to notify you via text-message of new API requests to your service. DreamFactory scripting support adds flexible ways to connect with different services, increasing the power of your automatically built API.

Security features

API security is critical and DreamFactory offers a strong set of options to lock down access to your data, including extensive user management.

On a basic level, DreamFactory supports roles and you can limit access to your APIs with those roles. You can choose to limit access to particular tables, or limit operations that the role has access to (such as GET or POST), limit access to only the API or only via scripts, and more.


Setting up allowed access under the Roles screen.

You can even add advanced filters, such as only allowing owners to view data, as seen above. All of these options can be changed on the fly through the DreamFactory configuration.

All this and more through DreamFactory’s automatic API generation for Oracle

As you can see, DreamFactory provides a complete and effective suite of tools to add an API to Oracle databases, saving you and your company hundreds of hours that you would spend coding these features otherwise.

For more information about DreamFactory, please see our main website, our documentation, and user guide. We also have more video walkthroughs available here.