Installing PostgreSQL

1. Using Locally

1.1 Use this command to download PostgreSQL:

sudo apt-get install postgresql

1.2 Now start the PostgreSQL service with:

sudo service postgresql start

To stop, use:

sudo service postgresql stop

To check the status, use:

sudo systemctl status postgresql

1.3 To control PostgreSQL using the command line, use psql:

sudo -u <username> psql # We have a default user created as 'postgres'

1.4 Create a user for your use case named sadiq with the command:

sudo -u postgres createuser <username>

1.5 Set a password for the user:

sudo -u postgres psql
psql=# ALTER USER <username> WITH ENCRYPTED PASSWORD '<password>';

1.6 To create a database, use the username under which you are creating the database:

sudo -u sadiq createdb <dbname> # Here, 'sadiq' is the username

1.7 Grant database privileges to the user in the PostgreSQL terminal:

sudo -u <username> psql
# This opens a postgres terminal
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <username>;

1.8 To connect to a database, use the following command:

sudo -u <username> psql -d <dbname>

1.9 The connection string will be:

postgresql://<username>:<password>@localhost:5432/<database_name>

Some Frequently Used Commands in PostgreSQL

\l # List databases
\c <db-name> # Switch to another database
\dt # List tables in a database
\d <table-name> # Describe a table
\d+ <table-name> # Provide detailed information about a table
\dn # List schemas
\du # List users and their roles
\q # Quit the PostgreSQL terminal
dropdb <database_name> # Delete a database
DROP TABLE <table_name> # Delete a specific table

Note: Follow the PostgreSQL guide by Prisma.io for more information: Prisma PostgreSQL Guide

2. Using Docker

2.1 Create a file named docker-compose.yml or docker-compose.yaml in the project you want to use PostgreSQL with (e.g., in the db folder or at the root) and paste the following contents:

version: '3'
services:
  db:
    image: postgres
    restart: always
    volumes:
      - ./data/db:/var/lib/postgresql/data
    ports:
      - 5432:5432  # Ensure no other container is running on port 5432
    environment:
      - POSTGRES_DB=dbname
      - POSTGRES_USER=username
      - POSTGRES_PASSWORD=password

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

2.2 To run the containers, use:

docker-compose up -d # Runs in detached mode, so no need to open a separate terminal

2.3 To stop the containers, use:

docker-compose stop

2.4 The connection string will be:

postgresql://<username>:<password>@localhost:5432/<database_name>

Handy Installation Guides

Installing Prisma

3. Download Prisma

3.1 Download Prisma with the following commands:

npm install prisma @prisma/client

3.2 Now initialize Prisma with:

npx prisma init

3.3 This generates a schema.prisma file that contains the models, similar to the models folder in MVC architecture.

4. Define Models

4.1 Create the models (tables) schema in it like:

model User {
  id         Int      @id @default(autoincrement())
  username   String   @unique
  password   String
  firstName  String
  lastName   String
}

4.2 Migrate the database using the command:

npx prisma migrate dev --name <migration_name>

4.3 Now generate the auto-generated client with:

npx prisma generate

4.4 The auto-generated client means, for example, in MongoDB we have a user model from where we can access the User and perform operations on it. With Prisma, this command creates and types a definition file (can navigate on ctrl-click on the model but can't see it) in @prisma/client, allowing us to access models and auto-generate functions by Prisma on that client, as shown below:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function insertUser(username: string, password: string, firstName: string, lastName: string) {
  const res = await prisma.user.create({
    data: {
        username,
        password,
        firstName,
        lastName
    }
  });
  console.log(res);
}

insertUser("admin1", "123456", "sadiq", "mohammad");

4.5 For supported functions and more information, refer to the Prisma Documentation. Kudos to Prisma for their documentation!

MdSadiqMd/PostgresAndPrisma.md