Oracle Developers
Published in

Oracle Developers

Building a Census application with Sequelize and Oracle Database

This blog post walks you through creating a web application that uses the Node.js Sequelize ORM and Oracle Database.

A banner image with the text ‘Creating your first Web Application that uses Sequelize’

This is a guest blog post by , who is a member of the Oracle Database development team working on drivers and frameworks.

In my I announced the Oracle Database dialect for Sequelize. Sequelize is a modern TypeScript and Node.js ORM. In this blog post I will walk you through how I created a demo Census application that uses Sequelize.

The architecture of the application is:

This is an architecture diagram showing users calling a frontend web application which communicates over a network to a backend server. The backend server does Oracle Database operations via Sequelize.

The frontend is a web application (at https://localhost:3000/). This frontend talks to the backend of the system which is listening on http://localhost:3001/. The backend is implemented using Sequelize and uses the new Oracle dialect to connect to Oracle Database.

The steps I followed to develop a web application

To simplify the development of the frontend, I am using create-react-native-app. This makes it significantly easier to get started with a React Native project.

How to set up the front-end?

Create the client directory and change into it:

mkdir client && cd client

Install the create-react-native-app module:

npm i -g create-react-native-app

Create a sample web application using:

create-react-native-app demo-sequelize-app

I checked the demo app worked by changing into the application directory:

cd demo-sequelize-app

I then ran npm start to start the web application, which was accessible on

Now I had the sample web app, I needed to extend it for my desired application and connect it to a backend.

Census Application Frontend

To build the frontend of my demo Census application, I modified the app.js file to add four endpoints:

  • CREATE — to insert data into the database
  • UPDATE — to update data in the database
  • READ — to read the database
  • FIND — to query the database

The below snippet shows how I added the CREATE endpoint. Similar code can be added for other endpoints. These just set up the URL to access these pages (I will show how to set up each of these endpoints in detail in the next section).

function App() {
const navigate = useNavigate();
...
const navigateToCreate = () => {
// Navigates to the create page
navigate('/create');
};
...
return (

<div className="main">
<div><h2 className="main-header">Census</h2></div>
<div>
...
<button onClick={navigateHome}>Home</button>
<button onClick={navigateHome}>Insert</button>
...
<Routes>
...
<Route path='create' element={<Create />} />
...
</Routes>
</div>
);
}

(The ellipses show where you can add code for the other endpoints.)

How to set up the backend?

To set up the backend of the application, create the directory server and change into it:

mkdir server && cd server

I set environment variables used by the Sequelize instance to connect to the database, for example with my local Oracle Database 21c XE database I set credentials to an existing database user that I had created previously:

export SEQUELIZE_USERNAME=sequelize
export SEQUELIZE_PASSWORD=....
export SEQUELIZE_SERVICENAME=XEPDB1
export SEQUELIZE_HOST=localhost
export SEQUELIZE_PORT=1521

Create a file called index.js in the server directory. This file will :

  • Instantiate the Sequelize instance
  • Initialise the census model
  • Sync the Sequelize model with the database
  • Populates data in the database table

I edited index.js to look like the below snippet. This shows how to instantiate a Sequelize instance and define a census model with two columns. The ellipses show where other columns can be added. At the end the census data is efficiently loaded into the database. I had previously downloaded the data from v:

// The Census model
let census;

// Function that we call when the server start to do the necessary initialization of tables in the DB
async function init() {
// Establishing a DB connection using sequelize
// For more info please refer - https://sequelize.org/docs/v6/getting-started/#connecting-to-a-database
const sequelize = new Sequelize(process.env.SEQUELIZE_SERVICENAME,
process.env.SEQUELIZE_USERNAME,
process.env.SEQUELIZE_PASSWORD,
{host: process.env.SEQUELIZE_HOST,
port: process.env.SEQUELIZE_PORT,
dialect: 'oracle'});

// Defining the census model which would create the table census in the db with the column names given as parameter
// The keys in the parameter represent the DB column name and the value represents its configuration
// For more info please refer - https://sequelize.org/docs/v6/core-concepts/model-basics/#using-sequelizedefine
census = sequelize.define('census', {
...
AGE: {
type: DataTypes.STRING,
allowNull: false,
},
WORKCLASS: {
type: DataTypes.STRING,
allowNull: false,
},
...
});

// We call sequelize.sync to refresh the DB with the sequelize models defined
// For more info please refer - https://sequelize.org/docs/v6/core-concepts/model-basics/#model-synchronization
await sequelize.sync({force: true});

// Reading the csv file in sync mode
let rows = fs.readFileSync('./CENSUS.csv').toString().split('\n');
// Extract the column name of the table from the csv file
const cols = rows[0].split(',');
// Shift the rows array to pop the header
rows.shift();
// Sequelize way to add multiple rows to the table at once
// For more info please refer - https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#creating-in-bulk
await census.bulkCreate(build(cols, rows));
}

init();

Setting up the components of the Census Application

In this section I show how to build the frontend and the backend of the Insert and Query pages of the application. The user interface might not look exactly the same for you because, for brevity, I will not be sharing the CSS of the front-end in this blog post.

The home page has buttons to allow insert, update, find and read:

A screenshot of a simple web application titled “Census” and showing buttons with the titles Home, Insert, Update, Find and Read.

How to set up the insert page?

The Insert page provides plenty of census data to be entered for someone:

A screenshot of a web form that has lots of census-related fields. For example it has fields where users can enter ages, names and occupations. At the bottom is a a Submit button

To implement the Insert page shown above, I added a file client/demo-sequelize-app/src/components/create.js. This file sends the input data to the application backend at http://localhost:3001.

The snippet below shows how create.js sets up the input fields for “age” and “workclass” and passes the entered data to the backend server. Other fields can be added similarly:

export default function Create() {
let navigate = useNavigate();
...
const [age, setAge] = useState('');
const [workclass, setWorkclass] = useState('');
...
// Method for sending the data received from the form to the backend server
const postData = () => {
axios.post(`http://localhost:3001/create`, {
...
age,
workclass,
...
}).then(() => {
console.log("done")
navigate('/read')
})
}
return (
<div>
<Form className="create-form">
<div className='row'>
...
<Form.Field className='col'>
<label>AGE</label>
<input placeholder='AGE' onChange={(e) => setAge(e.target.value)}/>
</Form.Field>
<Form.Field className='col'>
<label>WORKCLASS</label>
<input placeholder='WORKCLASS' onChange={(e) => setWorkclass(e.target.value)}/>
</Form.Field>
...
</div>
...
{/* We call the postData method when the Submit button is clicked */}
<button className='submitbutton' onClick={postData} type='submit'>Submit</button>
</Form>
</div>
)
}

Then I edited server/index.js to add code that inserts the input field data into Oracle Database using Sequelize. The snippet below shows how to do it for the two fields “age” and “workclass”:

// Endpoint to insert a row into the database
app.post("/create", async (req, res) => {
// Getting the data from the frontend
...
const age = req.body.age;
const workclass = req.body.workclass;
...
// We use the create method of sequelize to insert a row into the database
// For more info please refer - https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#simple-insert-queries
await census.create({
...
AGE: age,
WORKCLASS: workclass,
...
});
res.send();
});

The steps to set up the UPDATE endpoint is similar to the CREATE endpoint. I will leave this as an exercise for you.

How to query the database by ID and country?

The query page will look like this:

A screen shot of the web application with two search entry fields, ID and Country.

I added a new file client/demo-sequelize-app/src/components/find.js which has two fields, one for an “id” and the other for “country.” The query data entered by a user is passed to the backend.

The snippet below shows how to take the “id” as input from the user and send it to the backend server. Code for “country” can be added similarly:

export default function Find() {
const [APIData, setAPIData] = useState([]);
// Method for getting the rows by id from the backend server
const getDataById = (id) => {
axios.get(`http://localhost:3001/find/${id}`)
.then((getData) => {
setAPIData(getData.data);
})
}
...
return (
<div className='table'>
<div>
<Form className="create-form">
...
<Form.Field>
<label>Search By ID</label>
<input placeholder='ID' onChange={(e) => getDataById(e.target.value)}/>
</Form.Field>
...
</Form>
</div>
...
</div>
)
}

Then I edited server/index.js to include the endpoint for querying by id and country using Sequelize. The snippet below shows how I queried the database using the entered id:

// API to search by id
app.get("/find/:id", async (req, res) => {
// Extracting the id to be queried on
const id = req.params.id;
// We are querying the database for all elements matching the id
// For more info please refer - https://sequelize.org/docs/v6/core-concepts/model-querying-basics/#specifying-attributes-for-select-queries
const rows = await census.findAll({
where: {id: id}
});
// Sending the rows to the frontend to display
return res.send(rows);
});

The READ endpoint works similarly as the FIND endpoint but it fetches all the rows instead. I will leave this as an exercise for you.

Running the application

I made sure the credential environment variables were set as shown earlier, then I opened two terminals.

In terminal 1:

cd client
npm install
npm start

In terminal 2:

cd server
npm install
npm start

Now in a browser I opened https://localhost:3000/ and was able to view and create Census data.

If you’re curious about the goings-on of Oracle Developers in their natural habitat, join us on our !

--

--

Aggregation of articles from Oracle engineers, Groundbreaker Ambassadors, Oracle ACEs, and Java Champions on all things Oracle technology. The views expressed are those of the authors and not necessarily of Oracle.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Christopher Jones

Oracle Database Product Manager for language drivers including Python python-oracledb, Node.js node-oracledb, PHP OCI8 and more! On Mastodon: @cjbj@phpc.social