week 10.1, postgraceSQL, creating table in database

// initialise an empty typescript project

npm init -y
npx tsc --init

-:  change the rootDir and outDir in tscongig.json


instll pf libarary and it's type (because we're usign TS)

npm install pg
npm install @types/pg

 

Create a similar node.js app that lets you put data

=======================================

//write a function to create a uses table in your databse.

import  { Client } from 'pg'

const client = new Client({
connectionString: ""
 })

client.connect()

//curd operations

// write a function to create a uses table in your databse.

import  { Client } from 'pg'

const client = new Client({

connectionString: "postgresql://test_owner:eSLcgdkBX9D1@ep-fragrant-boat-a56z1tgw.us-east-2.aws.neon.tech/test?sslmode=require"

 })

 async function createUsersTable() {

    await client.connect()

//create operation 

    const result = await client.query(`

    CREATE TABLE users (

        id SERIAL PRIMARY KEY,

        username VARCHAR(50) UNIQUE NOT NULL,

        email VARCHAR(255) NOT NULL,

        password VARCHAR(255) NOT NULL,

        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP

    )

    `)

    console.log(result);

   

 }

createUsersTable();

//here back-ticks  "``" allows you to operate multiple operation in pgsql

for run the program use this command line by line

1. tsc -b
2. node dist/index.js

import { Client } from 'pg'
 

//simply code to  connect database

const client = new Client({
  host: 'my.database-server.com',
  port: 5334,
  database: 'database-name',
  user: 'database-user',
  password: 'secretpassword!!',
})

client.connect()

// write a function to create a users table in your database.**
import { Client } from 'pg'
 
const client = new Client({
  connectionString: "postgresql://postgres:mysecretpassword@localhost/postgres"
})

async function createUsersTable() {
    await client.connect()
    const result = await client.query(`
        CREATE TABLE users (
            id SERIAL PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        );
    `)
    console.log(result)
}

createUsersTable();

===================================================

Certainly! Below is the TypeScript code that corresponds to the SQL statements you provided:

```typescript
// Define a User interface to represent the database table
interface User {
    id: number;
    email: string;
    username: string;
    password: string;
    firstName?: string;
    lastName?: string;
}

// Create a function to execute the SQL statements
function createTableAndIndexes(): void {
    // Execute the CreateTable statement
    const createTableSQL = `
        CREATE TABLE "User" (
            "id" SERIAL NOT NULL,
            "email" TEXT NOT NULL,
            "username" TEXT NOT NULL,
            "password" TEXT NOT NULL,
            "firstName" TEXT,
            "lastName" TEXT,
        
            CONSTRAINT "User_pkey" PRIMARY KEY ("id")
        );
    `;

    // Execute the CreateIndex statements
    const createEmailIndexSQL = `
        CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
    `;

    const createUsernameIndexSQL = `
        CREATE UNIQUE INDEX "User_username_key" ON "User"("username");
    `;

    // Log the SQL statements (you can replace this with actual database queries)
    console.log(createTableSQL);
    console.log(createEmailIndexSQL);
    console.log(createUsernameIndexSQL);
}

// Call the function to create the table and indexes
createTableAndIndexes();
```

Feel free to adapt this code to your specific TypeScript project or integrate it with your database library. Let me know if you need any further assistance! 😊

 

 ===================================================

// Define a User object to represent the database table
const User = {
    id: null,
    email: '',
    username: '',
    password: '',
    firstName: '',
    lastName: ''
};

// Create the User table (assuming you're using a database library)
// Note: Replace the database-specific syntax with the appropriate one for your database
// For example, if you're using Node.js with PostgreSQL, you'd use the 'pg' library
// to execute the queries.

// Create the User table
const createTableSQL = `
    CREATE TABLE IF NOT EXISTS "User" (
        "id" SERIAL PRIMARY KEY,
        "email" TEXT NOT NULL,
        "username" TEXT NOT NULL,
        "password" TEXT NOT NULL,
        "firstName" TEXT,
        "lastName" TEXT
    );
`;

// Create unique indexes for email and username
const createEmailIndexSQL = `
    CREATE UNIQUE INDEX IF NOT EXISTS "User_email_key" ON "User"("email");
`;

const createUsernameIndexSQL = `
    CREATE UNIQUE INDEX IF NOT EXISTS "User_username_key" ON "User"("username");
`;

// Execute the SQL statements (you'll need to use your database connection)
console.log(createTableSQL);
console.log(createEmailIndexSQL);
console.log(createUsernameIndexSQL);
 

 

 

 

 

 

 sdfs