I have developed a web application with a backend in python for enterprise clients to manage their products.
For this I have about 10 text files which are extracted daily from their ERP to our servers and have over 1 Million rows and ca 10 columns each.
Goal is for the user to select a material and get quickly (<1s) all the info related to the specific material.
I have no previous knowledge of SQL or any database for that matter. I've never had a use case where things could not be solved in PC memory. But the feeling is that this amount of data has to be better organized, therefore the need for a database.
Now the use case:
The info for a specific material has to be searched in all 10 tables
There will be a maximum of ca. 10 users active concurrently
- Requests from front-end will come as JSON, response will be JSON as well
- The database should be opensource and python libraries should exist
- The database should be lightweight on resources, such that it doesn't need a very costly server (ideally below 1 GB of RAM)
- The database will NOT have to scale to 1000nds of concurrent users
- The database should be very easy to learn for a total noob in databases of any kind (no idea of schemas, queries etc.)
- The database should be among the 15 most used worldwide so in the future we can find developers which are familiar with it
Done research so far: I have done the usual 3 hours of web search (otherwise I would not be posting this). My conclusions so far:
Traditionally, most people working in databases use SQL databases. Popular ones are MySQL and PostgreSQL with PostgerSQL established as a standard in the Python community
There has been a trend towards NoSQL such as MongoDB or Redis. Redis is quick as in memory, MongoDB is easier to learn
In python there is already SQLite, but it is advised against running in production Mentioned here
So to summarize there are different types and instances for different applications. It largely also depends on use case you have and what you have done previously. I got extremely different advice on the web and would appreciate your recommendation for my specific use case. I fear going down a rabbit-hole of learning one complicated database only having to switch later. Also, I don't want something that is overkill for my case. I usually prefer light and simple (i.e. Flask) to heavy but complete (i.e. Django).