MySql vs SQLite vs MongoDB

[ilan]

hi,

i have started working on an eCommerce website for a customer. for the server-side i am planning to use b4j. my question is about the DB i should use.
i am running everything on my own server so i was thinking of using a local DB like SQLite. is this a bad idea?
the other 2 options are MySQL and MongoDB. so i see the discussion here and i am a little bit confused: https://hashnode.com/post/mongodb-vs-mysql-for-ecommerce-cip3y6265044zya535mhe565v

what would you recommend?
SQLite would be the simplest solution in my opinion. everything is stored in 1 file and no need to deal with other servers.

Difference between SQLite and MS SQL Server - GeeksforGeeks

Your All-in-One Learning Portal: GeeksforGeeks is a comprehensive educational platform that empowers learners across domains-spanning computer science and programming, school education, upskilling, commerce, software tools, competitive exams, and more.

www.geeksforgeeks.org

 

[KMatle]

Depends on the traffic. SQLite slows down (they say) when writing "many" updates/inserts. I would give it a try (WAL mode = ON) When it really slows down, you can then easily switch to MySQL.

 

[Peter Simpson]

This has been talked about many times on this forum, I already say to use MySQL database with the MariaDB connector for anything like that, this included intranets too ?

 

[aeric]

If you have PHP and Apache running then I would suggest MySQL. Just drop adminer.php and it is easy to manage the database.

 

[ilan]

If you have PHP and Apache running then I would suggest MySQL. Just drop adminer.php and it is easy to manage the database.

i am not using PHP all is handled by the b4j server. i build a few really nice projects with node js and MongoDB. what i liked most about it is the simplicity. i can create an object that contains objects inside even arrays and read them back without any need to convert them. there are some benefits to using MongoDB over SQL but again in an e-commerce DB, i am not sure. i will need lots of SQL joins of tables and this is much simpler using SQL than NoSQL DB.

 

[ilan]

how about using MySQL local with XAMPP? my server will run on a google cloud VM instance that is running Debian OS so i was thinking to install on it XAMPP and run the MySQL DB locally. how about the security? is this a bad idea?

 

[aeric]

It doesn’t matter. I also have my B4J server apps running on the same server with different port numbers. I just leave port 80/443 for PHP Apache to run the default page and adminer. I still prefer MySQL and easily manage using adminer.

 

[aeric]

For local development machine I prefer to use Laragon.

 

[Peter Simpson]

how about using MySQL local with XAMPP?

XAMPP uses MySQL. Personally for my client work I only use MySQL server, but on my local development machines I only have XAMPP installed when I'm creating and testing websites for clients.

So in effect I only use XAMPP for testing website that I create, I use MySQL server for the databases and nothing else.

 

[jerry07]

Hi,
Unless you have many tables and more then couple hundreds of inserts per day any of these will work for you.
Seems that you want to use Sqlite, maybe because you know it already?
If you have no preference you may want to go with MySQL as this is true client/server database and little more fit for your setup.


To provide better guidance more information is required:
How many users?
How many inserts daily?
How many tables?

 

[ilan]

Hi,
Unless you have many tables and more then couple hundreds of inserts per day any of these will work for you.
Seems that you want to use Sqlite, maybe because you know it already?
If you have no preference you may want to go with MySQL as this is true client/server database and little more fit for your setup.

thank you for your reply. you are right SQLite is my first choice because i have already worked with it. i am also familiar with mongoDB. MySQL looks a little bit confusing but not something that will stop me to go for it.

i read several articles and i can see that this is a big discussion around developers.

Comparing The Differences - MongoDB Vs MySQL

Understand the differences between MongoDB and MySQL, the scalability and security options for each offering, and which database to use when.

www.mongodb.com

MongoDB is a well-established, non-relational database system offering improved flexibility and horizontal scalability, but at the cost of some safety features of relational databases, such as referential integrity.

MongoDB vs MYSQL for eCommerce? - Hashnode

I am soon to start working on a personal project and am considering to use MongoDB over mysql as I will be using the MEAN stack, any points on why I should use MYSQL instead?

hashnode.com

To provide better guidance more information is required:
How many users?
How many inserts daily?
How many tables?

it is hard to tell. it is for a mobile shop (single shop) i don't think that there will be more than 10-20 interaction/day (at least in the beginning)
maybe 1k-3k users. and tables maybe 5-10.

so as you can see not a too big database. the goal is although to build a system that will fit several costumes so i don't want to make the same work twice so i am thinking already now to build something that can also fit for bigger companies that have several shops and the number can get 10x more.

 

[jerry07]

So I think you may have 1k - 3k users over some life of applications but concurrent users should be no more as 20 per day.

If this was single user application I would say you can still use any of the 3 databases.
However since this is multiuser database I think you may want to eliminate sqlite db.
The reason is that for multi user you will likely want to employ database views and/or user permissions.
Sqlite only has local file permission and I don't think it supports views.

Not that it cannot be done but you will have to build processes to manage some stuff that other database packages will do it for you.
It really comes down to design.


EDIT:
to make sure its clear you don't have to have database ID for every customer. That would be more of true statement if you have people registering to your store and making purchases.
However as opposite example if you building office application like accounting package and you selling this to different businesses you want consider database ID for every business.

 

[OliverA]

However since this is multiuser database I think you may want to eliminate sqlite db.

The kicker is, if you look at the source of most web applications that use SQL DB's as a back end, they completely ignore the underlying DB's security mechanism and view system. All the security checks and view building are done in code. In such a case, you may as well stick with SQLite, if that fits the bill. One of the counter-arguments that I have seen against SQLite is that it is very sparse on types. For that, there are a couple of other embedded databases such as H2, Derby or HSQLDB that my fit the bill without requiring a separate install of a DB system. H2 has a comparison table comparing itself with the other two embedded DB's mentioned and MySQL and PostgreSQL (https://www.h2database.com/html/main.html).

Update:
From H2's site, some benchmarks: https://www.h2database.com/html/performance.html

 

[tchart]

+1 for H2, Ive used it on several projects. It has several advantages over SQLite;

1. H2 has embedded modes as well as a server mode which means it can scale.
2. H2 has a Postgres compatability mode - which means if you ever get to a tipping point you can switch over to Postgres easily.
3. It is much stronger typed than SQLite and has many things youd only find in a large RDBMS.

However in saying that SQLite is my default and Ive had no problems with it as long as you are using a shared (global) SQL object in your project to avoid locking etc. I use it in several projects that write thousands of rows in 20+ tables.

Someone else asked this question regarding a POS system and ultimately went with SQLite (I believe). Using MySQL etc means extra components to install, manage etc for small customers its not worth it. For that user this would be an issue (ie having to be a DBA for those customers).

Lastly Ive seen bad performance for file system based databases on Azure app services but this is a known issue with disk access so not a fault of SQLite, H2 etc. So avoid using any file based datyabase on Azure app services.

Edit: Forgot to mention, MongoDB is a no-SQL database so its use case is different. Ive seen bad performance for this when a product I installed used Mongo as a backend (probably just because its cool and new). If you just need tables to store rows then Mongo isnt the right choice.

 

[tchart]

However since this is multiuser database I think you may want to eliminate sqlite db.

Thats not entirely a true statement. It doesnt support multiple connections. If your app only uses one conenction to access the database then mutliple users are defintely supported.

 

[tchart]

Here is that other thread, I suggest you read through this @ilan

Would you recommend SQLite?

Well, I intend to rewrite a small POS system for B4J. It will be used by small restaurants, so I don't expect a lot of competing readings and recordings, or a lot of data, and it will be used on a local network. My intention is to use SQLITE, to facilitate the installation, but I never used this...

www.b4x.com

 

[jerry07]

jerry07 said:
However since this is multiuser database I think you may want to eliminate sqlite db.

Thats not entirely a true statement. It doesnt support multiple connections. If your app only uses one conenction to access the database then mutliple users are defintely supported.

I wasn't even trying to suggest that each user should have own connection.
But since tchart didn't get that from my post I worry that it may not have been clear enough for ilan.

So to clarify, above quote wasn't a statement and more of a suggestion. Key word is "may want to eliminate Sqlite"
I also attempted to explain that you don't want DB ID for each user logging on to the store front but It may be recommended for accounting application.
To simplify this I would advise to have DB ID's based on roles but my experience is in data warehouses and not web applications.
My main concern using Sqlite is implementation of views as I think this is the easiest way to restrict user to see only his data. But I didn't say it cannot be done with SQLite.

I hope I made this a bit clearer.

 

[jerry07]

OliverA and tchart,
Thanks for introducing H2 I will have to have a look at this.

 

[tchart]

OliverA and tchart,
Thanks for introducing H2 I will have to have a look at this.

No problem. H2 is really good. Lots of options. Documentation is really good too.

Also if anyone is after a good SQL editor/UI then check out Dbeaver. Very good and automatically downloads jars files as required.

 

[Peter Simpson]

Also if anyone is after a good SQL editor/UI then check out Dbeaver. Very good and automatically downloads jars files as required.

Dbeaver is an excellent tool

Just my 2 cents...

My main SQL server editors are as follows

1. Dbeaver ???
2. HeidiSQL ???
3. Database .Net - (This connects to a lot of databases. I rarely use it, maybe twice a year) ?

My main SQLite editors are as follows

1. SQLite Expert Personal 5 - (For manually creating/editing databases) ???
2. SQLite Studio - (Mainly for importing CSV files directly into tables) ??
3. DB Browser (SQLCipher) - (For manually creating/editing encrypted databases, can also import CSV files directly into tables) ?? ?
4. SQLite Convert - (The name says it all) ??

Enjoy...