Using SQLite

[Rene Simons]

Hi Bruce,

Can I use SQLite for a WebServer app?
"Of course you can" you will say.
But is it wise /smart? Are there restrictions that could keep me from doing so.

René

[Bruce]

Of course you can...

>> But is it wise /smart?

That depends a lot on your use case. The limitations of SQLite are reasonably well known - any writes would incur a database-wide-lock.
So (if WAL is off) then all reads would be blocked while writing. If WAL is on writes would be limited to 1 hread at a time, but reads would not be blocked.

I would say it's suitable for low-volume sites, or sites with low-volumes of writes. (so Blogs etc). For sites with high volumes of writes it's likely not the correct choice.

For sites that are mostly "read-only" it's an excellent choice. So if you take www.capesoft.com as an example, where the accessory lists and version numbers, and release dates are all stored in a table it would be a good choice, because those tables are pretty much "read only" with only a handful of updates per _week_. So write blocking would not be a problem.

Cheers
Bruce

[Rene Simons]

Ok thanks,
There will be some writing going on. So for now I stick to tps.
Maybe later I'll upgrade to SQL (heavy).
René

[Ubaidullah]

Hi Rene,

Just wondering: why not just move to MS-SQL ? It's just as easy as moving to SQLite (or rather easier).

Regards,
Ubaidullah Nubar.

[Rene Simons]

Hi Ubaidullah,

That is a good question. To answer that I just Googled "SQLite vs MSSQL" and it came up with the following:

"Microsoft SQL Server is a powerful, full featured SQL system that supports entire ecosystems. While SQLite is a light-weight database meant to be embedded into end programs. SQLite is often useful even to SQL Server professionals when they need to create smaller databases without a full server setup to support."

Add to that the fact that I am not a computer scientist but just someone with a bunch of ideas.
I don't want the hassle of also having to be the support engineer of an MSSQL server.
I Also like the small footprint of the SQLite file system which means that I have more room for my application.
Which would allow me to spend less money on a server that performs well enough.

BUT when I take Bruce's answer in consideration I'd better stick to TPS.
Small footprint, harder to investigate. Choices choices.

Regards,
René

[Ubaidullah]

Hi Rene,

Practically speaking, nowadays MSSQL can be installed and it will run fine without much support needed.
And, if you use MSSQL Express, it will use only 1GB of RAM.

From a programming point of view, it's easier to code for MSSQL than SQLite.

Just my 2 cents.

Regards,
Ubaidullah Nubar.

[Rene Simons]

Hi Ubaidullah,

You are absolutely right.
But this time I stick to TPS.

René

[Ubaidullah]

Hi Rene,

I understand.

Not to flog a dead horse, but....

>> But is it wise /smart?

 

Regards,
Ubaidullah Nubar.

[Bruce]

Hi Ubaidullah,

>> And, if you use MSSQL Express, it will use only 1GB of RAM.

that can be significant. I've run NetTalk web servers on VM's with as little as 1G of memory total, and with tiny CPU's, and in those cases the Ms SQL resources are significant.
(when I started I was running on servers with a total of 0.5 G of Ram, but those VM's are no longer available.)

Using TPS on web server boxes, not across the network, is very solid - I've had no problems there.

Obviously no issue using MsSQL either - wee have a number of boxes with that.

Incidentally if you do go to MsSQL over SQlite, please remember to add a dependency to your service so it starts after the MsSql database starts.

cheers
Bruce

[Ubaidullah]

Hi Bruce,

Agree with you if RAM availability is an issue.

Regards,
Ubaidullah Nubar.