How to Start Using PostgreSQL and Migrate From MongoDB

[00:00 - 00:10] For our SQL database, we'll choose Postgres. Postgres is a very powerful relational database and it has the highest rating for SQL compliance.

[00:11 - 00:27] Postgres is a fully open source project and is developed and maintained by a large and active community. Postgres has earned a strong reputation for its reliability and robust feature set, becoming de-relational database of choice for many developers.

[00:28 - 00:42] To install Postgres, head over to their official download page and select the operating system you're using. Next, click the "Download the installer" button and choose the latest version for your operating system.

[00:43 - 01:04] Once the download is complete, run the Postgres installer, make sure you select all the components for installation. Because I already have an existing version of Postgres installed on my computer , it will be updated to the version I just downloaded.

[01:05 - 01:20] Note, if you are installing Postgres for the first time, you'll be asked to pick a directory to store your data, create a password for a super user, and choose a port number for your Postgres server. For this, I recommend using the default 5432 port number.

[01:21 - 01:36] Once the installation completes, you should be able to find PG Admin on your computer. PG Admin is an application that can be used to create, modify, and delete your Postgres databases using either SQL or a graphical user interface.

[01:37 - 01:44] When you launch PG Admin, you'll be asked to enter a password. This is the same password you set up during the installation process.

[01:45 - 01:54] On the left navigation panel, you'll find your databases. To create a database, simply right-click on databases and select Create Database.

[01:55 - 02:03] Next, enter a name for your new database. For this example, I will enter TestDB001.

[02:04 - 02:14] Then hit Save. To create a table for your database, select schemas and right-click on tables.

[02:15 - 02:21] Then select Create Table. To create table dialog window, enter a name for your new table.

[02:22 - 02:30] For this example, I will enter Test users. Next, navigate to the Columns tab to start building a schema for your new table .

[02:31 - 02:39] Click the plus icon to add a new column. For the first column, specify ID as the name and select Text as the data type.

[02:40 - 02:48] Set the Not, No, and the primary key constraints to Yes. The Not, No constraint ensures the column cannot contain any No values.

[02:49 - 03:04] The primary key constraint ensures the column must contain unique values, which can be used to identify each row of data. For the second column, specify Name as the name and select Character Varying as the data type.

[03:05 - 03:10] And specify 100 for the length constraint. Set the Not, No constraint to Yes.

[03:11 - 03:23] The Text data type in Postgres is equivalent to string in TypeScript. The Character Varying data type is the same as Text, except we can specify a maximum number of characters.

[03:24 - 03:33] In our case, we picked 100 characters as the max. We'll explore more data types in the next few lessons as we convert our apps to use Postgres.

[03:34 - 03:49] But for now, if you want to take a look at the full list of Postgres data types , I encourage you to check out the official documentation. If we click on the Save button right now, we would have created a new table using the Graphical User Interface.

[03:50 - 04:02] However, if we navigate to the SQL tab, we can actually see the SQL statement for doing the exact same thing. If we copy the statement, click Cancel.

[04:03 - 04:21] Select Tools, Query Tools from the top navigation bar, and execute this statement, we can create a new table using SQL. Next, let's execute some CRUD operations using SQL statements.

[04:22 - 04:30] To insert data into a table, we can run the Insert statement. This is equivalent to MongoDB's Insert 1 function.

[04:31 - 04:39] To see all of the data in the table, we can run the Select statement. This is equivalent to MongoDB's Find function.

[04:40 - 04:49] To find a specific row of data, we can also run the Select statement with a Wear class. This is equivalent to MongoDB's Find function with a Query object.

[04:50 - 04:58] To update an existing row of data, we can run the Update statement. This is equivalent to MongoDB's Update 1 function.

[04:59 - 05:07] To delete an existing row of data, we can run the Delete statement. This is equivalent to MongoDB's Delete 1 function.

[05:08 - 05:23] We can modify our table using the Alter table statement with the Rename column and the Add column clauses. We can rename one of our existing columns into username and add a new column called the email.

[05:24 - 05:32] We just modified our database schema using SQL. Finally, let's test our new database schema by inserting a new user.

[05:33 - 05:45] There are a lot more SQL statements than the ones we just explored, and you can create an entire course on SQL alone. If you are interested, I encourage you to check out W3School's SQL tutorial.

[05:46 - 05:58] However, the ultimate goal for us is not to create SQL statements manually. What we want is to have our node server generate SQL statements based on user inputs from our React application.

[05:59 - 06:02] For that, an ORM is the perfect tool to use.