PostgreSQL Basics

 

Pre-shell

Check if installed: psql --version

Check if it is running: sudo service postgresql status


Switch to the postgres user that comes by default on postgres installation (by default no password): sudo -u postgres -i

Enter the shell: psql

 

In the shell

Here you can now write RAW SQL queries and do stuff to your dbs. 

 


List out the current databases under postgres user: \l (\q to leave the view)

Create new database: CREATE DATABASE "database_name";

Switch to specified database: \c database_name

Create a new table for authors: 

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Create a new table for posts:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    summary VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    author_id INT NOT NULL
);


Insert into the authors table: 

INSERT INTO authors (name, email)
VALUES ('Jane Doe', 'ranchrobe23@gmail.com');

d


Change or add password to account (replace postgres with username used to login to psql): \password postgres





Express + Postgres

pnpm i pg


pnpm i --save @types/pg

create src/data/database.ts and insert:

import { Client } from "pg";

const client = new Client({

user: "postgres",

host: "localhost",

database: "your_db",

password: "your_password",

port: 5432,

});

export function connectToDatabase() {

client

.connect()

.then(() => {

console.log("Connected to PostgreSQL");

})

.catch((err) => {

console.error("Error connecting to PostgreSQL:", err);

});

}

export async function getAuthors() {

await client.connect();

try {

const result = await client.query("SELECT * FROM authors");

return result.rows;

} catch (error) {

console.error("Error retrieving authors:", error);

throw error; // Re-throw the error to be handled by the calling function

} finally {

await client.end();

}

}

 Make an authors table in your db with: id (int, pk, nn, ai), name (varchar255, nn), email (varchar255, nn)

 

Now go to src/index.ts and add:

import express, { Request, Response } from "express";

import cors from "cors";

import { connectToDatabase, getAuthors } from "./data/database";

const app = express();

app.use(cors());

 

// Connect to the database

connectToDatabase();

// Define the GET route for authors

app.get("/authors", async (req: Request, res: Response) => {

try {

const authors = await getAuthors();

console.log(authors); // Output authors to the console

res.json(authors); // Send the authors as JSON

} catch (error) {

console.error("Error retrieving authors:", error);

res.status(500).send("Error retrieving authors");

}

});

app.listen(3333, () => {

console.log("Example app listening on port 3333!");

});

when there is a GET request on localhost:3333/authors, the data will be returned as a json 

To see how to set up an Express server with TS, see: https://frontend-lord.blogspot.com/2023/07/typescript-express-setup.html