Coffee has always been a passion of mine. Last night while watching the Pat Mcafee Show I decided to channel that passion into something productive—a simple coffee brew tracking app. This blog will walk you through how to build a Node.js app and easily store your data using a free Oracle Cloud Database.
Whether you’re a fan of coffee, code, or just someone curious about blending the two, this tutorial will guide you step-by-step. By the end, you’ll have a working app to track your brews and a free cloud database for any other future projects.
Before You Begin
Here are a few things you’ll need to set up:
1. An Oracle Cloud Database
If you don’t already have one, check out my previous post:
Before we begin, all of the code for this project is available on my GitHub
Step 1: Setting up the app
Create Your Project Folder
Choose a location on your computer, create a new folder, and open it in VS Code.
Initialize the Project
Open the terminal in VS Code (Terminal > New Terminal) and run:
npm init -yThis command sets up the package.json file, which will manage your dependencies
Install Required Packages
Run the following to install the necessary libraries:
npm install oracledb body-parser dotenv express Here’s a quick breakdown of what these do:
• oracledb: Lets Node.js interact with Oracle Databases.
• body-parser: Parses incoming JSON requests.
• dotenv: Loads environment variables from a .env file.
• express: A popular web framework for Node.js.
Organize Your Project
Create a structure like this:
Step 2. Connecting to the Oracle Database
Now, let’s connect your app with the Oracle Database. This step involves setting up your database connection and creating a table to store coffee brew data.
Install SQL Developer Extension
In VS Code, install the Oracle SQL Developer extension from the Extensions Marketplace.
Set Up Access Control
• Go to your Oracle Autonomous Database (ADB) dashboard.
• Select your database instance and edit the Access Control List (ACL) to allow your IP.
Add your IP address by clicking Add my IP address, then save.
From the ADB dashboard click Edit next to “Mutual TLS (mTLS) authentication: Required”
Uncheck the box and click save. Once the changes are made, click Database connection.
Under the connection string section, switch to TLS authentication.
Copy the connect string (we’ll use “*_high”) and head back to VSCode.
Configure the Database Connection in VS Code
Use the SQL Developer extension to create a new connection with the following details:
Username: ADMIN or an existing database user you already have
Password: The database user's password
Click save password to make signing in easier next time
Connection Type: Change the type to TNS
Type: change the type to connection Identifier
Connection Identifier: Paste the TLS Connection string we just found above.
Test the connection, then save it.
Once connected, open a SQL Worksheet and create the coffee brews table. Hover over the connection and click the 'Open SQL Worksheet Button'
Create the Coffee Brews Table
Use the SQL Worksheet in VS Code to execute this command:
CREATE TABLE if not exists coffee_brews (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
brew_method VARCHAR2(255),
bean_origin VARCHAR2(255),
grind_size VARCHAR2(255),
temperature NUMBER,
brew_time NUMBER,
notes VARCHAR2(4000),
rating NUMBER,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Pro Tip: While using the ADMIN user for this demo is fine, it’s best practice to use a less-privileged user in production.
Wait! Why not store the data as a JSON object?
This is a very valid question. Oracle Database has supported the JSON datatype natively since 21c and supported JSON using the IS JSON constraint since 12c. You could easily create the same application and store the brews as a JSON object like this:
CREATE TABLE coffee_brews (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
brew JSON (OBJECT) -- supported in version 21c and up
);The reason I'm not doing this is so I can enhance the app to use the new JSON Relational Duality Views (a new feature of Oracle Database 23ai) in another blog.
Step 3: Setting Up the Database Integration
With the backend ready, it’s time to bring the app to life with a clean and functional frontend and using the oracledb Node.js package, we can interact with the database with just a few lines of code.
Database Configuration - db.js
The dbConfig object reads database credentials (username, password, and connection string) from environment variables (in our .env file), keeping sensitive information secure. Here’s how simple it looks:
const oracledb = require('oracledb');
const dbConfig = {
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
connectString: process.env.DB_CONNECT_STRING
};Creating a Connection Pool - db.js
Oracle’s connection pooling allows efficient database connections for multiple requests, reducing latency and improving performance. The initialize function sets up this pool:
async function initialize() {
try {
await oracledb.createPool(dbConfig);
console.log("Oracle DB connection pool created successfully");
} catch (err) {
console.error("Failed to create Oracle DB connection pool:", err);
}
}With this single function, the app is ready to handle any database operations.
Closing the Connection Pool -db.js
For clean shutdowns, the close function ensures all connections are properly terminated:
async function close() {
try {
await oracledb.getPool().close();
console.log("Oracle DB connection pool closed");
} catch (err) {
console.error("Error closing Oracle DB connection pool:", err);
}
}
module.exports = { initialize, close, oracledb };Defining the environment variables - .env
inside the .env file, store the database user, password and the connect string we used earlier.
DB_USER=ADMIN
DB_PASSWORD="StrongPassword123!"
DB_CONNECT_STRING=(description= (retry_count=20)(retry_delay=3)(address=(protocol=...)(port=1521)(host=...))(connect_data=(service_name=...))(security=(...)))Pro Tip: if your password had special characters be sure to format the string inside double quotes.
Step 4: Routing and Backend Logic with index.js
The index.js file is where all the backend magic happens. This is how you'll manage requests and responses while connecting the frontend with the Oracle Database. With Express and the oracledb module, creating and managing routes is straightforward.
Fetching Brew Data - index.js
When the user requests previously logged brews, the GET /brews handles it. The route goes to the database and returns the data as JSON:
app.get('/brews', async (req, res) => {
const conn = await oracledb.getConnection();
const result = await conn.execute(
'SELECT * FROM coffee_brews ORDER BY timestamp DESC',
[],
{ outFormat: oracledb.OUT_FORMAT_OBJECT } // Returns rows as JavaScript objects
);
res.json(result.rows);
await conn.close();
});Logging New Brews - index.js
The POST /brews route accepts user input from the frontend, validates it, and stores it in the database:
app.post('/brews', async (req, res) => {
const { brew_method, bean_origin, grind_size, temperature, brew_time, notes, rating } = req.body;
const conn = await oracledb.getConnection();
await conn.execute(
`INSERT INTO coffee_brews (brew_method, bean_origin, grind_size, temperature, brew_time, notes, rating)
VALUES (:brew_method, :bean_origin, :grind_size, :temperature, :brew_time, :notes, :rating)`,
[brew_method, bean_origin, grind_size, temperature, brew_time, notes, rating],
{ autoCommit: true }
);
res.status(201).json({ message: 'Brew logged successfully' });
await conn.close();
});Starting the Server - index.js
Finally, the server is started and listens on a defined port:
app.listen(3000, () => {
console.log('Server running at http://localhost:3000');
});Step 5: Frontend Logic
The script.js is what connects your UI to the backend. With a small amount of code, it allows the logging of new brews and retrieval of data.
Fetching Brew Data - script.js
The fetchBrews function retrieves all stored brews from the backend and displays them on the page. This function is called both on page load and after a new brew is logged:
async function fetchBrews() {
console.log("Fetching brews...");
const response = await fetch('http://localhost:3000/brews');
console.log("Response received:", response);
const brews = await response.json();
console.log("Parsed brews data:", brews);
const list = document.getElementById('brewList');
list.innerHTML = '';
brews.forEach(brew => {
console.log("Processing brew:", brew);
const listItem = document.createElement('li');
listItem.textContent = `${brew[1]}: ${brew[2]} (${brew[4]}°C, ${brew[5]}s) - ${brew[7]}`;
list.appendChild(listItem);
});
console.log("Brew list updated on UI");
}Logging New Brews - script.js
When users submit the brew logging form, the logBrew function captures the input and sends it to the backend:
async function logBrew(event) {
event.preventDefault();
const brew = {
brew_method: document.getElementById('brewMethod').value,
bean_origin: document.getElementById('beanOrigin').value,
grind_size: document.getElementById('grindSize').value,
temperature: parseInt(document.getElementById('temperature').value),
brew_time: parseInt(document.getElementById('brewTime').value),
notes: document.getElementById('notes').value,
rating: parseInt(document.getElementById('rating').value)
};
await fetch('http://localhost:3000/brews', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify(brew)
});
fetchBrews();
}Step 6: Everything together
Here's the link to my github where you can find the complete code for the app. I've also added a bit of CSS for style
That's all.
You've now got a simple full-stack app. If you're interested in the new Oracle Database 23ai features, check out the release blog and some of the public content currently available.
- Killian
Comments