I'm building a website with React, Node.js, and MySQL where users can upload up to three images for each listing. I'm unsure whether the image data should be stored directly in MySQL as BLOBs or kept somewhere else. I'm concerned that storing images in the database could make queries slower and cause problems as the database grows. What storage approach would you recommend, and what should I save in MySQL?
3 Answers
MySQL does support BLOB columns, but that does not make them the best default for this use case. Images can make backups, queries, replication, and accidental SELECT * operations much heavier. BLOBs can be reasonable when strict transactional storage is more important than scalability, but for typical listing images, object storage or a filesystem plus database metadata is simpler and more scalable.
If you have only one server and persistent disk storage, you can also save the files in a protected upload directory and store a generated UUID or filename in MySQL. Organize files into subdirectories so one directory does not contain everything. Validate the MIME type and file contents, restrict file extensions and sizes, generate your own filenames, and never allow uploaded files to be executed as server-side code.
The usual approach is to store the actual files in object storage, such as Amazon S3, Google Cloud Storage, or another storage bucket. Save the object key or URL, along with metadata such as the listing ID, MIME type, file size, and upload timestamp, in MySQL. Your application can then return the image URL when the listing is requested. This keeps normal database queries small and lets the storage service handle large files efficiently.
Make sure deletion is handled in both places. If a listing is removed, delete its database record and its storage objects, preferably with cleanup jobs or a consistent application workflow.
So the image itself would be uploaded to something like S3, while MySQL would only contain its URL or identifier? That makes sense.