No binary data in your databases

It might be tempting to store binary data in one of your database's table. Just don't do it. Even if a lot of ORMs do support uploading binary data into a database, it does not mean you should do it.

There are a lot of reasons against storing binary data (or BLOBs) in your database

Database backup runs longer

This is obvious: Having more (binary) data in your database leads to much longer backup runs of your pg_dump or mysqldump commands. Backing up files is much faster than doing a database with the same binary data.

No incremental backups

Having your binary data inside a filesystem allows you to do incremental backups, lowering the overall size. This is not possible if your database contains the binary data. A database dump can not be used for incremental backups.

Restoring files

There might be the time that one of your customers deletes an important file which has been stored in your database. It's definitely possible to restore this file from of one your database backup dumps but it takes time. Restoring a file from a filesystem backup or dedicated backup application is much faster.

Roundtrip time

Having binary data stored in your database means that each requested file must pulled from the database. Depending upon the number of requests, this is probate way to paralyze your database server.

Best practice

  1. Don't store your binary data in your database. Either use the filesystem or an object storage like AWS S3.
  2. Use a folder structure like {customer-id}/{uuid-for-file}
  3. Create a table inside your database which contains a reference to the uploaded file.
  4. Let your webserver either serve the static files or just link to the reference in the chosen object storage.

Schema

Blueprint

Table customer {
  id integer [not null, increment]
}

// This can be used as a template for local files or object storages
Table file {
  id integer [not null, increment]
  customer_id integer [not null, ref: > customer.id]
  // after uploading the file, it is renamed to this UUID and moved to {local_file.customer_id}/{local_file.uuid} relative to a defined directory by your application; don't store absolute paths!
  uuid uuid [not null]
  // original filename
  filename char(255) [not null]
  // the uploaded file's MIME type; this is stored so the application can send the correct HTTP Content-Type headers
  mime_type char(255) [null]
  // size in bytes, also used for sending the correct Content-Length HTTP header
  size integer [not null]
  // maybe a hashsum to prevent duplication
  // hashsum char(64) [not null]
}