DEV Community: DennisVN

PostgreSQL terminal: a CRUD beginner guide (Windows 10).

DennisVN — Sat, 27 Nov 2021 16:34:43 +0000

For all my beginners out there, i began using PostgreSQL this morning. This article will help you with initiating and editing in the SQL shell. This article will not cover installation, and won't cover the GUI. This means that i expect you to have completed installation, and allready set a password throught the GUI.
During my webdev bootcamp we learned working with MySQL and MAMP, but i find them very different, so i hope this article helps you to save some time at researching. Let's get started !

Getting inside SQL through the terminal

i still find working through terminal quite tedious as i always get classic errors like "wrong host", "wrong password", "access denied" and all them classic shenanigans. It's pretty straightforward in this case :

Start > PostgreSQL 14 > SQL shell (psql)

the terminal opens and this is the one line you see :

Server [localhost]:

And here is where my confusion started, but basically, all you have to do is leave it blank & press enter. Same for the following fields that pop up :

Server [localhost]: (leave empty, press the ENTER key)
Database [postgres]: (leave empty,press the ENTER key)
Port [5432]: (leave empty,press the ENTER key)
Username [postgres]: (leave empty,press the ENTER key)

Now hold on, because the next one is PASSWORD. Here you have to insert the password that you set with the installation. Once you passed that, you are in ! well done.

Let's go over the commands to get you started. Here's your first one :

help
this will give you :

\copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

--> press q to quit list 


\l to see an overview of all databases 
(offcourse it is empty for now)

So let us create a new database called 'test' :

The command for this is :

CREATE DATABASE test;

Please not the importance of putting the semicolon at the end. Later on we will see that the terminal only executes after a semicolon. If you press enter without ending with a semicolon, the terminal expects you to continue typing.
Let's check if worked with :

\l
to check our list again, you will see it has been added !

Congratulations, we just opened the terminal, did our first basic commands, and we allready created a brand new list without touching the GUI.

But how to check out a specific database ?

close your terminal, and let's start from scratch.

When you open the Shell again (pro-tip: put a shortcut on your desktop to save time) and repeat previous steps. The only difference here will be that we going to tell postgres that we want to go to our newly created 'test' database. So don't go to fast, database is the second prompt you'll get ;).

Server [localhost]:
Database [postgres]:test 
Port [5432]:
Username [postgres]:
Password for user postgres:(enter the one you created).

you will get a warning message, just ignore it.
Next up use the following command to see what's in our 'test' database :
\d
If you are good at guessing, you can allready tell it's going to be empy. If not, this is what will appear in the terminal :

Did not find any relations.

And this is where the fun begins !

Manipulating our database through the terminal

In this part we go over basic CRUD commands to add, read, update and delete the information in our 'test' database.

ADD & READ NEW TABLE :
In our 'test' db, we will create a table called person. That person wil have following properties :

id ( number (int), a primary key, autoincrementing.)
name ( letters (characters), 100 max )
country ( letters (characters), 50 max )

We will have to define the type of data the properties get. Our id wil be an integer for example, but with an autoincrement function. This so our id will be automaticaly sequenced by postgres. Let me show you what i mean. This is how we do it :

CREATE TABLE person (
id BIGSERIAL NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
country VARCHAR(50) NOT NULL );

! Note how there is no semicolon until the fourth line.
We can press ENTER after
CREATE TABLE person (
and start the new line. Once you're finished, put a semicolon ; and press enter, only then terminal will execute those commands.
The terminal then confirms your creation and tells you:

CREATE TABLE

Now check again to see what's in our 'test' with :
\d
and terminal will show you :

test=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner
--------+---------------+----------+----------
 public | person        | table    | postgres
 public | person_id_seq | sequence | postgres
(2 rows)

Here you'll notice how the second row has

public | person_id_seq | sequence | postgres

This is the built-in sequencer from postgres, that'll make sure to check and auto increment our id property 24/7. Pretty awesome right ?

We have just checked our database, now let's check out our table with the command:
\dt
Then terminal will show you :

test=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | person | table | postgres
(1 row)

ADD NEW INFORMATION IN OUR TABLES :
Now that we have created our new person table, let's populate it. AKA let's INSERT the VALUEs for a new person. You can do this with :
INSERT INTO person (name, country) VALUES ('Jean-Baptiste', 'BEL');

press ENTER and you'll see :

test=# INSERT INTO person (name, country) VALUES ('Jean-Baptiste', 'BEL');
INSERT 0 1

let's add two more :
INSERT INTO person (name, country) VALUES ('Jev', 'US');
INSERT INTO person (name, country) VALUES ('Rudy', 'UK');

Then it should look like this :

test=# INSERT INTO person (name, country) VALUES ('Jev', 'US');
INSERT 0 1
test=# INSERT INTO person (name, country) VALUES ('Rudy', 'UK');
INSERT 0 1

A way to see everything from person is :
SELECT * FROM person;

* means EVERYTHING / ALL

test=# SELECT * FROM person;
 id |      name       | country
----+-----------------+---------
  1 | Jean-Baptiste   | BEL
  2 | jev             | US
  3 | Rudy            | UK
(3 rows)

extra: let's say you want the count of all your persons :
SELECT COUNT(id) FROM person;

test=# SELECT COUNT(id) FROM person;
 count
-------
     3
(1 row)

Well done, we've just added three people in our person database ! We were also able to read it, and to count how many people are in our database. If this were thousands of people, this was very welcome.

UPDATE THE INFORMATION IN OUR TABLES :
What if we have to change a name that we have in the database ? No need to delete and re-enter, you can use the following command :
UPDATE person SET name = 'Tapis-Plain' WHERE id = 1;

for my OG readers, my French is still lame ... We select here based on ID with the id of '1', in our example this is Jean-Baptiste. Let's check again. The console will return

UPDATE 1

Now let's chek again with
SELECT * FROM person;

test=# SELECT * FROM person;
 id |      name       | country
----+-----------------+---------
  2 | jev             | US
  3 | Rudy            | UK
  1 | Tapis-Plain     | BEL
(3 rows)

Very cool and good but ... 'Tapis-Plain' is a fabric and not a person's name. That's just silly ! So let's delete it, OK ? Last step incoming, you're almost there !

DELETE INFORMATION IN OUR TABLES :
Pretty straightforward. With the previous knowledge, we apply it to the DELETE command and you're good to go :
DELETE FROM person WHERE id = 1;
SELECT * FROM person;
The result :

test=# SELECT * FROM person;
 id |      name       | country
----+-----------------+---------
  2 | jev             | US
  3 | Rudy            | UK
(2 rows)

YOU DID IT ! Create, Read, Update and Delete (aka CRUD) in the Postgres SQL terminal.
Well done ! You just applied all needed techniques for a real world crud database. Before we end this rather long read, i'll leave you with a last command, handy when you want a more detailed overview of your database. This is called the expanded view :
\x
Terminal will show you :

test=# \x
Expanded display is on.
test=# SELECT * FROM person;
-[ RECORD 1 ]------------
id      | 2
name    | Beaucoupdargent
country | BEL
-[ RECORD 2 ]------------
id      | 3
name    | Magere
country | BEL

To leave this view press
q or \x once more.

Thanks for peepin', i sincerely hope this was useful for my beginners out there. See you in the next article !

React TSX: passing props with useContext (beginner guide) Part 1/2

DennisVN — Wed, 10 Nov 2021 15:53:45 +0000

I'm 7 months into coding now, simply love React so I dedicated to write my tech article about it. You can consider a 'classic' way of passing data, is passing data through a JSON list or Database. This often causes having to re-deconstruct the State in each new child component. To avoid this you can use the useContext hook in React, and pass it's props through a ContextProvider very easily.

This article will show you how to pass data from one central useContext that provides it's props to all components.
To go even further, we will edit the data and pass it to our screen and localStorage in part 2 of this beginner guide !
And yes, i mean ALL children. Let's start with context for now !

Action plan

In this example we are creating a restaurant's menu that displays a daily 3 course meal for Monday-Saturday. Before i spill the tea, let's start with the structure and action plan.

Please note that i expect you to have a basic understanding in React x TSX, and how components & React-Router-Dom works.
This however easily translates in JS for those who are not familiar in Typescript !

Let's get started !

In this project our initial Routing is structured as followed :

function App () {
<>
<div className="App>
    <Router>
    <Navbar>
    <Switch>
        <Route path="/menus" component={Menus}></Route>
    <Switch>
    <Navbar>
    <Router>
    <Footer>
</div>
</>
}

Note that there are other components in the OG project, but these are irrelevant in our case.

Important before you proceed is making an action plan, by dedicating the correct Parent & Children. For our menu this will be as followed :

Component tree in App.tsx :

<Menus> = Parent Component 
-(redirected from App with Route to component={Menus}.)
    <MenuCard> = Child component 
-(This will be the "card" for the COMPLETE week. 
        <AddForm> = Child Component 
-( We need this later in a modal to edit the data )

Where is your useContext i hear you think ? this is a global component so it has it's own place.

We will need a Provider to "inject" other components with it's props.
This means once you pass the props in a component, this comp will be "Provided" with the needed data !
Here we call it MenuContextProvider because obviously we are working on a Menu.

Step 1 : Setting up our useContext

First off, we obviously need to import & declare the context hook from React in this component :
-import { createContext } from "react";

Step 2 : Declaring data & objects inside context

To know how to pass the props we need to create our data and destructure it first.
We only need to do this once, here in this component.
Create a new State, which we will use as an Array of Objects.
Let's call this menus in our example ! This is how it looks :


const [menus, setMenus] = useState([
        { id: uuidv4(), day:"Monday", nameStarter: "Pôtage du jour", priceStarter: 4.20, nameMain: "Pomme d'amour", priceMain: 6.90 , nameDessert: "Salade du fruits", priceDessert: 4.20, totalPrice: 13.50, },
        { id: uuidv4(), day: "Tuesday", nameStarter: "Croquette crevette", priceStarter: 4.20 ,nameMain: "Moules Frites", priceMain: 6.90 , nameDessert: "Mousse au chocolat", priceDessert: 4.20, totalPrice: 13.50, },
        { id: uuidv4(), day: "Wednesday", nameStarter: "Croquette fromage", priceStarter: 4.20 , nameMain: "Pain Saucisse", priceMain: 6.90 , nameDessert: "Mousse au fraise", priceDessert: 4.20, totalPrice: 13.50, }, 
        { id: uuidv4(), day: "Thursday", nameStarter: "Croquette duo", priceStarter: 4.20 , nameMain: "Pain Fromage", priceMain: 6.90 , nameDessert: "Mousse au Camembert", priceDessert: 4.20, totalPrice: 13.50, },
        { id: uuidv4(), day: "Friday", nameStarter: "Cocktail of crevettes", priceStarter: 4.20 , nameMain: "Panette poison", priceMain: 6.90 , nameDessert: "Mousse au Brie", priceDessert: 4.20, totalPrice: 13.50, },
        { id: uuidv4(), day: "Saturday", nameStarter: "Carpaccio", priceStarter: 4.20 , nameMain: "Pain Jambon", priceMain: 6.90 , nameDessert: "Tiramisu", priceDessert: 4.20, totalPrice: 13.50, }
    ])

There are some take aways here :

my French is lame
for id I used uuidv4(). You can go for a class with name Id, but often you will get an error as unique ID's are required for each item. uuidv4() creates random strings as an ID, which makes it easy to imply those unique ID's ! Don't forget to import this on top of our context component !

import { v4 as uuidv4 } from 'uuid';

Step 3 : Setting up MenuContextProvider

We already know why we need a provider.
But how does it look, and how are we going to use and pass it ?

First off we need to create a new context. React does this with createContext. Again make sure you imported useContext on top of the component.

Since we are using typescript , we need to declare some extra types.

It took me a while to figure this out, but this is how it looks:

export const MenuContext = createContext< any | undefined>(undefined);

- const MenuContextProvider = (props) => {
    return(
        <MenuContext.Provider>
            {props.children}
        </MenuContext.Provider>
    )
}
export {MenuContextProvider}

Note how we export the MenuContext as an Object aka {MenuContext} ! Important that we import this likewise in the other components.

Note how is different from !

< MenuContext.Provider > is only needed in our routing which needs the .Provider to send the props.
< MenuContextProvider > works as the function component and will be imported in other components to process the State.

Step 4 : Context & React-Router-DOM

This step will show you how to inject your routing and links with the props, and Provide the pages with the data.
In fact this is very easy; We place the .Provider around the Router. This means all components become a Child from the .Provider.
Crazy right ? This is how it looks in our App.tsx;

import { BrowserRouter as Router, Route, Switch } from 'react-router-dom';
import Navbar from '../src/components/Navbar';
import Footer from './components/Footer';
import Menus from './components/Menus';
import {MenuContextProvider} from './contexts/MenuContext';

    function App () {
    <MenuContextProvider>
    <div className="App>
        <Router>
            <Navbar>
                <Switch>
                    <Route path="/menus" component={Menus}></Route>
                <Switch>
            <Navbar>
        <Router>
        <Footer>
    </div>
    </MenuContextProvider>
    }
    export default App;

Don't forget to import the Provider on top of the component, and as mentioned, as an Object !
Well done, you injected the {props.children} of the context in all Child components !

Let sip our drink, and overview our MenuContextProvider component:

import { createContext, useEffect, useState } from "react";
import { v4 as uuidv4 } from 'uuid';

export const MenuContext = createContext< any | undefined>(undefined);

const MenuContextProvider = (props) => {

    const [menus, setMenus] = useState([
        { id: uuidv4(), day:"Monday", nameStarter: "Pôtage du jour", priceStarter: 4.20, nameMain: "Pomme d'amour", priceMain: 6.90 , nameDessert: "Salade du fruits", priceDessert: 4.20, totalPrice: 13.50, },
        { id: uuidv4(), day: "Tuesday", nameStarter: "Croquette crevette", priceStarter: 4.20 ,nameMain: "Moules Frites", priceMain: 6.90 , nameDessert: "Mousse au chocolat", priceDessert: 4.20, totalPrice: 13.50, },
        { id: uuidv4(), day: "Wednesday", nameStarter: "Croquette fromage", priceStarter: 4.20 , nameMain: "Pain Saucisse", priceMain: 6.90 , nameDessert: "Mousse au fraise", priceDessert: 4.20, totalPrice: 13.50, }, 
        { id: uuidv4(), day: "Thursday", nameStarter: "Croquette duo", priceStarter: 4.20 , nameMain: "Pain Fromage", priceMain: 6.90 , nameDessert: "Mousse au Camembert", priceDessert: 4.20, totalPrice: 13.50, },
        { id: uuidv4(), day: "Friday", nameStarter: "Cocktail of crevettes", priceStarter: 4.20 , nameMain: "Panette poison", priceMain: 6.90 , nameDessert: "Mousse au Brie", priceDessert: 4.20, totalPrice: 13.50, },
        { id: uuidv4(), day: "Saturday", nameStarter: "Carpaccio", priceStarter: 4.20 , nameMain: "Pain Jambon", priceMain: 6.90 , nameDessert: "Tiramisu", priceDessert: 4.20, totalPrice: 13.50, }
    ])


    return(
        <MenuContext.Provider value={{menus}}>
            {props.children}
        </MenuContext.Provider>
    )
}
export {MenuContextProvider};

As you can see the value for the .Provider allready has the menus props passed.
This is the object that we created in our hook [menus, setMenus]. We don't use setMenus for now, so if you like you can remove it.

And there you go ! you just set up the useContext of an Array of Objecs, and dissected the props. From here you can easily deconstruct these props by passing them as a value for your components ! in part 2/2 i will show you how easy it is to get the wanted data, and also how to save them in localStorage. We go as far as creating a modal that pops up & where you can update the menu. Part one is allready quite the read, so i let you digest this one first ;) !

Thanks for peepin' & see you in part 2 !