I recently saw a job opening where they wanted a Nodejs ,TypeORM and GraphQL developer So I decided now would be a good time to brush up on those skills
Throw back to 3 years a go when Ben Awad was the predominant react influencer on YouTube when he dropped this absolute banger of an intermediate full-stack tutorial
But before starting with that I went back to do a Postgres refresher and tried out a freecodecamp video
In the video they ha a sample movies database which I decided to build an app around as practice and expose a GraphQL API of it
Checkout sample database setup part
Before we do anything , we'll walk over how we'll use TypeORM with GraphQL
The idea will be to
- Create a
GraphQl
server with express andapollo-server-express
- Using TypeORM as our ORM to talk to the database with and define the schema
- Use
type-graphql
to define the GraphQ schema and resolvers for maximum type safety
The TypeORM + TypeGraphQl definition would be a class with respective annotations
import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"
import { ObjectType, Field, Int, InputType } from 'type-graphql'
import { registerEnumType } from "type-graphql";
enum FilmRatings{
PG13 = "PG-13",
NC17 = "NC-17",
R = "R",
G = "G",
PG = "PG"
}
registerEnumType(FilmRatings, {
name: "rating", // Mandatory
description: "The film rating", // Optional
});
@ObjectType()
@InputType()
@Entity()
export class Film {
@Field(() => Int)
@PrimaryGeneratedColumn()
film_id: number;
@Field(()=>String)
@Column({ type: "text" })
title: string;
@Field(() => Int)
@Column({ type: "integer" })
release_year: number;
@Field(() => Int)
@Column({ type: "smallint" })
language_id: number;
@Field(() => Int)
@Column({ type: "smallint" })
rental_duration: number;
@Field(() => Int)
@Column({ type: "numeric" })
rental_rate: number;
@Field(() => Int)
@Column({ type: "smallint" })
length: number;
@Field(() => Int)
@Column({ type: "numeric" })
replacement_cost: number;
@Field(()=>FilmRatings)
@Column({ type: "enum",enumName: "rating" })
rating: FilmRatings
@Field(() => String)
@Column({ type: "timestamp without time zone" })
last_update: Date;
@Field(() => [String])
@Column({ type: "text", array: true })
special_features: string[];
@Field(()=>String)
@Column({ type: "text" })
fulltext: string;
}
When you already have an existing database you can use the TypeORM option
synchronize: false,
import { DataSource } from "typeorm"
export const AppDataSource = new DataSource({
type: "postgres",
host: "localhost",
port: 5432,
username: "postgres",
password: "postgres",
database: "dvdrental",
synchronize: false,
migrations: ["src/migrations"],
entities:["src/entities/*.entity.ts"],
})
The issue I immediately ran into was how to use the column types database already as to generate TypeORM schemas manually
There a was a Postgres query to return a table's column types
SELECT table_name,
(SELECT string_agg(column_name, ', ')
FROM information_schema.columns
WHERE table_schema = t.table_schema
AND table_name = t.table_name) AS columns,
(SELECT string_agg(data_type, ', ')
FROM information_schema.columns
WHERE table_schema = t.table_schema
AND table_name = t.table_name) AS column_types
FROM information_schema.tables t
WHERE table_schema = 'public'
But it wasn't very clear on Enum types and other types like that for example the film table had rating column that was an Enum type
You could get the Enum values using a distinct select
SELECT DISTINCT rating FROM film
But this didn't feel scalable so I built a simple Postgres GUI as a challenge to view the columns and their types and feed that info into an LLM to get a schema.
Postbase , Am bad at naming things but in It's basics
When loaded up the /pg
route , This snippet will run and fetch all the databases running on your Postgres server
const query = useSSQ(async (ctx) => {
try {
const config = safeDestr<DbAuthProps>(ctx.cookie?.pg_cookie);
if (!config) {
return { result: null, error: "no config" };
}
const sql = postgresInstance(config);
if (!sql) {
return { result: null, error: "no config" };
}
if (config.local_or_remote === "remote") {
return {
result: { redirect: `/pg/${sql.options.database}` },
error: null,
};
}
const database = (await sql`SELECT datname FROM pg_database`) as any as [
{ datname: string },
];
const users = (await sql`SELECT * FROM pg_catalog.pg_user`) as any as [
{ usename: string },
];
return { result: { database, users }, error: null };
} catch (error: any) {
console.log(" === error == ", error.message);
return { result: null, error };
}
});
Clicking on one database will prompt you for it's credentials
const query = useSSQ(async (ctx) => {
// console.log("This is a one database ", db_name);
try {
const config = safeDestr<DbAuthProps>(ctx.cookie?.pg_cookie);
if (!config || !config?.local_or_remote) {
return { rows: null, error: "no config" };
}
const sql = postgresInstance(config);
if (!sql) {
return { rows: null, error: "no config" };
}
const tables = (await sql`
SELECT table_name,
(SELECT string_agg(column_name, ', ')
FROM information_schema.columns
WHERE table_schema = t.table_schema
AND table_name = t.table_name) AS columns,
(SELECT string_agg(data_type, ', ')
FROM information_schema.columns
WHERE table_schema = t.table_schema
AND table_name = t.table_name) AS column_types
FROM information_schema.tables t
WHERE table_schema = 'public';
`) as any as [
{
table_name: string;
columns: string;
column_types: string;
},
];
// console.log(" === tables == ", tables[0]);
return { tables, error: null };
} catch (error: any) {
console.log(" === error == ", error.message);
return { tables: null, error: error.message };
}
},{key:db_name});
Clicking on one table will prompt you to pick one of the rows as the primary key for sorting
The table view consists of 3 tabs
list
We select 10 first rows in this table and will get the next 10 using pagination
const query = useSSQ(
async (ctx) => {
try {
const offset = (table_page - 1) * 10;
const config = safeDestr<DbAuthProps>(ctx.cookie?.pg_cookie);
if (!config || !config?.local_or_remote) {
return { rows: null, error: "no config" };
}
const sql = postgresInstance(config);
if (!sql) {
return { rows: null, error: "no config" };
}
const rows = (await sql`
SELECT * from ${sql(db_table)}
ORDER BY ${sql(db_primary_column)}
OFFSET ${offset}
LIMIT 10`) as any as [{ [key: string]: any }];
return { rows, error: null };
} catch (error: any) {
console.log(
" === useSSQ OneTableRowsOffsetPages error == ",
error.message,
);
return { rows: null, error: error.message };
}
},
{ key: `${db_name}/${db_table}` },
);
types
This part is powered by kanel to generate the types and
Shikiji to style the output
TypeORM + TypeGraphQl class
This part is powered by Gemini AI to generate TypeORM and TypeGraphQl classes from our generated typescript interfaces
Some notable utilities i used include
- a script that reads the type files and concats all the imports inside it together in one
import fs from "fs";
import fsp from "fs/promises";
import path from "path";
export async function getTextFromFileWithImports(
filePath: string,
currentText: string,
dbName: string,
) {
try {
let count = 0;
// console.log("===== file path ======",filePath);
const fileContent = await fsp.readFile(filePath, "utf8");
// console.log("===== file content ======",fileContent);
const importRegex = /import.*?from ['"](.+?)['"];/g;
let match;
let updatedText = currentText;
if (count === 0) {
updatedText += fileContent;
}
while ((match = importRegex.exec(fileContent)) !== null) {
count += 1;
const importedFilePath = match[1];
const absoluteImportedPath = path.resolve(filePath);
// console.log("===== absolute path ======",absoluteImportedPath);
const importedFileContent = fs.readFileSync(absoluteImportedPath, "utf8");
updatedText += "\n" + importedFileContent;
const next_file_path =
path.resolve("pg/" +dbName+"/public", importedFilePath) + ".ts";
// console.log(" ====== next file path ======",next_file_path);
updatedText =
(await getTextFromFileWithImports(next_file_path, updatedText, dbName)) ?? ""; // Make the process recursive
}
// console.log("===== count ======",count);
return updatedText;
} catch (error) {
console.log("====== error in readTextFromFileWithImport === ", error);
return;
}
}
- A helper to trim out any markdown code blocks that gemini was adding randomnly in it's responses like this
export function trimOutCodeBlock(input: string) {
const codeBlockStart = "```
typescript";
const codeBlockEnd = "
```";
const startIndex = input.indexOf(codeBlockStart);
const endIndex = input.indexOf(
codeBlockEnd,
startIndex + codeBlockStart.length,
);
if (startIndex !== -1 && endIndex !== -1) {
return (
input.slice(0, startIndex) +
input.slice(endIndex + codeBlockEnd.length)
);
} else {
return input;
}
}
- A helper for Postgresjs to handle local or remote connections config
import postgres from "postgres";
import { RequestContext } from "rakkasjs";
export function postgresInstance(options?: DbAuthProps) {
if(!options){
return
}
if (options?.local_or_remote === "local") {
return postgres({
host: options.db_host,
user: options.db_user,
password: options.db_password,
database: options.db_name,
idle_timeout: 20,
max_lifetime: 60 * 30,
});
}
return postgres(options.connection_url, {
idle_timeout: 20,
max_lifetime: 60 * 30,
});
}
export interface LocalDBAuthProps {
local_or_remote: "local";
db_name: string;
db_password: string;
db_user: string;
db_host: string;
}
export interface RemoteDBAuthProps {
local_or_remote: "remote";
connection_url: string;
}
export type DbAuthProps = LocalDBAuthProps | RemoteDBAuthProps;
export function setPGCookie(ctx: RequestContext<unknown>, value: string) {
ctx?.setCookie("pg_cookie", value, {
sameSite: "strict",
httpOnly: false,
maxAge: 60 * 60 * 24 * 30,
path: "/",
});
// console.log( " ============= set pg cookie ============ ", ctx.cookie?.pg_cookie);
}
export function deletePGCookie(ctx: RequestContext<unknown>) {
ctx?.deleteCookie("pg_cookie", {
path: "/",
});
// console.log( " ============= deleted pg cookie ============ ", ctx.cookie?.pg_cookie);
}
And with that we have a basic PostgresSQL GUI hat generates TypeORM and TypeGraphQl classes for us , I feel like this will come in handy soon 😏 . feel free to play with it and discover new patterns
Next step is using something like wails or tauri or electron to package it into a binary for better DX
Top comments (0)