129 Which Database Type Should I Use For My App?

Which database type is right for my project? If my app only has one user, does that answer change? Which type is right if I distribute my application? These are the questions we will answer in today's episode of Dev Questions.

Website: https://www.DevForge.com/ 
Ask Your Question: https://suggestions.iamtimcorey.com/
Sign Up to Get More Great Developer Content in Your Inbox: https://signup.iamtimcorey.com/

Transcript

Copy Transcript Copied

SPEAKER_01 0:00

Welcome to the Dev Questions Podcast with Tim Corey. Join us each episode as we tackle the questions you are asking about a career in software development, understanding the industry, and new technology. If you're just starting out or you want to grow stronger as a developer, this is the place to get your questions answered. Now, here's your host, expert developer and online educator, Tim Corey.

SPEAKER_02 0:25

Which database type should I use for my application? This is a question I was asked recently on a suggestion site, and I thought it'd answer in today's episode of DevQuesti. Now, let's get the answer out of the way right away, and then we'll talk through. Okay. So the answer is it depends. Okay. So that's the answer to pretty much any question you ask, but let's talk about the criteria, think through what's the best database type you should use for your application. So the first question I want to ask is what's the purpose of a database? All right. So what is the purpose of any database that you have for any application? Well, it's there to store your data, right? It's there to hopefully give your data back to you quickly. All right. And then it's there to secure your data and it's there to protect your data. All right. So secure and protect, think of it this way. Secure is like no one can access your database who shouldn't. And the safety or security of your, I'm sorry, safety or protection of your database is like, is it backed up? So if something happens to corrupt it, is it protected against that? So is it, you know, your database is designed to protect your data as well as secure it. Is there to store your data and is there to quickly give you back your data? Those are the kind of the four big buckets that we're talking about when it comes to a database. All right. So here's an example for you. Um, the IM TimCore database site, or I'm sorry, the IMTMQRI site has a database, I guess. Uh we store data that is used to generate and populate the site. And that's kind of a database. But that database is a JSON file. It's not SQL or NoSQL or any of those others. The reason why is because it allows for the storage of all the data. You can store data in a JSON file. It's extremely fast to access. It's as secure as it needs to be. I mean, I don't care if you read my JSON file, go for it. Read it all. It doesn't matter because that's all public information. And then it's protected by source control so that if it ever got corrupted, I just republished the latest build and we're good to go. So when you think about your databases, don't say, oh, well, it must be SQL. It must be, you know, some big expensive system, some complicated system. A database could be as simple as a text file. Now, do I recommend text file storage for your database needs? Not usually. I think that usually you want to kind of grow beyond that and use systems that better secure your data because usually you do care if people read your data. And maybe you want to have a better backup system than just it's in source control. Okay. And the reason why my JSON works is because you don't write to it. When you go to my website, you don't create anything that ends up getting put in that JSON file. It's a read-only database. The only time we change that data is when we physically go in there and make a change to the file and then commit that to source control. So that's why it works for this situation, but not for every situation. In fact, not for most situations. But when you're thinking about databases, don't limit yourself. Don't start with, okay, a database must mean some variant of SQL. SQL, MySQL, SQLite, you know, down the list. No, no, no, stop. Back up. And again, think about the goals of your data. How is the database going to be used? What is it needed for? And so on. So this is where we need to talk about the size of a database. If you've got a large project, and by large, I mean you're expecting well over millions of records. Millions. In that case, you have less options. Okay. There's some things that just won't work. For instance, uh SQLite. That is will not work for millions of records. It just shouldn't work. It can, you don't want to do that. Okay. So you're kind of limited to a, you know, either SQL, a major SQL version, or a NoSQL version. Okay. And then it's just whichever flavor works best for you. So we'll come back to that, but it really comes down to the skill of a team working on it and the previous databases used and the costs associated. So if you're in an environment that already has 18 Microsoft SQL databases, and you say, what database should we choose for this project? You're probably going to lean towards Microsoft SQL database because you already have a lot of the infrastructure, you already have a lot of experience, you already have a lot of the systems in place to help you build those databases. And the cost associated will be pretty low. And if you said, well, actually, we're going to try and use Oracle for this one, that's probably not a good call because you got a whole new database system and a whole new set of commands and all this other stuff. You don't want to mix Oracle and Microsoft SQL. You don't need to. And you probably wouldn't go with a NoSQL database unless you had a good reason. You'd want to stick with what you already have. So there are times of change, but in a big situation, usually you end up relying on those criteria. Now, for a small database, and by small, I mean less than a million records. Okay? So we're talking about databases that really can handle even a medium-sized business. Because even a medium-sized business just doesn't have millions and millions of records, all right? Usually. So for a small database, there's lots of options. And they're going to appear based upon what your needs are. So let's talk through some questions you can ask. Does it need to be portable? Meaning, if you deploy an executable file to a client machine, you install it using MSI or something else, and it needs to work offline, you probably want a database next to that exe. Well, that's a portable database. You don't want to install Microsoft SQL Server, you know, SQL Express or something like that on their machine and have it all set up. That's a little complicated for just a portable database. So that's question number one. Question number two, is this a single user or a multi-user database? Meaning, if if I create a, let's say, a to-do list and maybe I even put it as a web app, but only I have access to it. It's just for me, I'm not going to publish it because, my goodness, there's a bajillion to-do apps in the market. So I just want for me, I want a couple of tweaks to it. That's a single user database. So that will be a different choice than a multi-user database. You could potentially even still use SQLite or goodness, Microsoft Access. It could work, even though you would not want to do that for a multi-user environment. All right. Number three, does it need to be secure? Again, coming back to my JSON example. My JSON did not need to be secure at all. Does yours need to be secure and how secure? So, what level of security you need to have as far as who can have access and what type of access and limitations. For instance, uh, Microsoft SQL Server, you can say your login only has access to these three store procedures. That's it. That's all you can do. Whereas this login over here can access all the store procedures, but no tables or views. And yet the store procedures can call those tables or views. Whereas this login can access tables, views, store procedures, all of them, but it cannot modify any structure of those tables, views, or store procedures. And this one over here can do backups and restores, but can't reaccess the database. So there's lots of different things you can do based upon the levels of security you want to set up inside a Microsoft SQL Server. You cannot do the same thing with a SQLite database. Okay? So there's different levels depending on a SQLite database. You can have a login in password that protects the database where you get in. That's, you know, that's kind of the levels you can have. So which one you choose is going to depend on what levels of security you need to have. Now, number four, does this database need to be replicated? This is one that until more recently, um SQL was not good at. Okay, SQL has had replication for decades. But uh I say Microsoft SQL, um, but it's not been easy. And it's not it's been complicated and expensive and all the rest. And then NoSQL comes along, and NoSQL like uh MongoDB or Cosmos DB, super simple to replicate. Okay, you create a database and replicate across the globe, and boom, you're done. So does it need to be replicated? And how much replication? Do you need to have uh read-write replicas, or do you want to have one database that's the read-write and a bunch of read-only replicas? That changes things too. So that's the fourth question to ask. Number five, how fast does it need to be? Now, when you're first starting off, everybody that is especially newer, but it's it's it's really a disease we all have. And that is, I want to be as fast as Google, I want to be as big as Google, I want to have it have as much throughput as Google. You're not Google. You're just not. So realistically, how fast does it need to be? If it needs to be lightning fast, okay, that changes what things you can choose. So here's some examples. Okay, so if you have a single-user portable database need, okay, one user needs to be portable database, SQLite. That's a good option. Now, you could also look at a CSV file if that's you don't have to have protection. And there's other options, but SQLite would probably be a good option. Okay. If you need a quick, cheap database that's replicated across the globe, MongoDB, maybe Cosmos DB, uh, those are very easy to replicate and they're very easy to start very, very small and very, very cheap. All right. If you need a small multi-user database that maybe you're already working with some SQL databases or have some knowledge of Microsoft SQL Server, Azure SQL, great place to start. Uh, $5 a month, I believe it is, for the tiniest dev database that is super small but still works as a full SQL Server. Five bucks a month. Okay. Maybe it's 10. I think it's five. Um, anyways, super small version for a small multi-user database. If you need extremely fast databases that's used for caching, Redis. Okay. You might want to look at Redis because that's what they're designed for is for caching. It's still a database, but it's designed for very, very quick lookups because that's what's most important is that super fast speed. All right. So different databases for different situations. Go through that checklist of those questions and say what kind of fits in best. Now, here's my recommendation. And in order to use that list of questions that I gave you, you need to follow this recommendation. And that is get to know the various database types. Because if you don't know what's out there, then how can you make a decision on one versus the other? And then even if you know what's out there, you need to know their strengths and weaknesses. Because every database is different in its strengths and weaknesses. All right. If you don't know those, how do you compare the two? There's a bajillion flavors of SQL. Like I said, MySQL, NoS or SQL Lite, Microsoft SQL, Oracle. These are all SQL type databases where you use a SQL command structure and it's a relational database and all the rest. And so why would you choose one or the other? You have to know its strengths and weaknesses. Now, put a big emphasis when you're learning these database types on understanding both the SQL structure and the NoSQL structure. Too often people say, you know, SQL relational database is the only way to go. Really? Because all your caching databases are all NoSQL databases. So why are you saying that? You don't want to cache your data in a Microsoft SQL Server. That's just not the place to do it. You want to cache it in a Reddit structure because that's a NoSQL structure. And in fact, there's not just one NoSQL type, there's multiple. Okay? You have a graph database and you have the key value dictionary type pairing. And you like there's so many different types of NoSQL databases. You should know them all and how to use each one effectively. Put emphasis on knowing both the SQL structure and the NoSQL structure. Don't throw out tools. Too often I hear people say, well, you know, this is the best tool, so therefore throw that one away. Don't do that. Put the tools in your toolbox. Know when they're used most effectively. Because doing that will allow you to make the best choice for your situation. At the very beginning, I gave you the answer to this question. And the answer was, it depends. And if you say this tool is best, throws others out, then you're saying, no, Tim, you're wrong. It doesn't depend. This is the tool. And that's just not correct. There's a reason we have multiple different types, because different database types fit different situations better. All right. So once you pick your database, once you say, I've I've narrowed down based upon my knowledge, and based upon the pros and cons and the strengths and weaknesses and the costs and all the rest, I've decided this is the database of choice. Great. Now, really get to know it. Okay? Really dive deep into that database. Don't just stop at the surface level. Too often I see people say, well, you know, here's my pet peeve. I use Entity Frameworks, so I don't need to know about SQL. That's just a bad idea. Because you need to know how to get the most out of your database, how to use it well, how to secure your data properly, how to properly back up your database so that it's protected. You're protecting that data, how to restore that database properly, how to manage who has access to it and make sure that it's locked down as much as possible, how to get the most performance out of it possible. I've seen people complain about databases being slow when they're using really, really bad queries. Well, write a better query. But in order to do that, you have to know how to write a better query. So go deep into understanding your database that you're using. Don't just stop at the surface level and say, I put data in, I get data out, we're good. Okay? Know it better than that. All right. So that's my answer to the question: which database should I choose? It depends, but there's the criteria for choosing and evaluating which database works best for you. A quick example here: I have a C sharp master course where I take you from knowing nothing to being a C sharp developer who can get a job as at least a junior level developer, if not higher. Well, in there I have a complete section on databases. And we don't just choose one database type. We go through SQL, Microsoft SQL, SQL Lite, or MySQL, MySQL. We go through using both Dapr and Entity Framework. Yeah, I know. We also use NoSQL like MongoDB and Cosmos DB. We also look at Link. We also look at a lot of different things to make sure you understand how to access data properly, how to be the best at accessing that data so that you understand I have 18 different tools. I have 18 different sizes of flathead screwdriver. Why? Because different screws have different thicknesses, and I need the right screwdriver for the job. Okay. Or I need to have the right whatever other tool for this job. So I go, I spend a lot of time in there because it's important to not just say SQL Server. It's it's the one. Microsoft SQL Server, it works great with C Sharp, and it does. So therefore, that's the solution to every problem. It's not. Okay? Make the right choice that works for you. Okay. Hope I answered your question. Thanks for listening. And as always, I am Tim Corey.

SPEAKER_00 17:56

Thank you for joining us for this episode of Dev Questions. Tim is committed to making it easier for you to become a developer. If you would like to help make more content like this possible, please like, subscribe, rate, and share Dev Questions. You can also send your questions to questions at IamTimCorey.com. Until next time, remember, you are too smart and your time too valuable to waste it making all the mistakes Tim did. When you're ready to learn to think and code like a professional developer, head over to IamtimCorey.com and enroll in a course.