I'm building a website with React, Node.js, and MySQL. Users can upload up to three images when creating an announcement, and I'm unsure how those files should be stored. I've read that saving images directly as MySQL BLOBs can make queries slower and cause the database to grow unnecessarily. Should I use cloud storage, the server's filesystem, or another approach, and what information should go in MySQL?
3 Answers
If the application runs on a single server and the files do not need to scale much, you can save them in a protected directory on that server and store a generated UUID or filename in MySQL. Keeping the first few UUID characters as nested directories can prevent one directory from containing too many files. Store searchable metadata, such as the original type, size, and announcement ID, in the database.
MySQL does support BLOB columns, but that does not automatically make them the best choice here. With up to three images per announcement, BLOBs can still work for a small, carefully designed application, but they increase backup size and make careless queries expensive. Avoid selecting image data when listing announcements; use object or filesystem storage when you want easier scaling and simpler delivery.
A common design is to store the actual image in object storage, such as Amazon S3 or another storage bucket, and save only its key or URL in MySQL along with the announcement ID and metadata. This keeps normal database queries lightweight and makes the files easier to scale independently.
If you use separate storage, make sure your upload and cleanup logic handles failures on either side. Otherwise you can end up with a database record without an image, or an unused image without a record.
Do not trust the uploaded extension or MIME type. Validate the file, restrict permitted image formats and sizes, generate your own filename, and ensure uploaded files cannot be executed as server-side scripts.