Best database for small applications and tools

Question

What is the best database for small applications and tools? I used paradox, moved to SQL Server, now I use Blackfish. But are there other small and free databases for small applications and tools. Thanks

Answer 1

For single/non-concurrent applications:

SQLite is very nice for small, non-concurrent applications. It's easy to deploy and use since the DB is typically just a single file and the libraries to access it are very light.

If you're deploying a simple app or tool, this is really, really good.

For concurrent users/applications, or large datasets:

You have a lot of choices:

• MySQL
• Postgres
• SQL Server Express
• Others

The biggest draw back is that they are heavier to run and can be more difficult to deploy.

They do have a lot larger feature sets, though.

Answer 2

Do you need concurrent connections/transactions to the database?

If you do not, SQLite is the way to go. Lightweight, can be packaged with your applicaition, so deployment is really easy.

If you do need multiple connections, I suggest MySQL. Personally I'm not much of a fan of MySQL (actually, I really don't like it) but I have to admit that it is the best database for lightweight server applications by far.

Answer 3

SQLite has already been mentioned but there's also Firebird and SQLServer Compact Edition.

VistaDb is also available however it's a commercial product

I've personally used the SQLServer compact edition and found it really nicely integrates with .net however there are definately some limitations which you should check out.

Answer 4

H2 is quite interesting.

It supports both embedded and client/server setups

Answer 5

If you by small you mean no installation/embedded databases definitely check out SQLite and Firebird Embedded. Neither require any sort of installation and, while they can only handle a single concurrent connection, are feature rich as far as SQL implementations and Relational Database features. Both are pretty platform agnostic and have delphi drivers as well I think.

If by small you mean easily configurable and and small footprint check out Firebird and MySQL. Both are pretty portable across platforms and pretty feature rich. I'm much more of a MySQL fan now that version 5 has given it some more basic relational database features.

I used SQLite with great success in an application as a file structure, each file you opened was it's own SQLite database. I've also had great success with Firebird with a product that scales from a single connection and a a couple megabytes of data to installations of 30+ concurrent connections and 10's of gigabytes of data. All with almost no tweaking.

Answer 6

If you want to do even better than SQLite, I'd recommend DISQLite3.

It is a relatively inexpensive product based on SQLite, but with enhancements to work in Delphi very quickly, with very small overhead, and with command-based calls - which I like. It is actively under development and works under Delphi versions from D4 right to D2009.

You'll be hard-pressed to find another that is as fast or uses such a small memory footprint.

Answer 7

id have to say im rather happy with NexusDB

it works client/server or fully embedded, simple enough you can have both in your app and switch between them, depending on your clients needs

• the embedded DB is free,
• client/server "Priced per developer" is AU$ 750
• No cost per install.

Answer 8

Accuracer http://www.accuracer.com/ is the best fastest, smaller, single-file Delphi BDE database replacement I happily use:

• Client/server database engine with server component and custom messages
• Easy-to-use multi-user (file-server) database engine
• Multi-thread access
• Storing all tables inside the single database file
• Embedded database: no BDE, no DLLs
• SQL'92 (DML & DDL) support
• Referential Integrity support (SQL'99 compliance)
• Fully compatible with standard DB-aware controls
• IProvider Support (ClientDataset)
• Small footprint and low memory usage
• In-Memory tables support for the fastest data access
• Varchar and BLOB field types with optional data compression
• Strong database encryption with wide variety of algorithms and modes
• Transactions support with READ COMMITTED isolation level
• BatchMove component
• Reverse engineering (tables to SQL script export)
• Backup and Restore support
• Triggers - database and server events
• Capability of database embedding inside the executable file
• ODBC Driver available
• Windows / Linux cross-platform database engine
• No royalties
• Native Delphi database, full source code available
• Ideal as BDE replacement - does not require any .dll and includes data transfer utility.

Answer 9

I am pretty sure you want something embeddable or preinstalled on every computer, that rules out the BDE and pretty much any main stream database, apart from MS Access.

Access only requires ADO to be installed which is true for almost every Windows computer nowadays. It also allows concurrent access from multiple programs, multiples users and even multiples computers but it isn't the most stable available. I have used it quite often and it is a viable option, if you don't forget your backups.

Then there is firebird embedded which is free even for commercial use, while mysql embedded is only free for GPL software.

Another option is a tbdf, a pascal/Delphi implementation for accessing dbase tables. I have never used it but I always wanted to have a look at it.

Answer 10

Firebird can be used either as an embedded server or as full featured SQL server in two tier client-server applications.

Answer 11

Me, I've found DBISAM and its successor, ElevateDB to be very reliable, and have great support. Embedded or client server, or both. Elevate Software

Answer 12

I too have found DBISAM to be very good. It handles multithreaded and multiprocess accesses just fine, even when using very different drivers (native driver in one program and the ODBC driver in another banging on the same file database). While not free, it is royalty free, so there's only a single upfront cost.

Answer 13

on *nix, you can use dbm(http://en.wikipedia.org/wiki/Dbm) or berkley db(http://en.wikipedia.org/wiki/Berkeley_DB). To answer which is the best option is dependent on the concrete application.

Answer 14

I have workd with MSsql server , Oracle, Sqllite, MySql, but for my personal projects i use only Firebird, emambed and server solution in less than 2MB. It's amazing fast, and there is lots of good development tools e.g. IBExpert.

If you work with delphi than you don't need any new components just use Ibx that comes with delphi.

Answer 15

If you are not an expert or dba - SQL Server Express wins hands down * easy to do almost everything * better optimizer than MySQL (hence much faster) * better ui than MySQL * better stability than MySQL (especially recent versions)

If you are an expert or dba - PostgreSQL wins * most things need to be done command line

I've used MySQL, SQL Server Express, DB2, and Oracle extensively. I've used PostgreSQL minimally, but have gotten an expert to show me its functionality.

MySQL is the big suck right now... In fact, I'm starting to get advertisements from SUN about completely unrelated products.

There are restrictions with using SQL Server Express (4GB size and single processor), but you shouldn't hit these in a small system.

Answer 16

SQL Server Compact Edition (CE) is best for .net applications... I have used it with C# and when you do a publish from Visual Studio, all of the necessary drivers are installed. Will not work for everything, but it integrates better than SQLlite in visual studio.

Answer 17

Hi,

Depending on your needs, you may want to try CodeGear MyBase: basically, you can use TClientDataset component to have an inmemory dataset with persistence support on file. It comes in the box, easy to use, requires no external DLLs(well, you should deploy Midas.DLL except that you can include the Midaslib unit in your project).

Andrew

Answer 18

Though I will be criticized a lot I would vote for Access.Yes, really let me tell you why:

1. I have tried Nexus, SQLite, VistaDB, Firebird Embedded and believe it or not Access mdb file was the most compact one. My 25 MB db becomes 50 or so when imported in SQLIte, SQL Compact, and all other equivalents I have tried.
2. It has really the best native UI for database filtering, sorting, grouping. Yes, I know that there are bunch of free, shareware, etc. tools for all the other dbs. But I really like the Access way of grouping data (a very similar to the Developers Express way of doing it)
3. It has average fast support from ADO, ADO.NET, ODBC, JDBC, and actually almost any database access framework.

I must admit that still there are "features" that drive me crazy: 1. Terrible security 2. Creation of .ldb file for locks 3. No intelligent support for stored procedures. I know that I can write VB modules but this is far from good 4. Discontinued support 5. No native driver 6. Necessity to have MDAC installed (actually all upto date Windows have it).

Just my $2.

Answer 19

Advantage Database Server from Sybase works great in the 5 - 25 user space. It has client kits for most development languages and has a free embeddable engine as well as a client/server service. It is a full relational database management system with support for Views, Triggers, Stored Procedures, Replication and Online Backup.

Answer 20

We use C++ Builder, a close brother to Delphi, and I spent 6 months locating the right BDE replacement. I selected SQLite and we began the conversion a few months ago and have a beta site already. SQLite has been impressive. The beta site has 10 million records and the reports take 5-10 seconds to run in SQLite. For deployment, you simply include the sqlite.lib 255k file in the application folder. Period. As you know the BDE is slightly complicated to deploy and many of the other databases mentioned here are even more difficult. Again, SQLite just requires you include the 255k lib in the folder.

Answer 21

I'm Firebird user and fan and not the only one :

• it's free (not like MySQL)
• it have good backup (what I call good backup is something they want to put in MySQL 6)
• it have more features (Common Table Expressions)
• there is less bug. (I also use MySQL and was happy to put MySQL 5.1.32 in production but there is simply a little bug in auto increment

For more, this bug was already corrected in MySQL 5.1.22 !

Interesting links here and here

Answer 22

Another vote for SQLlite. It's fairly widely used. For example, it's embedded in PHP, part of Mac OSX and the iPhone, so appications can store data.

Answer 23

I recommend Absolute Database. It has a small footprint and is fully embedded. It is a single file database with SQL support.