Recommendation needed for good database for Delphi desktop app

Question

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

Ginaginder

Is it just me, or is someone downvoting all of the responses? – Deflection

@Bruce some people are just db zealots... or someone was gaming the voting system. Either way, community recommendations are highly subjective. The accepted answer will be just as subjective. – Brigade

Thank you eveyone for your input. There are a lot of good answers and comments in this thread. I did not accept an answer because there is no one correct answer - what is right will depend on many factors that may change from project to project. For the particular app I was working at the time I ended up using an SQLite3 wrapper by Tim Anderson itwriting.com/blog/…. I'm not saying that it is an end-all solution - just that it was more that adequate for the needs of this particular project and worked out very well. – Ginaginder

Answer 1

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.

Finecut

Thank you for your input - definitely a contender. Firebird is on my checkout list. – Ginaginder

Don't forget that you can also use the embedded version instead of the full server when there is no need for multi-user – Negligee

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. – Brigade

Answer 2

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.

Ahner

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. – Masseur

There is an alternate wrapper at aducom.com. This is a TDataset replacement for D4..D2007. – Saladin

Thank you for the information - definitely a contender. I am in the process of checking SQLite out. – Ginaginder

Update on the link for SQLite3 wrapper for Delphi. itwriting.com/blog/?page_id=659 There even a version listed lower in the page for Delphi2009/Delphi2010. – Ginaginder

Unicode version of Tim Aanderion's SQLite3 wrapper. itwriting.com/blog/… Don't know if it works with Delph Xe or Xe2 – Ginaginder

Answer 3

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).

Velda

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. – Ginaginder

@TheSteven: I don't think that's true, or has ever been true. You only need to register to have the right to redistribute: 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... – Splashboard

Thanks for the clarification. I also don't think MS-SQL Server Express for the right choice for this job. – Ginaginder

Answer 4

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

Counterwork

Answer 5

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

Browbeat

You can even embed Firebird in your Delphi .exe. fikrethasovic.com/fb_embedded_1.0_SRC.zip – Addictive

Answer 6

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.

Disorientate

PostgreSQL seems capable, possibly a contender although Firebird seems to have a stronger following. – Ginaginder

I looked seriously at Firebird, but it was not nearly as feature complete for my application. – Disorientate

Answer 7

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.

Abingdon

Answer 8

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).

Cabasset

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. – Greenhorn

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. – Ginaginder

Do any of the downvoters care to explain what's wrong with MySQL? I quite like it for my day to day stuff. – Cabasset

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.) – Legionary

Answer 9

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.

Yoruba

Answer 10

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.

Deflection

Looks interesting, seems that you can use it to build Windows CE applications too. Thanks for the information. – Ginaginder

Answer 11

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

Footslog

I've heard good things about NexusDB, but not a consideration right now due to economic factors. – Ginaginder

A free version of NexusDB V3 Embedded database engine is available: nexusdb.com/support/index.php?q=FreeEmbedded – Civet

Answer 12

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

Mediatory

Answer 13

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.

Legionary

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). – Ginaginder

@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. – Legionary

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. – Ginaginder

Answer 14

DBISAM and Elevate are great.

Noshow

Answer 15

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

Canoe

Answer 16

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

Hoad

Answer 17

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.

Charger

Answer 18

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.)

Triplane