Create Database Model Interface

[00:00:00]
>> Maximiliano Firtman: Okay, so we need to get rid of the dummy data, okay? So it's time to go with real data. So we will start working with our database, okay? So our database is actually based in a SQL database. Of course, we could use a no SQL database as well, but for this project, I picked a SQL based structure.

[00:00:25]
So in that case, we have these entities and relationship between entities, so we have users that we haven't used yet. This case, this is accounts, so the users will have saved Movies. Movies have actors, that's the cast. And movies have genre and keywords. That's how it looks like.

[00:00:52]
We need to normalize this, because it's a SQL-like database. So in terms of tables that we will have, we will have the users table, the movies table, the genre tables, actors, user_movies, because the relationship between users and movies is one to n, okay? So one user, many movies.

[00:01:17]
I can bookmark, I can make a fabric, many movies. So it's one to n, so I need another table there. And of course, one movie can be added from many users, so it's actually end to end. So that's why we need a table in the middle. The same happens with actors, because the same actor can be in many movies, and one movie has many actors.

[00:01:44]
The same with keyboards and genres. So that's why we have user_movies, movie_cast, movie_genre. So those are intermediate tables with foreign keys, okay? Does it make sense? So that will be our table structure. We also have movie keywords, okay, that will be, it's just strings, but the idea is that, because it's many collection later, we can add another feature, for example, to find similar movies.

[00:02:17]
I mean, if you like the movie, you can find similar movies by verifying the same keyword. For example, again, you are watching a movie, a history movie, on World War II. Well, those are keyword, World War II, history, maybe boats, and then you find other movies with the same keywords.

[00:02:43]
In that case, that would be homework, so they prepare this, so then you will have by the end more work to do and more features to add to this project. It's time to actually create our database. So to do that, first we need a provider. So you need to pick, where are you going to save your database?

[00:03:02]
If you have a Postgres local server, you can use it. If not, you can use any of the providers that we have available. Remember, let's go back to the list of providers that I know they will work. Supabase or aiven.io or neon.tech. Any of those will work. For example, let's take aiven.io, for example.

[00:03:36]
So I go
>> Maximiliano Firtman: To their website, they have many solutions, but one specifically, it's for PostgreSQL. And there is a free plan available. You can always go to pricing and see if that plan will be good enough for you. There are many information here, okay, the features, and the free plan that fortunately has no credit card requirements, so you need to add, it's five gigabytes of a storage, which is a lot for our needs today.

[00:04:11]
So you click, Get started for free. You just need to log in. I think I'm already logged in. And you can just log in with your Google account or with your GitHub account and it creates an account in five minutes. Actually, five seconds. From here, you need to create a service.

[00:04:29]
And there are many services that they have, and you create the PostgresSQL server. So you pick that database that we want. And it says, do you want a full platform? Like a trial of 30 days for free of the paid account or free plan. So let's go with the free plan.

[00:04:48]
You can select the server, the continent, or the country where that server will say, remember, it's in the cloud. You can just pick wherever, and you set a name, this is the name of an instance. I can call it on a that's name of our project. And then you create the the service, okay?

[00:05:09]
And for me, it's not giving the option because I have only one for free. So that's why when I'm going back, I really have one here, okay, that is already running. So when you get into it, the most important part here is this service URI. So, this is the string that we'll need to copy and paste in our code.

[00:05:35]
So that will be the next step. Actually, it says, click to reveal password. So if you click here, okay, I'm gonna delete my instance anyway later. So it doesn't matter if you see my password, but actually, this is what we need to copy and paste in our code.

[00:05:51]
We're going to do that actually in two places. One is gonna be for the import or installation script that will actually populate the database. And then for our actually server, okay? But first, before actually trying to connect to the database, let's populate the database, okay? It doesn't matter if it's your local instance or if it's one of these providers.

[00:06:17]
Going back to our project, there is an import folder here that has a database dump file. There is actually a dump that I did from my instance. So here we have everything we need to create the tables and to insert the data in the tables, okay? So here we can see the structure of the table, for example, actors, we have an ID that they have a sequence, it's actually auto numeric.

[00:06:49]
It's creating an ID, an automatic ID, first name, last name, image, URL, and the primary key is ID. So, pretty straightforward. Then we have genre, it's ID and name and primary key, nothing so fancy. The same for keywords, movie cast, it's movie ID and actor ID. And it's compound primary key, both are the primary key.

[00:07:14]
So the same actor cannot be twice in the same movie. Movie genre, so nothing really important. And the largest table that we have is actually movies, with all the properties and the primary key, and actually that's all, okay? And, well, users, and then we have the data, okay?

[00:07:37]
We insert into blah, blah, blah value. So, how we put this Is into our database. If you have a tool to connect to your database, for example, I have one here, it's called TablePlus it's for Mac only, but there are for Windows as well. You can even use the same tools that we typically use for MySQL.

[00:08:02]
So you connect to a Postgres. You can use .ui, so it's a desktop UI to connect to your table, and then you can copy and paste the SQL or you can go to file import and you can import from SQL dump. Okay, that's one option. But maybe you don't have these tools or, this is important, maybe you wanna automatize the idea of installing this in a server.

[00:08:30]
So you wanna deploy this in a server, and as part of your initial script, you just wanna do it automatically. You don't wanna do it manually. So for that, ARA have for you an install.go script. There is actually, right now it's in the same project, but it's kind of a separate thing, okay?

[00:08:52]
It's a separate Go file, that will actually connect to your database, take that file, and will execute that file over your database. So it will create the tables, and it will populate all the data. Does it make sense? The only mandatory part is that we need to replace the connection string.

[00:09:12]
So if you're going to use
>> Maximiliano Firtman: Aiven, well, I can copy this, so I can copy this and then replace the connection string. I mean, if you look at the code, it's pretty straightforward. It opens the file, then just to tell you quickly what this is actually doing is removing the comments, so it's not executing the comments.

[00:09:40]
And also, it's splitting the string by semi colon, which means it's actually recognizing all the statements and then it's executing each statement one by one. So now if I open the terminal, I open a new terminal, the other one is my server, I get into the folder. So, change directory into import.

[00:10:08]
>> Maximiliano Firtman: Now, I don't need to use AR, just a plain script. Go, run, install.go.
>> Maximiliano Firtman: It will go there to the database and install everything. In my case, it's complaining because I already have the data in this particular server, okay? So if I wanna execute this, I can delete everything first.

[00:10:35]
So I can delete everything because it's complaining because of the ordering, why I'm in order in which I'm deleting the data, because I need to delete the data in cascade, okay? But if I truncate all the tables and drop all the tables, okay, it will work. Have in mind it will take some time, just 40 seconds, 1 minute, not more than that, okay, to send and execute all the table for you, and you will get something like this.

[00:11:08]
So you will get all your movies in a table with all the properties inside. There are other ways to do that as well, if you have the data in CSV format. PostgresSQL has a way to send or import from CSV. In this case, you typically use the Postgres terminal to do that.

[00:11:31]
It has to do more with specific of this particular database, but this script will work. And later, if you need to change that for other database, it will probably work. So if everything works, now we have data in the database. So we need to move on and of course, the next step will be, well, we need to go to our Go project and actually connect to the database.