Why Aren't You Using SQLite?

[weirdo12 19]

If you are building a stand alone app that relies on a database, why aren't you using SQLite?

 

We used to use SQL Anywhere as our default database. It was great. The database could be one file that you could copy and move very easily. Like SQLite. But it did have an engine to install which could be done easily with InnoSetup without a separate exe included in the setup file (think SQL Server whatever). We did have have to pay for each install which was fine. But then it was sold to SAP and you know what, they just stopped asking us for royalties. I guess it was so small time they couldn't be bothered.

 

So like 6-7 years ago I thought I'd see if I could use SQLite as a replacement. And it was possible. Obviously the database would work. The issue was with stuff stuff that was done in database - triggers - that we used and could they be done in SQLite. They could be.

Edited October 4 by weirdo12

[Brian Evans 105]

I started with FlashFiler then migrated to NexusDB for both standalone and C/S versions of applications. The former was available years before SQLite came on the scene and the latter was the spiritual successor of the former. Never migrated to SQLite because using C/S tooling during development of even a standalone application makes things easier. Both a database development tool like Upscene Production's Database Workbench and the application itself can access the live database tables at the same time easing debugging and other tasks. Can then ship the application with the same database engine compiled into the EXE but hooked up directly instead of through C/S components. 

 

Originally used FlashFiler as the database engine for an application + database published monthly on a CDROM. With some modifications to the source to open read-only files as shared it worked well for years. Both FlashFiler and NexusDB are royalty free so no per user fees in either standalone or C/S configurations. 

[Der schöne Günther 316]

I am using Sqlite exclusively, because I don't know anything else.

[Markus Kinzler 174]

FireBird (embedded) would be a futher option. The same database file can be used in the server and embedded (client) edition. Triggers are fully supported.

[DelphiUdIT 176]

Normally I always use SQLite as a base for all my applications (configurations, settings and more are kept inside). Then depending on the customer, additional databases such as MSSql, MySql, Firebird are used in addition, but with the server managed by the customer. However, the use of databases in addition to SQLite is sporadic, many customers want to have the data in an open format of pure text such as a CSV file.

[corneliusdavid 214]

SQLite's own documentation lists some reasons why using it might not be the best for every situation.

11 hours ago, weirdo12 said:

building a stand alone app that relies on a database

That's a pretty broad question and could mean anything from a small single-person app with a few records in a couple of tables (where SQLite would be perfect) to a large, multi-user application suite with hundreds of users over a large network with thousands of transactions per minute (where SQLite would be quite inadequate).

[weirdo12 19]

29 minutes ago, corneliusdavid said:

a small single-person app

Okay, then let's go with that description if stand alone isn't clear enough.

[corneliusdavid 214]

2 minutes ago, weirdo12 said:

if stand alone isn't clear enough.

To me, "stand alone" means it's not a plug-in for something else. So, yeah, could be large or small. Thanks for the clarification. :-)

[Rollo62 536]

On 10/4/2024 at 4:57 AM, weirdo12 said:

If you are building a stand alone app that relies on a database, why aren't you using SQLite?

Sqlite can be used in large, production-ready sites, you could have a look what Pieter Levels does with Sqlite, PHP, JQuery only, like back to the roots.
https://news.ycombinator.com/item?id=23359023

https://www.google.com/url?sa=t&source=web&rct=j&opi=89978449&url=https://www.youtube.com/watch%3Fv%3DB-_P0d1el2k&ved=2ahUKEwjJxILl-PuIAxXFSPEDHTKKOr8QtwJ6BAgKEAI&usg=AOvVaw0ESb4TJ_wTRTzhSmsFX7ot

[weirdo12 19]

This guy is also pretty high on SQLite:

 

 

[weirdo12 19]

It might not have been clear in the original post that SQLite is our default database (and it has been since 2021). Using FireDAC, the same install supports SQLite, PostgreSQL, SQL Server, SQL Anywhere and MySQL,

 

[Rollo62 536]

5 hours ago, weirdo12 said:

Using FireDAC, the same install supports SQLite, PostgreSQL, SQL Server, SQL Anywhere and MySQL,

I would not use any of those for a local app, that doesn't really hardly requires any of the big ones.

The locical steps for me would be 1. Sqlite, 2. Firebird Embedded or Server, 3. only if that is not good enough something bigger.

Mysql (or better MariaDB) would be the next logical step (3.) for me,
PostgreSQL is quite heavy weight, but I have not much experience with it, perhaps its capable for doing huge stuff.

SQL Server binds you more to the Microsoft environment, if that is no issue for you.
I was forces to use SQL Anywhere once and I was no big fan of that either.

In the end, all SQL DB should work fine, at the SQL level, then maybe the small differences appear more important.
Like drivers, like OS and general support, like portability, like certain SQL specialities (e.g. full text searc h), and so on.

Generally I tend to stay with the most basic SQL sets anyway, which would allow me to switch easily between DBs anyway.

What I can say is that Sqlite is perfect for local DB under all OS, thats the usual choice.
Firebird server works also rock solid, even when not eagerly maintained, this really astound me how this runs untouched over many years.

Interbase IBToGo is maybe a good choice for cross-platform too.
 

[weirdo12 19]

7 hours ago, Rollo62 said:

I would not use any of those for a local app, that doesn't really hardly requires any of the big ones.

Agreed. That said, there are developers in the space we work in (in fact most of them) that include SQL Server Express as default database for a small single-user app. That or Access.

 

If you have not worked with PostgreSQL you should check it out.

[Die Holländer 45]

42 minutes ago, weirdo12 said:

that include SQL Server Express as default database

Wise developers. MS SQL Express is great and the 10GB restriction is a lot of data.

Easy to install and the database manager studio for managing your databases and

testing your SQL's is also very good.

[Rollo62 536]

1 hour ago, weirdo12 said:

If you have not worked with PostgreSQL you should check it out.

Yes, I checked that, its a very heavy and mighty DB that needs a lot of maintainance attention, IMHO.

It can do much more, what I ever will need probably.

I do like the more light weight DB, which are more easy to handle and with a less steep learning curve.

 

[weirdo12 19]

2 hours ago, Rollo62 said:

It can do much more, what I ever will need probably.

I do like the more light weight DB, which are more easy to handle and with a less steep learning curve.

Of course, if you'll never need to support a client/server installation it makes no sense. 

[PhilPlus 6]

I use MS Access : no installation, triggers, views, all the datas are in a single file but for Windows only. 

[sjordi 39]

On 10/4/2024 at 7:24 AM, Brian Evans said:

I started with FlashFiler then migrated to NexusDB

[...]

Both FlashFiler and NexusDB are royalty free so no per user fees in either standalone or C/S configurations. 

I too used to first embedd FlashFiler and then NexusDB which I really like. But it unfortunately, it works with VCL apps only if I'm right.
I know do only FMX apps and use SQLite instead.

[sh17 26]

i use only sqlite in my server application. All user access it over a REST-Api. So 30 Users are no problem.