views:

968

answers:

18

I am creating a desktop application written in Delphi and I am looking for a database for my application.

I'm a bit overwhelmed by the number of available options. I'd really appreciate some recommendations and insights from other developers based on their experiences.

Critical factors

  • Low or no buyin cost.

  • No distribution fees.

  • Easily handle up to 35,000 records with no problems, ideally up to 100k.

  • Supports multiple tables (in this case up to 10)

  • Blob support (binary objects, images, etc.)

  • Can be distributed as part of the application install set. I.E. User does not have to someone else's website to download database installer.

  • Can be installed and configured by install set with minimal user interaction. My target userbase is not technically inclined.

  • Reasonably fast performance.

  • Support for standard SQL statements (or something reasonably close)

  • Support for multiple indexes

Less critical

  • Size of database installation
  • Size of database once installed on user's system.

Not critical, but nice if available

  • Multi-user support
  • Encryption
  • Scalability

Thanks...

+9  A: 

A good option would be to use Sqlite. There is a wrapper for Delphi 2009.

It will handle this size of DB very, very well, and is completely free to use and distribute, stable, robust, and supports pretty much all of your requirements.

Reed Copsey
I actually can't think of one of the things that is listed that SQLite wouldn't support. Multi-user might be the only things depending on what the specific requirements are, as the locking is database wide versus highly atomic, but for most applications this is actually not a problem.
Kitson
Yeah, and the install is super easy - just copy the file...
Reed Copsey
There is an alternate wrapper at http://www.aducom.com/. This is a TDataset replacement for D4..D2007.
SeanX
Thank you for the information - definitely a contender.I am in the process of checking SQLite out.
TheSteven
+1  A: 

PostgreSQL is a great database server. It has a very light footprint, and you can customize the install very easily. It weighs in at or < 10mb to ship, and can be configured in many different ways to optimize performance or system usage.

There is a delphi interface: http://dbslim.berlios.de/

One thing about PostgreSQL is that it scales very nicely (from very small deployments to large ones.) I am using it in a few projects, and I have been very pleased with it.

Christopher
PostgreSQL seems capable, possibly a contender although Firebird seems to have a stronger following.
TheSteven
I looked seriously at Firebird, but it was not nearly as feature complete for my application.
Christopher
+20  A: 

Firebird is an excellent choice. It meets all of your requirements, there are excellent administrative tools available for it (IBExpert, even the free version is the best tool I have ever used for a DB), and you can write your own user-defined functions in Delphi for special requirements your application might have. The support group at Yahoo groups seems to jump all over any support requests anyone submits. All in all, for my desktop application, I don't think I could do better.

Thank you for your input - definitely a contender.Firebird is on my checkout list.
TheSteven
Don't forget that you can also use the embedded version instead of the full server when there is no need for multi-user
Alan Fletcher
Two biggest benefits I see are you can use it in commercial applications without restrictions and whether you use the embedded library or one of the server implementations its the same db engine so there are very few surprises.
codeelegance
+1  A: 

DiSqlLite (another wrapper around SQLLIte) is a good choice. There are plenty of management utilities to help you get things started. Deployment is very simple. I used it in a multi-user project by writing a delphi n-Tier server that performed all of the database access.

skamradt
A: 

I've had a lot of luck with DBISAM. It's been superseded by ElevateDB, which I would use for new projects.

I also like that I can do an XCopy install.

Bruce McGee
Looks interesting, seems that you can use it to build Windows CE applications too.Thanks for the information.
TheSteven
+1  A: 

ElevateDB is an embedded SQL database engine that can be compiled directly into your application and offers local single and multi-user access (file-sharing) and client-server access with the provided ElevateDB adheres to a subset of the SQL 2003 standard (ANSI/ISO 9075).

Some of the standard features in ElevateDB include:

  • Small footprint
  • Multi-threaded
  • ANSI/Unicode
  • Collations
  • Online backup
  • Serialized transactions
  • Constraints
  • Triggers
  • Views
  • Jobs
stukelly
+2  A: 

I have had good luck with ComponentAce's Absolute Database. It compiles into your application so that you just end up with your program and the database file. It is SQL 92 compliant with blobs, doesn't bloat your code too much, has a multi-user option and is available starting at around 100 euros. Source code and multi-user options top out at around 350 euros, not cheap but no distribution licenses.

lgallion
+4  A: 

Funny no one has mentioned this yet, but SQL Server Express (free) would do everything you're after. The Express edition has a database size limit of 4GB. You can use Devart's SQL Server Direct Access Components (SDAC for short) to communicate with it in your Delphi app.

Also, it might be worth looking at the 2005 edition specifically since its install requirements aren't as heavy (2008 requires .NET 3.0).

Pauk
From my understanding (which is possibly old or erroneous information) you could not distribute SQL Server Express with your application. You could have a link for the end user to download it but not have it bundled with your installer. If this is not true, I would consider it for other applications.Also for this particular application I'd rather have something 'lighter' with less system requirements. Currently there is nothing planned in the app that would require .NET.
TheSteven
@TheSteven: I don't think that's true, or has ever been true. You only need to register to have the right to redistribute: http://www.microsoft.com/sqlserver/2005/en/us/express-redistribute.aspx. I don't think MS SQL Server Express is the best choice for your needs, but still...
mghie
Thanks for the clarification. I also don't think MS-SQL Server Express for the right choice for this job.
TheSteven
+1  A: 

Take a look at NexusDB. We've found it to be solid and speedy and it has a good community around it.

Nathan Sutcliffe
I've heard good things about NexusDB, but not a consideration right now due to economic factors.
TheSteven
A free version of NexusDB V3 Embedded database engine is available:http://www.nexusdb.com/support/index.php?q=FreeEmbedded
Erwin
A: 

MySQL is always an obvious choice. You can embed it in your application so you don't need a server (you just need to include libmysqld.dll to get full database functionality).

It's powerful, fast, fairly lightweight, and when needed, you can just switch to a "normal" MySQL server without having to change your code or queries.

No installation is needed.

There's a wealth of free examples, documentation, tools and drivers available for MySQL in general, but also in combination with Delphi.

...........

Here's the checklist:

  • Low or no buyin cost. It has 2 licences: GPL (Free) and a commercial one

  • No distribution fees. As far as i know, you can redistribute it freely

  • Easily handle upto 35,000 records with no problems, ideally up to 100k. I'm using it with tables that have up to 30.000.000 records, and it works without sweating (if you think carefully about your queries of course)

  • Supports multiple tables (in this case up to 10) check

  • Blob support (binary objects, images, etc.) check

  • Can be distributed as part of the application install set. I.E. User does not have to someone else's website to download database installer. just add libmysqld.dll, and that's it

  • Can be installed and configured by install set with minimal user interaction. My target userbase is not technically inclined. No install needed. It embeds in your app

  • Reasonably fast performance. It's very fast I can tell you

  • Support for standard SQL statements (or something reasonably close) check

  • Support for multiple indexes check

  • Size of database installation LibMySqlD.dll is a couple of megabytes

  • Size of database once installed on user's system. Depends on your database of course. It's not compressed

  • Multi-user support No multi-user support in the embedded version as far as I know

  • Encryption Encrypted data files are not natively supported, but 3rd party solutions exist

  • Scalability If your embedded server cannot handle stuff, switching to a "normal" or external one can be done without changing code or queries

...........

Limitations:

You don't have replication or events, there's just a single InnoDB thread, there is no authentication or authorization mechanism, and only your application can access the database (makes sense for an embedded database).

Wouter van Nifterick
Just be careful of the commercial license requirement. If you ship it in software that you charge for, you need the commercial license and that does cost money.
MikeJ
I've used MySQL for web apps and like it. Hadn't considered for a desktop application unfortunately the commercial license requirement makes it a non-contender.
TheSteven
Do any of the downvoters care to explain what's wrong with MySQL? I quite like it for my day to day stuff.
Wouter van Nifterick
@Wouter van Nifterick: In my opinion the only thing wrong with MySQL is the license. Unless you are writing an open source application, you need a commercial license which - last time I looked - costs quite a bundle. (In addition I had to fix too much bad code in applications that used MySQL which has somehow rubbed off to my opinion of it as a database.)
dummzeuch
A: 

Again, the results look to be overwhelming as each response is for a different database.

My vote goes to SQLite.

However... it looks like an Access database would do you just fine too. Zero install needed, just Windows, and ADO or similar, and you are good to go.

Here is a link on Access limitations.

http://www.databasedev.co.uk/access_specifications.html

John

Thanks for the information link.
TheSteven
A: 

Why has nobody yet mentioned an MS Access database? The required drivers (ADO / Jet) comes preinstalled with every recent version of Windows (XP, Vista ...), supports multiuser, encryption, Blobs and SQL and is reasonably fast. Mind the maximum database size, though.

edit: I don't really understand why this answer was voted down. I have used MS access dbs with Delphi several times and it worked well. It is not meant for heavy multiuser installations, of course and it isn't the fastest around, but that wasn't a requirement in the question.

dummzeuch
I did above yours! :)
Access perhaps isn't a bad solution for an app this size.Its been a while but I don't think I want to go that way again.Last time I used Delphi with Access I had to create a DLL in VB4 to get around Delphi issues with either creating or modifying the structure of an Access database (its been a while so this issues probably been fixed) also had to rename the file extension because people couldn't use Outlook to email databases or exported records (Outlook by default blocks sending or opening attached MDB files).
TheSteven
@TheSteven: I never needed Visual Basic for anything in my projects. It is possible to create tables, add columns, create indexes and primary/foreign keys, even to add comments to columns in Delphi, if you want to (I usually just ship an empty database with the application that I use for a template.). The only thing I never managed to do was adding a comment to tables.It certainly beats having to ship the BDE.
dummzeuch
Did a little checking. There's been a number of major updates since I last used it. Access would probably be fine and is worth considering.
TheSteven
I currently support a commercial product using Access as a data store and after seeing how easily it corrupts itself I would definitely warn against using it on any application. It would not be my choice for anything I do.
Alan Fletcher
+3  A: 

+100 for Firebird SQL Embedded, a complete RDBMS in a 5-6MB DLL. Supports all your requerimets and a lot more

José Romero
Thanks for info! - will check it out.
TheSteven
You can even embed Firebird in your Delphi .exe.http://www.fikrethasovic.com/fb_embedded_1.0_SRC.zip
HMcG
A: 

DBISAM and Elevate are great.

A: 

My other option is Dbase IV,V format with full-multi user support auto edit refresh stuff. I recommended Topaz from www.softsci.com

I uses it for multi-user and desktop application. Database never crashes and handle under million records in a breeze.

Vitisan

A: 

Using MySQL embedded (libmysqld.dll) is easy and convinient.

But you do NOT have a right to redistribute libmysqld.dll with your app unless you either:

  1. open your app source code to the whole world (GPL license) or
  2. buy commercial license from MySQL (now SUN) – it is not clear how much it costs
careful_guy
A: 

Like Steve, I too am interested in an easy to use database tool. I've not really programmed that much in Delphi for the past 5-6 years. I started out with Turbo Pascal 3.0, went to TP5 bought TP6 called Borland the next day and downgraded to TP5.5. I purchased Delphi 3 Professional, upgraded to Delphi 5 Enterprise and that is what I currently have. I just downloaded a trial version of Delphi 2010.

I've been frustrated with the lack of easy to use and easy to deploy databases that Delphi interacts with. I love SQL and I want my SQL code to be inside stored procesures. I want to be able to test my SQL code and validate my data both inside and outside my application.

I've just spent the past 2.5 hours looking at ElevateDB and reading the Forums. I'm very impressed with the way they answer user questions. I'm hoping this product will work the way I think it will. I just signed up for a trial download.

Cape Cod Gunny
A: 

If NexusDB is too expensive you could try FlashFiler, which is a free open source database written in Delphi. I think it is an ancestor of NexusDB. It has had very little maintenance since it became open source, but it is well documented and meets all your requirements.

FlashFiler is free, supports blobs, very large numbers of records, indexes and tables. It can be embedded in a single-executable program (with no DLLs required), or run as a multi-user client-server database. It is fast and provides both SQL and record level access. There is some light built-in encryption or you can add your own routines.

For example, I have a program with it embedded as a single-user database and the executable is 3MB and it does not require any DLLs. This accesses 50 tables of total size 15MB very easily. (I also use FlashFiler with much larger client-server databases.)

soid