Switching to Production Database

[00:00 - 00:10] Till now we were using SQLite and it was working perfectly fine. But when it comes to production level application, we need to set up a database which is built for handling large amount of data.

[00:11 - 00:24] Since we are using Entity Framework, we can use almost any relational database, such as MySQL or Postgres. We can't use non-relational databases such as MongoDB with Entity Framework.

[00:25 - 00:35] And we have built a replication with relational databases in mind, so we have nothing to worry about. We are going to use Postgres as our choice of production database.

[00:36 - 00:45] One of the main advantages of using Postgres is that we don't need a credit card to use this on Internet. Let's start by installing Postgres in our computer.

[00:46 - 00:59] For using Postgres or any other database, it's much easier to go with Docker rather than installing it separately. So go to docker.com/products/docker-desk-top.

[01:00 - 01:17] Download it as per your operating system. It's advisable to use Docker because running database with Docker will create it in a separate container, which makes it much easier to operate, because we are not running the database in our operating system, but in a virtual machine.

[01:18 - 01:28] Once you have installed Docker desktop, I already have it, so I will open my terminal. And let's type docker run.

[01:29 - 01:35] Now let's give it a name, let's call it dev. And now let's use the environment variables.

[01:36 - 01:43] So we need to put -e. Now we can write the Postgres username with Postgres_user.

[01:44 - 01:49] Let's keep it simple and I will use user. Another environment variable will be the password.

[01:50 - 01:57] So let's write Postgres_password. And again, let's keep it simple.

[01:58 - 02:02] Let's call it password. Now we need to mention the port.

[02:03 - 02:14] So we need port 542 in our computer and 542 in Docker. And now we need to download the latest copy of Postgres.

[02:15 - 02:23] So I will write Postgres colon latest. Now we can run enter.

[02:24 - 02:31] It will check our local machine for the Postgres image. If it doesn't find one, it will download it on our behalf.

[02:32 - 02:40] After successful loading, it will give us the image of the container. If you open docker desktop now, we can see our Postgres connection called dev.

[02:41 - 02:43] We can click on it. We can check the logs.

[02:44 - 02:50] We can see the credentials if we click on inspect. So we have the username to be user.

[02:51 - 02:55] Password being password. We can see the port on our computer.

[02:56 - 03:06] It's 542 and on the container it's 542 as well. Now let's configure our application to use Postgres instead of SQLite.

[03:07 - 03:16] And let's open Visual Studio Code. Here, let's close this and open app settings.development.json file.

[03:17 - 03:23] Now this is the default connection string for SQLite. We need to change it if we want to use Postgres.

[03:24 - 03:32] Now let me get rid of this and write server. This will be localhost.

[03:33 - 03:44] We need port which will be 542. We need the user ID which is user.

[03:45 - 03:54] We need the password which is password. And we need the database name.

[03:55 - 04:02] Let's call it store. This is our string for the Postgres SQL database.

[04:03 - 04:09] Now that we have set this up, let's install Postgres in our application. This will be installed using nougat gallery.

[04:10 - 04:20] So let's press command shift P on Mac and open nougat gallery. Let's search for NPG SQL.

[04:21 - 04:34] Which stands for Postgres and we need to install this one entity framework core .postgres SQL. And we can install the latest version inside the infrastructure project.

[04:35 - 04:41] Let's click on install. Now we can close this and open program.cs file to configure this.

[04:42 - 04:53] Now we need to replace this with Postgres. So let's write use NPG SQL.

[04:54 - 05:07] If we see the error, what we can do is we can open the terminal, close this. We can go back and enter dot net restore.

[05:08 - 05:19] And the error is now gone. And we can now delete the migrations folder because these migrations were made for SQL database and not for Postgres.

[05:20 - 05:37] So let's open infrastructure and delete the migrations folder. Before we create a new migration with Postgres, we need to change something in our code because Postgres has changed the way it handles the date.

[05:38 - 05:49] We can open our course entity. It doesn't work like that anymore.

[05:50 - 05:57] This problem will exist with Postgres database and not with any other one. The latest version of Postgres accepts date time with UTC.

[05:58 - 06:05] So we can simply change this from dot now to dot UTC now. And that's it.

[06:06 - 06:11] We can open our terminal once again. And now we can create a new migration.

[06:12 - 06:32] Let's call it dot net if migrations add Postgres added. And our starter project API and our project is infrastructure.

[06:33 - 06:39] This time let's also add the output folder. So we can write dash O and now we can provide the folder name.

[06:40 - 06:52] So let's keep it inside data folder and migrations. Now let's click enter and RP should be small and not capital.

[06:53 - 07:08] So let's change that now press enter. Now if you open our infrastructure project, we have data folder which has a migrations folder with all these migration files.

[07:09 - 07:26] Make sure that it says Postgres and not SQLite. We can now open the migrations file and here we can see the fresh migration and everywhere we see Postgres instead of SQLite.

[07:27 - 07:35] We can now install an extension which will help us visualize the data. So let's close this and open extensions.

[07:36 - 07:43] Now this one is called Postgres SQL. So let's search for Postgres SQL.

[07:44 - 07:48] This is the extension by Chris Kaufman. Please install this.

[07:49 - 07:55] I already have this installed. And after you install this, you will see the logo on left.

[07:56 - 08:01] This extension will let us make a connection with the database when it's running. So then we'll be able to see the data.

[08:02 - 08:09] Now we can go back to our API project and run.net. Watch.

[08:10 - 08:23] Run. This has seeded all the data and we don't see any errors as of now.

[08:24 - 08:30] So now what we can do is we can open the extension and we can click on plus. So now we can create a new connection.

[08:31 - 08:40] Let's call it localhost. This is the host name of the database and we need the user which is simply user .

[08:41 - 08:52] We need the password which is password. The port name is already mentioned and we can use a standard connection and we need to use store because that's what the name was.

[08:53 - 09:03] Now if you open it, we can see data. So let's click on run select top 1000 and we see our causes data.

[09:04 - 09:15] We can also see the users. So let's open ASP.net users and we see R2 users, student and instructor.

[09:16 - 09:23] We can also open the application and see if everything is working as expected. We see all our data like before.

[09:24 - 09:29] Let me click here and we see all our causes. If I try to log in.

[09:30 - 09:43] I'm able to do that as well. Our application is working as expected so far.

[09:44 - 09:47] Let's take it to the internet in the next lesson.