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

Thanks...

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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

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.

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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.

Answer 13

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

Answer 14

DBISAM and Elevate are great.

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

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

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.

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