Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Do I need a database model?

by neilwatson (Priest)
on Mar 10, 2014 at 15:06 UTC ( #1077701=perlquestion: print w/replies, xml ) Need Help??
neilwatson has asked for the wisdom of the Perl Monks concerning the following question:


I'm prototyping a web application using Mojolicious. The database schema for this application is simple, only two tables, each with less than 12 columns. The main table will have 100,000s of rows. The second table will have less than 100 rows. Only one query involve both tables. The rest involve only the main table and involve some sub queries, group bys, and maxes.

Is this complicated enough to warrant Class::DBIx or similar modeler? Will I gain anything by modeling?

Neil Watson

Replies are listed 'Best First'.
Re: Do I need a database model?
by moritz (Cardinal) on Mar 10, 2014 at 15:12 UTC

    Just the other day I wrote a DBIx::Class-backed application where the database only has two tables.

    What DBIx::Class simplifies a lot is basic CRUD (Create/Read (select)/Update/Delete) operations, as well as stuff like prefetching related tables.

    It also has a learning overhead, so the first time you use it, you might spend more time learning it than if you used plain DBI.

Re: Do I need a database model?
by Your Mother (Chancellor) on Mar 10, 2014 at 16:30 UTC

    In addition to what moritz said, I think the biggest gain in a smaller situation like this is experience. DBIC really shines on complicated stuff (ResultSets and relationships and object inflation/deflation) and deployments and fixtures and that sort of thing. It's quite hard to learn actually but I feel worth it and I think a small project with just two tables represents an ideal case for easing the learning curve.

Re: Do I need a database model?
by boftx (Deacon) on Mar 10, 2014 at 22:25 UTC

    I would agree with the others that there is value in gaining experience. But...

    If there are performance issues involved I would think twice about it and probably be biased towards using clean, solid CRUD in simple modules. Given that you have a table that will be relatively large it sounds like performance might be in play.

    It helps to remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.
      Yes, tests so far show that transaction performance is a challenge. I've been thinking about sticking with raw sql too.

      Neil Watson

        Raw SQL with properly bound columns and prepared statements or DB procedures will always win the speed race, often hugely, but I want to point out that DBIC got MUCH faster recently. Fast enough to beat Rose::DB, the previous ORM speed king, on certain operations; and you have tools like DBIx::Class::ResultSet::HashRef to skip the most expensive part of DBIC (objectification of records). DBIC also has some cache control built-in so depending on the use case it could conceivably be faster than raw SQL unless you go to the trouble of handling caching yourself. And that underscores the final case for DBIC: it does nearly everything and it does it before you realize you were going to need it.

Re: Do I need a database model?
by sundialsvc4 (Abbot) on Mar 11, 2014 at 13:03 UTC

    In my opinion ... and it is “my opinion” only ... ORMs turn out to be a colossal (a) waste of time, and (b) source of complexity and maintenance headaches.   And yes, I have dealt with many very-substantial applications that did it both ways.

    Instead of elaborating on these matters, though, my core objection to them is really one of philosophy.   The foundational argument behind these classes is the premise that “a database table === an application object.”   This is very rarely the case, IMHO.   Instead, a set of database tables provides the data source(s) for “a proper ‘application object,’” and the object itself consists of much more than simply a programmatic expression of a 1->1 and/or a 1->M relationship.   In a real-world sized application, many related objects might draw information from the same table, yet the structural strength and integrity of each of these objects is, yet again “IMHO,” considerably weakened(!) by spinning-off parts of it to a “third-party class” which represents any single table and which seeks to express anything anywhere that has anything to do with this particular table.   Note that you can commit this error with or without DBIx, but DBIx and its kin make such errors (IMHO) considerably easier to commit.   A program-object is not necessarily a data-object, and a pure-data-access object often is not useful and (IMHO!) does not belong.

    My career experience consists mostly of dealing with now-recalcitrant applications, many of them built over the last five to ten years, which at the time were very lavishly built but which now are collapsing under their own baroque weight.   (As such, I do not fear running out of a job anytime soon ...)   These monsters are being scrapped, decommissioned, or “in-sourced,” left and right now, and one of the key reasons for this is their “bespoke” complexity.   The system must be changed in some deep way, so the logic pertaining to what it does now must be cleanly isolated, extricated, and replaced ... but there is simply so much coupling within the system, so many source-code modules (and configuration files) that must be simultaneously understood and touched, that the software-machine becomes unmaintainable.   Since data (structure and access) touches literally everything everywhere, it becomes the straw.

      DBIC is shorthand for DBIx::Class not DBIx. DBIx is, in fact, nothing but a prefix. There is no DBIx module. There are several hundred DBI based packages that use the DBIx prefix and many differ completely in purpose and usage.

      DBIC does not simply abstract 1:1 with tables. ResultSets and Result classes cover a lot including plain highly reusable SQL like SQL::Abstract, without necessarily mapping to an existing table at all. And in DBIC there are column objects, storage object, schema objects, and more. Chained ResultSets is one of the most powerful and liberating ideas in DB code.

      DBIC is more complicated than a plain SQL library but if written with the same kind of discipline it takes to keep an SQL library from turning into spaghetti it is self-documenting, highly extensible, repurposable, self-deploying, versionable, etc, etc, etc. Many, every one Iíve seen, home grown SQL libraries take on some of the characteristics of a good ORM, except they tend to do it atrociously without testing or sanity. NIH is a the prime cause recalcitrance in some applications.

      Please provide literal examples instead of philosophy if you're going to make this kind of argument. Several of us have provided good, clean examples of DBIC on the site before. If you have a better approach, everyone would love to see a CUFP post on it.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1077701]
Approved by marto
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (2)
As of 2018-01-19 06:05 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (215 votes). Check out past polls.