Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Perl and Databases

by NAstyed (Chaplain)
on Mar 28, 2002 at 16:20 UTC ( [id://155020] : perlquestion . print w/replies, xml ) Need Help??

NAstyed has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks!

I have a question about Perl and general Databases:

Is there a "recomended" database to use with Perl? I know speed is related with code, OS, CPU and other hardware things, but i just want to know if there is a database that "interact" better with Perl.

Also if some one can tell me some good and bad facts about databases such as mySql, MS SQL2000, Access and Oracle, would be very usefull, because ive searched comparison charts between them, but never found one.

I Hope you understand my english!

Greetings from Costa Rica

Replies are listed 'Best First'.
Re: Perl and Databases
by strat (Canon) on Mar 28, 2002 at 16:27 UTC
    Basically, about all the mentioned databases work very well with perl. But since the databases are built for different purposes, the question is what you want to do with them. If you just want to store and retrieve a little amount of data in single-user mode, Access might be ok (on Windows machines). If you need more data and Multiusermode and Speed is important), but just very standard selects (no views, no stored procedures, no triggers), then mysql might be your choice, or if you need advanced features, use MsSql. If you need a really big ammount of data, use Oracle or DB/2.

    Another issue is always what rdbms is available and well known.

    I hope, you understand my english, too :-)

    Best regards,
    perl -le "s==*F=e=>y~\*martinF~stronat~=>s~[^\w]~~g=>chop,print"

      I wouldn't say that "a really big amount of data" is the only reason to use Oracle:
      1. Scalability/Performance - 9i can use clusters better than any of the others
      2. Scalability/Number of Users - every version from 7.0 up can use MTS (and more in newer versions) for servicing extreme numbers of users (10K's)
      3. Reliability - 9i (or even 8i) almost completely transparent (ie no need to reconnect) failover
      4. Hardware independence - everything from a Win95 (or if you really know what you're doing ... DOS) to Mainframes via virtaully every flavour of *nix can support an Oracle database
      5. Features - stored procs in PL/SQL and Java, pre-compiled SQL (aka cached queries) and PL/SQL (in 9i), Views, snapshots, full text indexing, web based administration, triggers (on startup, login and shutdown not just insert/update/delete), full audit facilities, security (built in encryption for starters)
      6. Metalinks/Technet - a *useful* knowledgebase
      7. and it ships with Perl, Apache and mod_perl :)

      I don't work for Oracle, and the phone support is pretty much summed up as "upgrade!", but with everything from Personal Oracle Lite to Enterprise Edition freely available for download it's very easy to get started.

      Just my 2p worth :)


      I think what you need to do is ask: What is the right database for me to use?

      Perl has drivers that allow it to connect with MySQL, MSQL, ODBC and many others. You are using DBI and DBD to connect, after that, the type of DB you are using is irrelavent from perl's standpoint.

      From here, concentrate on the database for your needs.

      Do you need a transactional DB?
      Do you need a relational DB?
      Do you need speed over functions?
      Is price an issue?
      What do most of your clients use?
      What is the size of the company or job?
      Will you DB be heavily used to monetary transactions?
      What OS does the DB support?

      I have been using MySQL with perl for almost a year. I have also used perl to access ODBC databases and perl does the job well. I think you may be asking the wrong question.

      I use MySQL for the following reasons:

      -It is free for me and cheap for my clients.
      -It is easy to install and easy to manage.
      -It works on many platforms, but works very well on linux.
      -It is very fast.

      Oracle is great for large companies and has functions for pretty well anything you could want. However, you will pay for this with dollars and speed. It is transactional and fits in very well for e-commerce situations where losing a transaction or corrupting a transaction can prove costly.

      MySQL works well for non-transaction based applications and in small to mid sized companies. It is fast and designed to be so. It has few functions compared to ORacle. MySQl is cheap... I could go on, but you need to determine what you need and then create a proposal table to see which DB scores the best


        Why isn't MySQL good for transactions? I've not worked with any databases, but would like to get into it. Only reason would be for e-commerce solutions.
        And if MySQL isn't good for transactions, is PostgreSQL?
        Just wanting to get a feel of what database system I should start fiddling around with when I get the free time to do so.


      If you need advanced features, I would also recommend PostgreSQL. It's open source, reasonably well-known, and contains a lot of the standard features (stored procedures, views, triggers, etc.) Perl support is very good. However, my experience is that it doesn't necessarily deal well with huge data sets .. for that you probably want Oracle.

      One thing to consider if you're a major Perl geek: MySQL either has or is developing a "native" programming language (similar to PL/SQL or PL/PGSQL) but based on Perl. I haven't worked with this myself, so I can't vouch for it, but it's a data point to consider.

      Hope this helps.

Re: Perl and Databases
by grep (Monsignor) on Mar 28, 2002 at 16:33 UTC
    There is crash-me provided by It gives a feature by feature comparison of many popular databases. It is provided by but it seems unbiased.

    grep> cd /pub
    grep> more beer
      Of course it seems unbiased, if you're using it as a source of information. But, it states things that are widely disputed in the One True Free Database War. My only point is, there is so much BS on the topic published by all involved parties, that the only opinion anybody should regard as unbiased is their own. (assuming they're non-partisan...)
      Snazzy tagline here
Re: Perl and Databases
by trs80 (Priest) on Mar 28, 2002 at 17:35 UTC
    I "recommend" MySQL. I have been using it since 1997 and it has proven to be very stable and robust, it's support from various firms has steadily increased over the years and some of its current release options include transaction support, which were for sometime one of the reasons not to use it. But one of the nicest things is that it is built for multiple platforms and you can share your tables between them, that is copy them from one OS to another if you use the newer table formats. You do have to alter the case sometimes when coming from Windows to Linux, I just use this script to help me out:
    # invoke with <script_name> /var/lib/mysql/your_database chdir $ARGV[0] @names = <*.*>; foreach (@names) { my $t = $_; if ($t =~ s/\.my([id])/\.MY\U$1/) { rename($_,$t); } }

    I don't use MS SQL 2000 because it has limited datatypes and its reserved word list conflicts with what would tend to me some more common table names ('user' for example).

    Oracle is good if you have the money to support it, but you really need to have a problem large enough to really get your moneys worth out of it.

    PostgreSQL is a good open source database engine as well, it has a very large feature set and includes object support. It is shipped with most major Linux distros as well. Is there a current stable port for Windows? The last time I looked at PostgreSQL its Window port was way behind the main release.

      I know this is getting OT, but how does MS SQL Server 2000 (or 7, for that matter) have limited datatypes?

      M-x auto-bs-mode

        My issue was with the lack of more date time datatypes out of the box. I know you can create your own datatypes. I must admit I didn't get indepth into MS SQL 2000, I am sure it is a wonderful product.
Re: Perl and Databases
by MRossland (Novice) on Mar 28, 2002 at 23:18 UTC

    Well, you'll probably be using DBI, so it takes care of all the different 'interactions.' (DBI is a generic database tool that handles all the complex stuff between your code and the DB ... search for it on CPAN).

    MySQL and PostgreSQL are Open Source databases (so, they're free). MySQL is fast and PostgreSQL is powerful. Most folks that I've talked/chatted/emailed recommend PostgreSQL.

    I'd stay away from msSQL, Access and Oracle becasue of the price. PostgreSQL should do what you need it to.

    Check out this link:

    It's the O'Reilly PostgreSQL book online (it's a legal copy by the authors ... part of the Open Publication License).

    Hope this helps!

PostgreSQL for stored Perl procedures
by Aighearach (Initiate) on Mar 29, 2002 at 01:01 UTC
    I use PostgreSQL, for a few reasons: Excellent performance and scalability, good transactions, and easy to extend. My favorite thing is the Perl integration.

    PostgreSQL supports an interpretted language called PL/Perl for writing stored procedures. What this is, is a perl interpretter running inside the plperl module, running the stored Perl inside a sandbox. The nice thing is, it's all open source, so you just edit the PL/Perl source to add functions to what can be used in the sandbox. Then, you can let the database itself dispatch your code for you. This is nice for a few reasons:

    • It moves database related processes off of your web/other servers, and places them on your database server. There is no good reason for large database accesses to slow down a website if you have seperate servers for them, but this is often the case.
    • It allows you to change your Perl database code without touching the clients. So if you have 34 servers accessing your database server, you only change the code in 1 place. (unless you're changing the agreed schema)
    • It allows for a higher level of specialization for each piece of code, encouraging the practice of building a system out of small bricks with well defined tasks.

    Snazzy tagline here ### Changlog ### # 1061536574: fix mispelling of "Perl" (was "perl")
Re: Perl and Databases
by Ryszard (Priest) on Mar 29, 2002 at 00:50 UTC
    I've used Oracle and Postgres quite successfully with Solaris, HPUX and linux.

    The oracle databases i've used are in the order of 1Tb with several tables of 55m+ rows. The main thing you really have to be careful of is the way you construct your queries, rather than how you execute them via perl. Make sure you make use of explain plan to tune your queries, especially if your data is "worthy".

    The main down side with Oracle is the damn cost. Its *really* expensive. Cost aside, it does scale very well, there are lots of resources out there for it, from skilled professionals, to tutorials, to a very active community.

    After a little research, I chose postgres over mysql maily because of the transaction based approach. I use it on my desktop for some web stuff, information management and generally small stuff. I've also used postgres as a base to develop some Oracle web apps. Some of the stuff requires some small changes (ie the date functions, O=sysdate P=now(), and the table creation stuff wrt specific datatypes, constraint syntax et al)... All very minor stuff.

    Postgres as survived being thrown across harddisks, disk failures and reinstallations, without losing a byte of data. I must say I treat it pretty badly, and has survived very well. Given the stability and the cost (free) it was a real boon for me to get/use and learn.

    As most people have already said, your question could be better rephrased as, "Which database is the best for me?". The only thing I say, would be it must be SQL 92 complient (or use it as a base). I say this becuase, you dont want to go down the road of learning (or inventing) some vendor specifc language (if you choose an RDBMS)

    I dont know if anyone has mentioned OLAP databases here yet. There are a couple out there, Oracle Express, Essbase, however i'm not sure of their interfacability to perl. I've mentioned them because they work really well with specific types of data. For example, i've used Essbase for financial type data (management and financial accounting). Altho' i didnt use perl, they suited the data perfectly.

    I guess the point is keep an open mind. Perl/RDBMS may not be the answer to your problem.

Re: Perl and Databases
by mt2k (Hermit) on Mar 28, 2002 at 19:30 UTC
    Okay, don't kill me for suggesting something that isn't really a database, but if you do not have access to somthing like Oracle or MySQL, a few simple lines of perl code can get you up and running if you use Data::Dumper. What my example does is print a copy of the %db hash structure to a file that you can later read in using a do() statement. Here is some sample code:

    #!/usr/bin/perl use Data::Dumper; #Use the Data::Dumper module use Fcntl ':flock'; #Need the flock module also &dbfile("open", "database"); #open database for reading & writing #You can do any data struture you want with hash %db $db{'hash'} = "hash_value"; push @{$db{'array'}}, "array_value"; &dbfile("close", "database"); #save and close database #If this is a CGI script, uncomment the following line: #print "Content-type: text/html\n\n"; &dbfile("open", "database"); print "Hey look at the value in \$db{'hash'}: $db{'hash'} \n"; print "And what's in \$db{'array'}[0]?: $db{'array'}[0]\n"; &dbfile("close", "database"); #Sub - opens/closes databases sub dbfile { if ($_[0] eq "open") { #open a database open LOCKFILE, ">>lock.lck" or die "Error! Could not lock database!"; flock LOCKFILE, LOCK_EX or die "Error! Could not lock database!"; do "$_[1].dat"; } if ($_[0] eq "close") { #save & close database $Data::Dumper::Purity = 1; $Data::Dumper::Indent = 0; open FILE, ">$_[1].dat"; print FILE Data::Dumper->Dump([\%db], ['*db']); close FILE; close LOCKFILE; %db = (); } #mmm... not opening database or closing. #What are you trying to do?! if ($_[0] ne "open" && $_[0] ne "close") { die "Error! Wrong parameter + passed to db_file()!"; } }

    It is missing some stuff such as strict, making sure you don't open 2 databases, try to close a database that does not exist, or even making sure that you save to the same database that you opened (though this is not really a bug; it would allow you to copy a database), etc. But it gives you a general idea, so I figure it is a good enough of an example.

    Also, here is the contents of file "database.dat" from my example. You'll notice I have taken out indentation to get rid of the extra space needed for all the spacing.:

    %db = ('array' => ['array_value'],'hash' => 'hash_value');

      If you don't have or can't install one of the relational systems discussed elsewhere here, you might instead look at DBD::SQLite, which includes a lightweight SQL database in the distribution. It's extremely fast and works great as long as you're not using it in a simultaneous read/write multiuser fashion, It also provides an upgrade path to a relational system if you decide to upgrade.

      M-x auto-bs-mode

Re: Perl and Databases
by Michalis (Pilgrim) on Mar 29, 2002 at 09:05 UTC
    I have used mysql, postgres and Oracle, so I'll speak for what I know.

    mysql is fast, but... so what? With mod_perl and persistent DB connections, plus my sites don't really have that many visitors, I don't really care about the DB's speed.

    Oracle is well known, has stored procedures, is capable of advanced clustering and such, but it's sooooo expensive, that I would better forget about it.

    PostgresSql, is the best choice for me. It has triggers etc, it is as fast as I need it to be, it supports transactions (which, combined with triggers and rules, can take MUCH of the code out of the web, putting it into the RDBMS itself, thus being much faster than mysql IMHO :-)

    The best thing about postgres though, (in conjuction with perl of course) is the ability to write stored procedures in perl (you'll have to build shared perl).

    I hope I helped a little bit.
Re: Perl and Databases
by S_Shrum (Pilgrim) on Mar 29, 2002 at 02:02 UTC

    What everone has stated above are valid's WHAT you want to do with it that is important.

    If you are just looking for something that reads data from a flat file or even a custom file structure, DBI with DBD::AnyData works great (and is free). It has support for using user-defined FORMATs to access various types of file structures: CVS, Pipe, Tab, Fixed, HTML Table, MP3, Weblog, Password, etc. AnyData also support custom FORMATs, if you wanted to go that far. You can create a new FORMAT and AnyData could access the data structure based on the info in the format file.

    Speed-wise I am not too sure about it. Most of my data bases are less than 1000 records in size so speed is not a BIG issue yet ;) I'm in the process of timing outputs from AnyData and MySQL...I'll try to post my results when I'm done.

    Sean Shrum

Re: Perl and Databases
by ignatz (Vicar) on Mar 29, 2002 at 13:45 UTC
    > Is there a "recomended" database to use with Perl?

    No. Perl is perfect with ANY database. DBI is the most wonderful thing in the world.

    (Added with slight edits)
    > tell me some good and bad facts about databases

    • MySQL: Fast, free, nice tools / Toy Database
    • M$ SQL Server: Hard Core, nice tools, cheaper than Oracle / Evil, only on NT
    • Access: Easy to use, comes with Office / crippled by M$, evil, only on Windows
    • Oracle: Can handle anything, looks great on the resume / Fat, bloated, ancient code base, Expensive as hell, pain in the ass to admin, not FreeBSD friendly
    • PostgreSQL: Fast, free, Real RDBMS / Designed for Unixesque systems, hard to find Hosting
    ()-()                                                      ()-()
     \"/    DON'T   BLAME   ME,   I  VOTED   FOR    PACO!       \"/
      `                                                          ` 
      Gee, I think we can detect which way you're bias inclines you without much difficulty.

      For my 2 centidollars, I use MySQL on Linux with much success. I have found it to be very fast, even with large datasets, although my idea of large is much smaller than some of these TB cowboys. I mostly work in lowly GB. ;-)

      "Non sequitur. Your facts are un-coordinated." - Nomad
        My comments concerning MySQL stem from a few things:
        • Experience working with High Traffic sites that had some painful crash and burns using MySQL. (Back your ass up, baby!)
        • The fact that MySQL cuts a few RDBMS corners that I consider important in order to get a speed boost.
Re: Perl and Databases
by chhe (Sexton) on Mar 29, 2002 at 12:46 UTC

    I think most of my fellow commentors got the point, there is no good and bad as such at least in software development, it depends almost entirely on the context you want to use the database. For the "persistent storage" relevant context of your application you can formulate your requirements to a "database management system". My fellow commentors have outlined many possible requirements to "persistency": efficiency, cost, standardization, host-language integration, portability, current access, volumnes, data integrety upon failure, atomicity of transactions with the database, what you actually want to do with the storage, why are storing it anyway, what are your query requirements etc.

    Even you say for example Oracle, you will have formulate your requirements, since many database management systems cover a wide range of application profiles and therefore need/can to be customized to your specific requirements. This often not even the easiest thing to do.

    One requirement is also how and with which language(s) you want to access the "persistent storage". Since your have written to Perl Monks, i assume you want to access the "persistent storage" with Perl. Here you have also a wide range of options, all very valid applied in the "right" context.

    You have "perl managed persistency options" for "streaming" perl data, like FreezeThaw, Data::Dumper and Storable. All great for what they do.

    You have "database interfaces" for perl for various different database technologies, relational and others:

    You can write your own "persistency option" with good old files:

    All most probably great options depending on the context, i would not want evaluate, without knowing your requirements .....i am aware that these are not so much "database" options, but options accessing "persistent" storage, but maybe that input will also help to find the "right" database

Re: Perl and Databases
by mattr (Curate) on Mar 30, 2002 at 08:11 UTC
    Perl works well with most databases and also provides many other data storage alternatives which might be sufficient for you, such as working with Berkeley DB object stores.

    Hopefully you can provide more info about your application, or you already know which database is best given that it works with Perl..

    One note I should make about Mysql and PostgreSql. Yahoo runs on Mysql; it is not a toy database. Some people think it is a "toy" because it was not originally ACID compliant, however 1) it is a very cost-effective way to do lots of reads, and 2) there are now one or more table types which handle transactions. PostgreSql is (for me) harder to use however it feels like a cheap Oracle, meant in a good sense; it has very nice advanced features and it has in the past year or so become much more stable and much faster. Mysql and PostgreSql are in a war about stats and also the code of each is continually improving, for example Mysql's transaction support and also replication is new.

    They have different philosophies and may be better for you depending on your needs. But which free db you pick doesn't really matter as far as Perl is concerned. Many people use Mysql or PostreSql it seems. Unless you already know that you must use Oracle, I'd stay away from it since there is quite a lot of learning and expense involved. MS SQL seems quite nice if you are a Windows-only house, however I am very unimpressed with the way MS dealt with a bug in it originally (the one that lost nuclear data), and with Microsoft in general. I'm not saying they're evil.. but I think if you happened to come across some kind of a bug you would get it fixed more quickly with some other group, or you might be able to fix it yourself or hire someone to do so, since you have the source code.

    Finally, if you stay away from using db-specific features, you can use DBI (or maybe DBIx) to abstract data from the database you use. Theoretically this means you can switch databases painlessly. All I know is I once switched from MySql to Oracle and it wasn't painless! ;)

    Anyway, databases are a big subject but I probably would recommend keeping things simple so that you can change databases some time if you like, and (assuming you are not a db professional) start with something that combines a lot of power with little effort like Mysql. Or PostgreSql if you want, many programmers find great satisfaction and fulfillment in it. Good luck!

Re: Perl and Databases
by DaWolf (Curate) on Mar 29, 2002 at 17:33 UTC
    Hi there.

    I don't have too much technical knowledge to talk about db's, but I can give you some feedback based on practical experience.

    mySQL - It's free, easy to use and very powerful and stable. Thousands accesses per day are smoothly supported. Never had any problems with it. It's one of my favorites. As good for beginners as for advanced developers.

    Oracle - A pretty known db, Oracle is expensive, but also very good for large applications. It's a bit complicated for beginners, but not too much. A good choice if you have money to invest on, and if your db is a pretty big one.

    MS Access - Well, I think Access is the worst choice of the three. I'm developing an app on it right now, and having some troubles. Access is not intended for big apps or for too much people accessing it simultaneously.

    These three works perfectly with Perl, thanks to the marvelous DBI module.

    BTW, your english is perfect.

    Greetings from Brazil! : )

    Er Galvo Abbott
    a.k.a. Lobo, DaWolf

      A caveat on 'works perfectly with Perl', MS Access will only work with Perl on Win32 via DBD::ODBC or Win32::ODBC - there is, as far as I know, no way to use Access databases on Unix - you can of course use DBD::Proxy to a small server proxy on a windows machine if you really must.


Re: Perl and Databases
by Spenser (Friar) on Apr 04, 2002 at 00:54 UTC

    You've probably received more comments than you want or need.  However, I will call your attention to a recent article in e-week (part of Ziff-Davis and connected to PC Magazine) comparing the top database programs:  Oracle, MS SQL, mySQL, and a few others.  Oracle and mySQL seemed to do the best when compared on performance, and when you consider the basically free price of mySQL, it did the best.  However, there were some differences that are worth knowing that don't necessarily make any one database the absolute best, as many Perl Monks have indicated above.

    This e-week article was based on the results of an extensive comparison that they did amongst these databases.  They pushed the databases to extreme levels: for instance, they tested them for four weeks, retrieving something like up to 600 pages of data per minute for eight continuous hours per test to see if each database could hold up and to see how many pages of data they could retrieve per minute.

    It's not a long article, but the process they went through and the results of their intensive testing is quite amazing.  It's a good background article for anyone who works with databases to read.

    That's Spenser, with an "s" like the detective.

Re: Perl and Databases
by digiryde (Pilgrim) on Apr 11, 2002 at 04:37 UTC

    Most of the comments are right. Define your needs, fit the definition with the tool that fits best.

    However, there is one thing that is mostly missing from almost all of the posts that ought to be very heavily emphasized

    In most cases, when you first create your application, stick to only ANSI standard SQL. Stay away from the proprietary stuff from the database. Unless you want the pain of changing over at some time in the future (or the pain of being stuck with what you have). There are situations where this is the wrong path, but those situations tend to be self-evident (like must use Brand A ERP in same db engine, or your DBAs are incapable of handling a simple DB)

    I normally start with a mysql database (its free, runs great and provides most everything a project needs) unless transactions were required (though that may no longer be a problem since transactions are included in some MySql table types). Then we do the project. Lower entry cost is a wonderful thing. If the needs of the project change in the future (never seen an exception unless the project dies), then you can reevaluate the situation and switch. If you have coded using non-standard stuff, then you will have big issues.

    Now, I use MySql because it is what I am most comfortable with, but PostgreSQL does a darn good job as well. I just am not as experienced with it.

    So unless you have a standard in company to work with (as mentioned above), I start with a freebie and build a project from there to minimize costs. Once a project starts showing its value, then I might look at a non-free db if there are issues that are not being handled.

    I wish you luck!

A reply falls below the community's threshold of quality. You may see it by logging in.