Installing MySQL is very simple. Just head to the official website and follow the instructions. You'll usually be asked for a password for the root user, which you can use later on to manage the server settings and user accounts.
There are some options to connect to a MySQL server using Node.js, but the best tools are the mysql and mysql2 modules. They both serve the required purpose, and neither is the next version of the other, they're just a bit different in design and supported features.
First, let's add the dependency to our service. On the terminal, go to our service folder and type:
npm install mysql --save
We can now include our dependency and configure a connection to the database. To avoid having the credentials in our code, we can create a separate file and put settings there that we may change in the future, and that shouldn't belong in the code. We can take advantage of Node.js being able to include JSON files, and just write our settings in JSON.
Create a file called settings.json, and add the following content:
{
"db": "mysql://root:test@localhost/imagini"
}
We defined a setting called db that has a database URI, which is a handy way of defining our database access and credentials using an address similar to any website address. Our database uses mysql; it's at localhost (using the default port), which can be accessed using the username root and the password test, and our database name is called imagini. We can now include the module and settings, and create the connection:
const settings = require("./settings");
const mysql = require("mysql");
const db = mysql.createConnection(settings.db);
This module only connects to the database when you make a query. This means the service would start and you wouldn't know whether your connection settings are correct until you make the first query. We don't want to figure out we can't connect to the database only when the service is used later on, so let's force a connection and check if the server is running and accepts our connection:
db.connect((err) => {
if (err) throw err;
console.log("db: ready");
// ...
// the rest of our service code
// ...
app.listen(3000, () => {
console.log("app: ready");
});
});
This way, if anything is wrong with the database, the service won't start and will throw an exception, which will notify you to check what's wrong. Here's an example of a possible error:
Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES)
This indicates you probably typed the password incorrectly, or the user doesn't match, or even the hostname or database may be wrong. Ensuring you connect to the database before setting up the service means your service won't be exposed to the public without a proper state.
Our microservice has a very simple state, so to speak. Our state is the images previously uploaded. Instead of using the filesystem, we can now use the database and create a table to store them all:
db.query(
`CREATE TABLE IF NOT EXISTS images
(
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
date_used TIMESTAMP NULL DEFAULT NULL,
name VARCHAR(300) NOT NULL,
size INT(11) UNSIGNED NOT NULL,
data LONGBLOB NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8`
);
We can issue this query every time the service starts because it will create the images table only if it doesn't exist already. If we don't change its structure, it's fine to always do this.
You can see we're creating a table with a unique identification number (id), a creation date (date_created), a date to know when our image has been used(date_used), the name of the image, the size of it in bytes, and the image data. The size is a little redundant here as we could just check the data length, but bear with me, this is just an example.
We also defined our name as a unique key, meaning it has an index for quickly finding images by name, and also ensures our name does not repeat and that no one can overwrite an image (without removing it first).
Having the images stored this way on a database table gives you several advantages, such as regarding:
- How many images you have
- The size of every image and the total size
- When the images were created and last used
It also enables you to improve your service; for example, you can delete images that are not used for longer than a specific time period. You can also make this dependent on the image sizes. Later, you can add authentication (mandatory or not) and have user-specific rules.
It's also easy to back up and replicate the state to another site. There are plenty of tools for backing up databases, and you can have another MySQL server acting as a slave to this one and have your images replicated in real time to another geographical location.
Let's change our service from the previous chapter to use our table instead of the previously used folder on our filesystem. We can remove our fs module dependency (don't remove the path dependency for now):
app.param("image", (req, res, next, image) => {
if (!image.match(/.(png|jpg)$/i)) {
return res.status(403).end();
}
db.query("SELECT * FROM images WHERE name = ?", [ image ], (err,
images) => {
if (err || !images.length) {
return res.status(404).end();
}
req.image = images[0];
return next();
});
});
Our app.param is completely different. We now validate the image against our image table. If it doesn't find it, it returns code 404. If it does find it, it stores the image information in req.image. We can now change our image upload to store the image on our table:
app.post("/uploads/:name", bodyparser.raw({
limit : "10mb",
type : "image/*"
}), (req, res) => {
db.query("INSERT INTO images SET ?", {
name : req.params.name,
size : req.body.length,
data : req.body,
}, (err) => {
if (err) {
return res.send({ status : "error", code: err.code });
}
res.send({ status : "ok", size: req.body.length });
});
});
Uploading images no longer use the filesystem and instead creates a new row on our table. We don't need to specify the id as it's automatic. Our creation date is also automatic as it defaults to the current timestamp. Our use date defaults to NULL, which means we haven't used the image yet:
app.head("/uploads/:image", (req, res) => {
return res.status(200).end();
});
Our image check method now gets extremely simple as it relies on the previous app.param to check whether the image exists, so, if we get to this point, we already know the image exists (it's on req.image), so we just need to return the code 200.
Before updating our image fetch method, let's try our service. If you start it on the console, you can immediately open any MySQL administration tool and check our database. I'm using Sequel Pro for macOS. Although there's a Pro in the name, it's free software and it's damn good:
Our table was created, and you can check it has all the properties and indexes we defined. Let's now upload an image once again:
As before, it returns a JSON response with a success status and the size of the image. If you look at Sequel again, on the content separator, you'll see our images data:
Let's try and upload the image again. Previously, our service would just overwrite it. Now, because of our unique index, it should deny an INSERT with the same name:
Great! The ER_DUP_ENTRY is the MySQL code for duplicate insertion. We can rely on that and deny overwriting images.
We can also check whether our image exists using the check method:
If we use another name, we'll get a code 404:
It looks like everything is working great. Let's now change our final method, the image manipulation one. This method is almost the same; we just don't have to read the image file, as it's already available:
app.get("/uploads/:image", (req, res) => {
let image = sharp(req.image.data);
let width = +req.query.width;
let height = +req.query.height;
let blur = +req.query.blur;
let sharpen = +req.query.sharpen;
let greyscale = [ "y", "yes", "true", "1",
"on"].includes(req.query.greyscale);
let flip = [ "y", "yes", "true", "1",
"on"].includes(req.query.flip);
let flop = [ "y", "yes", "true", "1",
"on"].includes(req.query.flop);
if (width > 0 && height > 0) {
image.ignoreAspectRatio();
}
if (width > 0 || height > 0) {
image.resize(width || null, height || null);
}
if (flip) image.flip();
if (flop) image.flop();
if (blur > 0) image.blur(blur);
if (sharpen > 0) image.sharpen(sharpen);
if (greyscale) image.greyscale();
db.query("UPDATE images " +
"SET date_used = UTC_TIMESTAMP " +
"WHERE id = ?", [ req.image.id ]);
res.setHeader("Content-Type", "image/" + path.extname(req.image.name).substr(1));
image.pipe(res);
});
You can see how we used the path dependency to get the extension of the image name. The rest is the same. We just add an update to our image every time we request this method.
We can use a web browser to test our method and see our previously uploaded image:
Everything should just work as before because we haven't changed our image manipulation dependency, so blurring and the other actions should work as expected:
We can now improve our service and add a method we didn't expose before: deleting an image. To do that, we can use the DELETE verb from HTTP and just remove the image from our table:
app.delete("/uploads/:image", (req, res) => {
db.query("DELETE FROM images WHERE id = ?", [ req.image.id ], (err)
=> {
return res.status(err ? 500 : 200).end();
});
});
We just have to check whether the query resulted in an error. If so, we respond with a code 500 (internal server error). If not, we respond with the usual code 200.
Let's restart our microservice and try to delete our image:
It looks like it worked; it responded with a code 200. If we try to open our image in the web browser, we should see something like this:
On Sequel, the table should now be empty too:
We now have a functional microservice with a state that persists across restarts, as we intended. You can now deploy to any cloud service with no dependency on the filesystem, just a database.
You could easily change MySQL to another database or use an object relational mapping (ORM) module to enable you to change database server without changing your code. An ORM is a library that allows you to use a common interface to access different types of databases. Usually, this kind of abstraction involves not using SQL at all and reducing your interaction with the databases to simpler queries (to allow for interoperability between database servers).
Let's take this opportunity to go a little further and add a few methods that got simplified by this migration to the database. Let's create a method that exposes statistics about our database, and let's remove old images.
Our first statistics method should just return a JSON structure with some useful information. Let's expose the following:
- The total number of images
- The total size of the images
- How long our service is running
- When the last time was that we uploaded an image
Here's an example of how our statistics method could look:
app.get("/stats", (req, res) => {
db.query("SELECT COUNT(*) total" +
", SUM(size) size " +
", MAX(date_created) last_created " +
"FROM images",
(err, rows) => {
if (err) {
return res.status(500).end();
}
rows[0].uptime = process.uptime();
return res.send(rows[0]);
});
});
Restart the service, and let's try it:
As we can see, we have no images as we just removed our image previously. There's no size because we have no images. There's also no used date, and the service uptime is 5 seconds.
If we upload our previous image, we will get different results, something like the following screenshot:
Now, for our second task, deleting old images, we need to check our database periodically. We'll use an interval timer and just run a DELETE query. The intervals mentioned in the following query are just an example; you can write the conditions you want.
setInterval(() => {
db.query("DELETE FROM images " +
"WHERE (date_created < UTC_TIMETSTAMP - INTERVAL 1 WEEK
AND date_used IS NULL) " +
" OR (date_used < UTC_TIMETSTAMP - INTERVAL 1 MONTH)");
}, 3600 * 1000);
This query deletes images that were not used in the past month (but were used before) or images that were not used in the past week (and never used before). This means that images uploaded need to be used at least once or they will get removed quickly.
You can think of a different strategy, or use no strategy and delete manually if you want. Now that we've seen MySQL, let's move on and look at another kind of database server.