Background

Here you are, want to enforce a constraint such as any new record must have a unique name, title, etc, but you are not sure where to enforce it exactly. application level or DB level?

Application level vs database level

For me having your constraints on the DB level is a must, the reason why is that the database could be accessed from many places so if you set the constraint over your application level that means if anything else accesses your db directly won't respect your rules. Plus most databases do the uniqueness validation more efficiently so it's better to have it there.

Assuming that your case is coverable by the DB of your choice then I would definitely recommend using your db to enforce your constraints and all you need to do is to handle the failure on your application level, most DB clients return error codes, something like below:

if ( err && err.code !== 11000 ) {
    console.log(err);
    res.send('duplicate record, u need to focus user!!!');
    return;
  }

Of course, would recommend wrapping your errors but that's a topic for later.

Status code

Should the status code be anything besides 4xx? Well well, 4xx means that the user needs to do some action or in other words the user's fault that this happened, so it should be 4xx.

Which one of the 4xx? Now you might be wondering why not use 400 instead of 409. the thing is your status code should be clean and actually reflect the status, so if you used 400 code for everything that means the FE has to do extra work with your error responses, then why not make it easy for your API number 1 users and just return 409

Conclusion

It's important to simplify your logic and make sure that the data integirty rules are set for all clients as well as returning the appropriate response status code.