B4J Question Would you recommend SQLite?

[jroriz]

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 database on a daily basis.
Does anyone here have experience with SQLITE?
Can I trust this database in the medium / long term, when it starts to grow?
Is there anything I should be concerned about?

 

[Erel]

Looks like a good solution. The driver is indeed compatible with MySQL: https://mariadb.com/kb/en/about-mariadb-connector-j/

BTW, I also think that SQLite is a great engine and use it in many cases.

 

[Peter Simpson]

On the other hand, to avoid the Oracle license I used the connector from MariaDB to connect to MySql (because it was the client's one) and it worked.

I was just about to say that is also a solution you could go for @jroriz.

And below is how to use the MariaDB Connector/J to connect to an MySQL database using B4J

✅ JDBCSQL Connector/J - Connect directly to MySQL, MSSQL, PostgreSQL, MariaDB and Oracle databases

Hello fellow B4X'ers, Here is some example code for connecting your B4J projects to 5 different types of database engines, this tutorial is for learning purposes only and nothing else. Disclaimer: This code with some minor changes may also work in B4A. MySQL and MSSQL connectors definitely do...

www.b4x.com

But it's entirely up to you...

 

[jroriz]

I'm afraid that Erel's assessment is not fully correct. The problem is not the server per se, the problem are the connectors.

MySQL is either licensed under the GPL or under a commercial license that you must purchase. MariaDB is only licensed under the GPL. The devilish problem with those servers are the so-called connectors that your application needs to access the server: They are also licensed under the GPL, unless you purchase a commercial license. The GPL by definition is a viral license: Using these connectors to access the server from your application either requires your application to also be released under the GPL - read: It MUST be Open Sourced under the GPL - or you contact Oracle and purchase licenses so that you are allowed to use the connectors in a proprietary/closed source application.

Repeat: If you want to deploy your application as closed-source software that uses MySQL through the official connectors, you MUST buy the appropriate licenses from Oracle, for the connectors and for the database server.

Of course, you could use a proxy that accesses the MySQL server. But that's an ugly, unprofessional hack and only adds needless complexity to the setup just to avoid licensing issues.

SQLITE is not only free and open source, it is in the PUBLIC DOMAIN -- you can do with it whatever you like. There's a reason why even Apple uses SQLITE as an embedded database engine (they did so for example in their now discontinued professional photo management software APERTURE). SQLITE --IS-- battle tested. And it's commercially safe for you to use it (which is the whole idea why the author put it in the public domain).

If you want to use a risk-free Open Source database server that can easily compete with MySQL or Mariadb on --ALL-- levels and that does not have ANY licensing risks, then use PostgreSQL. It has a steeper learning curve than MySQL, though. But it comes with a business-friendly BSD license.

I've been using MySQL for many years, for a local network application written in .NET.
I will install PostgreSQL.
I think it will be perfect for the POS.

I didn't think this innocent post was going to get this far.
But it was great: I didn't know the details of the MySQL license and I didn't know that PostgreeSQL could advantageously replace MySQL.
And I'm sure this post can help other users in the B4X community.

 

[KMatle]

SQLite is absolutely ok for small systems (up to 100-200 users, maybe more). Biggest advantage: It's just one file (when it is closed). So backups are easy.

 

[jroriz]

SQLite is absolutely ok for small systems (up to 100-200 users, maybe more). Biggest advantage: It's just one file (when it is closed). So backups are easy.

Then you would still recommend SQLite for this project (small POS, running on a local network, for a maximum of 20 users)?
Do you have experience with SQLite in a similar scenario?

 

[KMatle]

Then you would still recommend SQLite for this project (small POS, running on a local network, for a maximum of 20 users)?
Do you have experience with SQLite in a similar scenario?

B4x plus SQLite is more than good for it. Use AsyncStreams for the network connection. Of course I have other projects with MySQL. Here it is a solution when users need access via www (via OkHttpUtils and php) or webpages I have developed. Even here SQLite would be good if there are not too many users or bigger data.

 

[Diceman]

Then you would still recommend SQLite for this project (small POS, running on a local network, for a maximum of 20 users)?
Do you have experience with SQLite in a similar scenario?

Sqlite is a network database and Postgresql is a client server database. A C/S db has several advantages. You can use the c/s triggers and stored procedures to offload code from the client to the server so it is more centralized. This increases db integrity and security with user roles. I believe Postgresql can use Python for stored procedures. This also allows you to use other applications other than B4X to access the db. A c/s db uses less network traffic when retrieving records. Customers will pay more for a c/s db because it can more easily handle the load should the company expands. If you don't plan to sell more than a few dozen PoS apps, the SQLite will do well. But if you want to compete with the big boys, you should consider using a c/s db. There is a learning curve curve but you can take this knowledge and build more robust apps later with it.

 

[jroriz]

Sqlite is a network database and Postgresql is a client server database. A C/S db has several advantages. You can use the c/s triggers and stored procedures to offload code from the client to the server so it is more centralized. This increases db integrity and security with user roles. I believe Postgresql can use Python for stored procedures. This also allows you to use other applications other than B4X to access the db. A c/s db uses less network traffic when retrieving records. Customers will pay more for a c/s db because it can more easily handle the load should the company expands. If you don't plan to sell more than a few dozen PoS apps, the SQLite will do well. But if you want to compete with the big boys, you should consider using a c/s db. There is a learning curve curve but you can take this knowledge and build more robust apps later with it.

Well, I hope that in the future I need to worry about the number of customers ...
For now, I think I'll stay with SQLite, since even in the medium term it will do the job well.
But I will definitely take a good look at PostgreSQL, I already installed it and it was very smooth.

 

[Jack Cole]

I use SQLite on mobile devices with something like 20M installs over time. It's easy to work with and DbUtils makes it even easier. I'm certain it's much lighter / quicker to install on a client device.

I have never used it on the backend / server side. As many others have noted for backend, MySql or SQL server are preferred. I don't interface directly to those backend servers, but utilize PHP. Many like to use b4j instead of PHP. SQL server running on Microsoft Azure is quite nice if you have a subscription.

 

[jroriz]

I use SQLite on mobile devices with something like 20M installs over time. It's easy to work with and DbUtils makes it even easier. I'm certain it's much lighter / quicker to install on a client device.

I have never used it on the backend / server side. As many others have noted for backend, MySql or SQL server are preferred. I don't interface directly to those backend servers, but utilize PHP. Many like to use b4j instead of PHP. SQL server running on Microsoft Azure is quite nice if you have a subscription.

The intention is to develop a desktop application on a local network, not a client/server over the internet.

 

[Diceman]

You might want to consider using jRDC2 with Sqlite. This will act as middleware so you can centralize a lot of code on the server, instead of in the client apps. It runs well on a network with Sqlite (or PostgreSQL). It will also increase reliability. Then as your customer base grows, you can offer remote access to salespeople on the road using their iPhone or Android device.

One more thing. Can I assume you are using Android tablets for the PoS terminals? Their touch screens make it more intuitive than using a PC running B4J. The jRDC2 server would be running on a PC (Windows or Linux) and the PoS terminal is a standard Android tablet or phone that is portable and inexpensive. This will keep the costs down compared to using PC's as PoS terminals and will take up less room.

 

[jroriz]

You might want to consider using jRDC2 with Sqlite. This will act as middleware so you can centralize a lot of code on the server, instead of in the client apps. It runs well on a network with Sqlite (or PostgreSQL). It will also increase reliability. Then as your customer base grows, you can offer remote access to salespeople on the road using their iPhone or Android device.

One more thing. Can I assume you are using Android tablets for the PoS terminals? Their touch screens make it more intuitive than using a PC running B4J. The jRDC2 server would be running on a PC (Windows or Linux) and the PoS terminal is a standard Android tablet or phone that is portable and inexpensive. This will keep the costs down compared to using PC's as PoS terminals and will take up less room.

PC for server.
I will often have 2 or 3 PCs using the system (cashier).
At first, only the waiters will use the system on tablets or cell phones.
In the future, perhaps customers will be able to order as well, from cellphone.

I found these posts interesting, and they discouraged the use of SQLite as a server in this scenario.
One of them is from the SQLite team itself:

Appropriate Uses For SQLite: https://www.sqlite.org/whentouse.html

SQLite is Not a Server: https://dev.to/lefebvre/sqlite-is-not-a-server-56il

 

[MichalK73]

SQLite is Not a Server: https://dev.to/lefebvre/sqlite-is-not-a-server-56il

The author writes exactly what I mentioned to you. That if there will be an intermediary between the SQLite database and clients (WebApplication.HandleURL) then you can easily use SQLite because only the intermediary connects to the database. In short, using jRDC2 as an intermediary between the database and your client applications will be very reasonable and efficient.

 

[Diceman]

PC for server.
I will often have 2 or 3 PCs using the system (cashier).
At first, only the waiters will use the system on tablets or cell phones.
In the future, perhaps customers will be able to order as well, from cellphone.

I found these posts interesting, and they discouraged the use of SQLite as a server in this scenario.
One of them is from the SQLite team itself:

Appropriate Uses For SQLite: https://www.sqlite.org/whentouse.html

SQLite is Not a Server: https://dev.to/lefebvre/sqlite-is-not-a-server-56il

I agree with MichalK73. If you put jRDC2 on the server (PC) along with SQLite, and have the PoS terminals communicate with the jRDC2 server (PC), it should work fine. There is no direct communications connection between the PoS and the server which makes it far more efficient and reliable (faster locking mechanism). The db requests will be completed in ms and you won't lock other users out of the database. You will be able to put your business logic on the jRDC2 server (PC) so it will be very efficient. If you ever want to swap out SQLite for PostgreSQL on the server, it is easy to do and you won't have to modify your software on the PoS terminals at all.

 

[jerry07]

Hi Joao,
This is my opinion based on 20+ years of working with Databases including small applications using MS Access and big data warehouses (TD, DB2, MySQL).

1. Are you familiar with one of the databases all ready? Then use that.
You will be more efficient with what you know and if you run into any intricates most of the time you will be able to work around these issues quicker.
2. Think about proper design.
Just about any database will behave poorly if you have bad design.

For example MS Access is one of the most dislike database by professionals but I actually think it serves it purpose and my most successful application was build using MS access in late 90's and its being used today. I still have people in this industry finding me by word of mouth and asking to sell them app.

The key is good design. If I was building POS system with mobile client (android, ios) I would likely use SQLite.
Try to minimize data exchange between client and server to minimum, build your complete order on the client maybe using SQLite table to store this order. After customer finishes ordering you make call to main DB to derive Primary KEY and then make another call to submit your order to the main DB.
I did test with Access where I had few PC's acting as client spiting out multiple transactions per second and updating main database with no issues.
This was done using RDO Recordset and my VBA coding if I was relying on MS Access Wizard this would fail.

In your case since you have very low data needs only few clients, small amount of transactions and almost no data fetching SQLite should be able to handel this very easily with good design. I know Access will and to my understanding SQLite is as good or better than Access. MySQL may let you get away little longer with bad design but by the time you find out you have an issue it could be so late that you wish you knew about it earlier.

In summary.
Any database should work for you, your data needs are very small.
Pick one and just try to be good at it.
If you don't have much experience with any database you may want to choose what you will use in the future projects.
This is very subjective opinion but I think SQLite may have smaller learning curve.

Good Luck with your project and let us know how its going.
Thank you.
Jerry.

 

[jroriz]

Hi Joao,
This is my opinion based on 20+ years of working with Databases including small applications using MS Access and big data warehouses (TD, DB2, MySQL).

1. Are you familiar with one of the databases all ready? Then use that.
You will be more efficient with what you know and if you run into any intricates most of the time you will be able to work around these issues quicker.
2. Think about proper design.
Just about any database will behave poorly if you have bad design.

For example MS Access is one of the most dislike database by professionals but I actually think it serves it purpose and my most successful application was build using MS access in late 90's and its being used today. I still have people in this industry finding me by word of mouth and asking to sell them app.

The key is good design. If I was building POS system with mobile client (android, ios) I would likely use SQLite.
Try to minimize data exchange between client and server to minimum, build your complete order on the client maybe using SQLite table to store this order. After customer finishes ordering you make call to main DB to derive Primary KEY and then make another call to submit your order to the main DB.
I did test with Access where I had few PC's acting as client spiting out multiple transactions per second and updating main database with no issues.
This was done using RDO Recordset and my VBA coding if I was relying on MS Access Wizard this would fail.

In your case since you have very low data needs only few clients, small amount of transactions and almost no data fetching SQLite should be able to handel this very easily with good design. I know Access will and to my understanding SQLite is as good or better than Access. MySQL may let you get away little longer with bad design but by the time you find out you have an issue it could be so late that you wish you knew about it earlier.

In summary.
Any database should work for you, your data needs are very small.
Pick one and just try to be good at it.
If you don't have much experience with any database you may want to choose what you will use in the future projects.
This is very subjective opinion but I think SQLite may have smaller learning curve.

Good Luck with your project and let us know how its going.
Thank you.
Jerry.

Thanks for the complete and accurate answer!
I already have many years of experience with MySQL and MS Access.
However with SQLite did just a few small projects (desktop). That is why I am not sure to use it for the project mentioned, especially after these posts:

Appropriate Uses For SQLite: https://www.sqlite.org/whentouse.html
SQLite is Not a Server: https://dev.to/lefebvre/sqlite-is-not-a-server-56il

But I will use SQLite for this project. If I have any problems in the future, I think I will have no trouble changing the database.

 

[Harris]

"For device-local storage".... Of course, best method for the mobile device.

Otherwise (IMHO), → choose client/server

All above comments are valid. This community taught me how to do all this many years ago - when I knew NOTHING - after taking the finer points from all suggestions.
I love to see that a simple question turned into a complete examination - with competing points of view - of how one should proceed.
Anyone, with unlimited time, can explore the methods outlined. I have found my path and I am stickin to it until it's "time to grow".

Checklist For Choosing The Right Database Engine
Is the data separated from the application by a network? → choose client/server

Relational database engines act as bandwidth-reducing data filters. So it is best to keep the database engine and the data on the same physical device so that the high-bandwidth engine-to-disk link does not have to traverse the network, only the lower-bandwidth application-to-engine link.

But SQLite is built into the application. So if the data is on a separate device from the application, it is required that the higher bandwidth engine-to-disk link be across the network. This works, but it is suboptimal. Hence, it is usually better to select a client/server database engine when the data is on a separate device from the application.

Nota Bene: In this rule, "application" means the code that issues SQL statements. If the "application" is an application server and if the content resides on the same physical machine as the application server, then SQLite might still be appropriate even though the end user is another network hop away.

Many concurrent writers? → choose client/server

If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine.

SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency that many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will.

Big data? → choose client/server

If your data will grow to a size that you are uncomfortable or unable to fit into a single disk file, then you should select a solution other than SQLite. SQLite supports databases up to 140 terabytes in size, assuming you can find a disk drive and filesystem that will support 140-terabyte files. Even so, when the size of the content looks like it might creep into the terabyte range, it would be good to consider a centralized client/server database.

Otherwise → choose SQLite!

For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a better solution. SQLite is fast and reliable and it requires no configuration or maintenance. It keeps thing simple. SQLite "just works".

 

[Diceman]

From the link "SQLite is not a server":
"If you want to stick with SQLite then you’ll need to put something in front of it that can handle requests from multiple client apps. The most obvious solution is to create a web service by using a web app with WebApplication.HandleURL (or HandleSpecialURL). The web app can accept requests from multiple client apps (or any type — desktop, web, mobile, etc.), fetch the data requested from the SQLite database and then send it back as JSON. This works because the web app is the only app that is connected to the SQLite database. "

This is why I recommended you implement jRDC2 (B4J web service) running on a PC that your B4J (or B4A/B4i) apps will communicate with. Your B4J jRDC2 app is the web service (running on your network) that will handle the requests from the PoS terminals (clients). The jRDC2 server is the only process that has access to your SQLite database and will make your database as rock solid as a client server database, perhaps even more so. Instead of creating triggers and stored procedures on a client/server database, you will write the code in B4J on the jRDC2 server. If you are looking for a fast, secure and reliable solution, then this is it.

 

[MichalK73]

From the link "SQLite is not a server":
"If you want to stick with SQLite then you’ll need to put something in front of it that can handle requests from multiple client apps. The most obvious solution is to create a web service by using a web app with WebApplication.HandleURL (or HandleSpecialURL). The web app can accept requests from multiple client apps (or any type — desktop, web, mobile, etc.), fetch the data requested from the SQLite database and then send it back as JSON. This works because the web app is the only app that is connected to the SQLite database. "

This is why I recommended you implement jRDC2 (B4J web service) running on a PC that your B4J (or B4A/B4i) apps will communicate with. Your B4J jRDC2 app is the web service (running on your network) that will handle the requests from the PoS terminals (clients). The jRDC2 server is the only process that has access to your SQLite database and will make your database as rock solid as a client server database, perhaps even more so. Instead of creating triggers and stored procedures on a client/server database, you will write the code in B4J on the jRDC2 server. If you are looking for a fast, secure and reliable solution, then this is it.

I already proposed it

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

 

[Peter Simpson]

Hello @MichalK73
Today I needed some information about MariaDB solutions (nothing to do with connector J), but after getting the answer to my original questions, I then decided to give Juan Sanz from the MariaDB support team a scenario via one of those annoying chat windows through the browser.

The conclusion is that @Erel is 100% correct and you can in fact bundle MariaDB Connector J with any software created by yourself for clients even is you are financially benefiting from it.

I gave Juan Sanz the scenario and here is his answer to me via the chat window.

Spoiler: Juan Sanz from MariaDB support chatting to me via a support chat window - Last part of out chat...

Juan Sanz 09:51
Do you know our Knowledge Base?

Peter 09:51
Your knowledge is confusing, I just need a simple answer to the simple question above? Thank you...

Juan Sanz 09:51
Ok give me a second please

Peter 09:52
ok

Juan Sanz 09:53
Our connector is GPL

so you can use it freely

even if it is a bespoke solution you are benefiting commercially for

it's yours to enjoy and exploit

just like the rest of MariaDB Comunity

Peter 09:54
Oh okay, thank you very much. You have a nice day, and keep up the great work...
Read

Juan Sanz 09:55
you're very welcome, Peter

you too, have a nice one

Juan Sanz has closed the chat.

So you can indeed bundle MariaDB Connector J with your bespoke solutions for clients and there will be absolutely no comeback from doing that, and that is exactly what I've been doing for the last couple of years anyway with my closed source applications.


Enjoy...