In my previous post Using MySQL as a NoSQL Document Store - Introduction I had shown accessing MySQL Document Store using the MySQL Shell client using X DevAPI. Please refer the post for an introduction to the JSON data document store, usage of the APIs and the MySQL Shell.
In this post, I use the MySQL Connector/Node.js (NodeJS Driver) and the X DevAPI APIs in a NodeJS and ExpressJS JavaScript server-side program.
I had used MySQL Server v8.0.34 and NodeJS v18.17.1 for programming on a Windows 11 computer. The NodeJS application has the following dependencies (from the package.json):
"dependencies": {
"@mysql/xdevapi": "8.0.33",
"express": "^4.18.2"
}
The npm registry for the MySQL NodeJS Driver: @mysql/xdevapi
The Data
I used the following structure for the JSON document data to store in the database. The database (schema) is called X_db and the collection is users. I am assuming these already exist. The following is the structure of each document in the collection. Note that the structure can be different for each document in a collection.
Please refer my previous post (linked at the top) for details about collection, document, the _id field, creating the collection and the schema.
{
"_id": "1",
"name": "Pete",
"age": 13,
"city": "Dallas",
"favs": [
"Cheese cake",
"Skiing"
]
}
In this app we will make sure the name field is mandatory and is also unique within the users collection. This is by defining an index on the name field with unique and required constraints.
Start the MySQL Shell and connect to the database server. From the MySQL Shell in the default JavaScript mode:
// Create a unique and required index on the name field
mysql-js> db.users.createIndex("name", { fields: [ { field: "$.name", type: "text(20)", required: true } ], unique: true })
// List the indexes on the collection
mysql-js> session.sql("SHOW INDEX FROM x_db.users")
In case you need to drop an index, use the following syntax:
mysql-js> db.collection_name.dropIndex("index_name")
The App
The NodeJS app has two programs - index.js and database.js. The database.js has the database connection code using the MySQL NodeJS Driver APIs. The index.js is the ExpressJS web server and has the web API routes to perform CRUD (Create, Read, Update and Delete) operations on the MySQL Document Store.
The app is a barebones program with basic error handling (the data validation and detailed logging is not included for the sake of brevity). The app will show proper responses and logging in case of database connection errors, non existing schema or the collection, data errors for unique and required field constraint violations.
I have tried the web APIs using Postman software.
The APIs are:
- Post request with a JSON document as body to create a user:
http://localhost:3000/users/ - Get request to list all users sorted by
namefield:http://localhost:3000/users/ - Get request to read data for a specified name:
http://localhost:3000/users/:name - Put request to modify the document by its
_idfield with the body as a JSON:http://localhost:3000/users/:id - Delete request to delete a document by its
_id:http://localhost:3000/users/:id
The Code
database.js:
const mysqlx = require('@mysql/xdevapi')
const config = { host: 'localhost', port: 33060, user: 'user_name', password: 'password' }
const objects = { schema: 'x_db', collection: 'users' }
const getDbSession = async () => mysqlx.getSession(config)
const getCollection = async () => {
const session = await getDbSession()
const schema = session.getSchema(objects.schema)
if (! await schema.existsInDatabase()) {
throw new Error(`The schema ${objects.schema} doesn't exist`)
}
return schema.getCollection(objects.collection)
}
module.exports = { getDbSession, getCollection }
index.js:
const express = require('express')
const app = express()
const PORT = 3000
const db = require('./database.js')
app.use(express.json())
app.get('/dbinfo', async (req, res, next) => {
try {
const session = await db.getDbSession()
res.send(session.inspect())
}
catch(err) {
next(err)
}
})
app.post('/users', async (req, res, next) => {
try {
const collection = await db.getCollection()
const r = await collection.add(req.body).execute()
res.send(`Inserted a document with id ${r.getGeneratedIds()}`)
}
catch(err) {
next(err)
}
})
app.get('/users/:name', async (req, res, next) => {
try {
const collection = await db.getCollection()
const crsr = await collection.find('name = :param')
.fields(['_id', 'name', 'city'])
.bind('param', req.params.name)
.execute()
res.send(crsr.fetchAll())
}
catch(err) {
next(err)
}
})
app.get('/users', async (req, res, next) => {
try {
const collection = await db.getCollection()
const crsr = await collection.find().sort("name asc").execute()
res.send(crsr.fetchAll())
}
catch(err) {
next(err)
}
})
app.put('/users/:id', async (req, res, next) => {
const key = Object.keys(req.body)[0]
try {
const collection = await db.getCollection()
const r = await collection.modify('_id = :param')
.set(key, req.body[key])
.bind('param', req.params.id)
.execute()
res.send(`Updated ${r.getAffectedItemsCount()} document`)
}
catch(err) {
next(err)
}
})
app.delete('/users/:id', async (req, res, next) => {
try {
const collection = await db.getCollection()
const r = await collection.remove('_id = :param')
.bind('param', req.params.id)
.execute()
res.send(`Deleted ${r.getAffectedItemsCount()} document`)
}
catch(err) {
next(err)
}
})
app.use((err, req, res, next) => {
console.log("<<<ERROR>>>")
console.log(err)
res.send(err.toString())
})
app.listen(PORT, () => {
console.log(`Express app listening on port ${PORT}`)
})
Top comments (0)