We have three levels of code in our big and complex Web site:

  1. plain SQL (in fact Perl DBI),
  2. a primitive ORM (we wrote our own primitive ORM, because DBIx::Class appeared too slow in our environment),
  3. business objects (high level interface to the database, grouped by topics (not by tables and fields)).
  4. (well fourth, special code such as views)

Should such tasks as replacing an empty string with NULL be on level 2 or level 3?

  • Going into the database, level 3. Going out of the database, level 2.
    – Andy
    Sep 4, 2016 at 16:13
  • 1
    @DavidPacker You opinion is interesting but I see absolutely no rationale behind it. Please explain
    – porton
    Sep 4, 2016 at 16:13
  • @Laiv DBIx::Class Perl module supports both inflate ("from" DB) and deflate ("to" DB) search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/…
    – porton
    Sep 4, 2016 at 17:00
  • Is there any case where empty strings are valid inputs? If yes, I would not implement the replacement of strings by null at ORM side. However we don't know how your orm is and its features.
    – Laiv
    Sep 4, 2016 at 17:01
  • 1
    Do you wrtie the queries or does the ORM does it for you? You have "level" 1 with SQL and "level" 2 with ORM. Sep 4, 2016 at 23:12

2 Answers 2


First thing you have to decide is if you think it is important to distinguish (at least in certain cases) between empty strings and NULL in your business layer (BL), or not. I have seen systems where this difference was important, and other systems where the whole DB or its API was configured not to distinguish between those cases, because in the BL it made no difference. So before asking on which layer a mapping shall take place, clarify what you want to use here.

If you need to distinguish NULL/undef from the empty string in your BL, then the answer is simple: don't do any automatic replacement. Map the empty string to an empty string, and "undef" to NULL (and vice versa). You have to write your business code so it can deal correctly with empty strings and "undef"ined values as well.

If you want to treat "undef" and "empty string" as equal in your BL for all tables and attributes, then it clearly makes sense to let the ORM do the mapping. If, however, you want this only sometimes, for some columns, and in other cases you need to distinguish between both values, you need to know how often you expect this situation. If the former is a rare situation you need only for approximately a half dozen attributes, you can implement the behaviour in the BL on a "by case" basis. If you have 100 or more attributes for which "undef" and "empty string" should be treated as equal, you should extend your ORM to let it handle this for certain columns in a generic manner.

For example: for certain, specificially tagged columns, the ORM could just map any NULL value to an empty string when it reads the data from the DB, and convert the empty string back to NULL when it writes the data again. Extending your ORM in this manner to provide a generic mechnanism will probably need some effort, and you have to decide if you can reuse this mechanism often enough to gain more benefit from it than the effort it takes to implement it.


After some thinking:

If we want a string field in DB to have NULL instead of empty string, then it is most likely a property of the business object that it should return NULL (undef in Perl) for this column and it should allow to store NULL (undef) into this field (one way to do this is to replace empty string with undef every time empty string is stored into this object).

After some more thinking:

The database design may change. Now we want an empty string to be stored as NULL, but we may change it to store empty string as empty string. This should not affect the functionality of the business object, which API may be written either with undef or empty string, as agreed on business object layer, not ORM layer.

Thus both arguments call to do this in business objects not in ORM.

  • 1
    I think you might be overthinking this a bit. The ORM is going to define this field as nullable anyway. The business objects may or may not treat this as nullable for business purposes, depending on the business rule being implemented. So yeah, I think you probably got this backwards. At the end of the day, what matters is that the business purpose gets fulfilled, not that the code happens to be in the "right" place. Sep 4, 2016 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.