Access MySQL Document Store from NodeJS Application
Tags: mysql, json, nosql, node
Published: 2023-09-19 08:59:29

In my previous post [Using MySQL as a NoSQL Document Store - Introduction](https://dev.to/prsaya/using-mysql-as-a-nosql-document-store-introduction-125p) 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](https://www.npmjs.com/package/@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 `name` field: `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 `_id` field 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}`)
})
```


---

## References

* [MySQL Connector/Node.js API and Reference](https://dev.mysql.com/doc/dev/connector-nodejs/latest/)
* [MySQL - X DevAPI User Guide](https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-users-introduction.html)

---