I first wrote about uploading and downloading files in 2015. Since then, many new features have been added to the driver, including streaming and buffer APIs for working with LOBs. It’s time to revisit the topic and put these new APIs to use. In this mini-series, I’ll demonstrate two different options for uploading and downloading files. The best choice for your use case will depend on several variables which will be discussed in the respective post.



You might be wondering, why use the database to store files (unstructured data) when you could just use the file system? This is a great question. Here are a few important reasons:

To better relate unstructured data with structured data. For example, you can associate images of receipts with detail lines in an expense reporting application.

To simplify backup/recovery. It often makes more sense to reuse your existing database backup/recovery strategies than to invent new ones for the file system.

To leverage advanced database security features. From end-to-end encryption to advanced access controls, Oracle Database has a lot to offer when it comes to security.

To index files and make them searchable. Many document types can be made searchable with “Google-like” search results via Oracle Text.

As you can see, there are some excellent use cases for storing files in the database, so let’s get started!

Contents:

Node.js app overview

The Node.js app is implemented two different ways: one buffers files in memory and the other uses streaming APIs. The code base for both solutions will pick up where the series on creating a REST API left off. Additional routes, controllers, and database APIs have been added as needed. See that series if you have questions about the target environment, how the files are organized, etc.

To demonstrate adding custom business logic, both apps enforce a maximum file size of 50 MB. Additionally, two error conditions are handled: the user cancels an upload (simulated by refreshing the page while uploading) and a database error occurs (can simulate a variety of ways, such as renaming the destination table).

Front-end app overview

Each of the Node.js apps will utilize the same minimalistic front-end demo app. This is possible because the only difference between the Node.js apps is the way that files are moved – the API exposed to the front-end apps is the same. The front-end app allows you to select and upload files, see what’s been uploaded, and download or delete files. Here’s what it looks like:



The app uses Bootstrap and vanilla JavaScript to do its work, which should make it easy to convert to your preferred JavaScript framework. Files are uploaded using XHR2’s native BLOB support. The eachLimit method in the Async.js library is used to limit the number of uploads to three at a time (server-side logic should enforce this limit if it’s truly important).

Testing an implementation

To test one of the applications, start by cloning the oracle-db-examples repo from GitHub and then change directories to oracle-db-examples/javascript/files-up-and-down. Using a tool like SQL Developer, run the create table command found in the sql_scripts/jsao_files.sql file for the HR schema.

Next, change directories into the implementation you’d like to run, either hr_app_buffering or hr_app_streaming. Set the appropriate environment variables (see config/database.js), run npm install to install the dependencies, and then run node . to start the app. Finally, open a browser and navigate to http://localhost:3000. From there you can use the front-end app to upload and download files.

Use the following links to learn more about a particular implementation for uploading and downloading files.