TypeScript and SQL: Six Ways to Bridge the Divide

https://effectivetypescript.com/2023/08/29/sql/

by danvk • 3 years ago

2 3 3 years ago

I have personally come to the conclusion that typescript should not be used for writing a backend. The lack of RTTI means that you have to either write a TON of verbose code mapping Typescript class fields (note: different than ES6 classes, which would be more useful in this regard) to SQL statements.

What this means in practice is using ORM frameworks which require you to put decorators (which are still somehow "experimental" despite all major frameworks relying on them) on your class fields to generate sql commands.

But you also need to handle user input, and validate it. You can use JSONSchemas in some frameworks (fastify), but there's no really good way to generate these automatically yet, and you'll need to update them as you add more fields. This is most easily done through (a lot) more decorators

Additionally, because there's no RTTI, you can't rely on the typescript operators like keyof to generate other fields at runtime (only at compile time). Let's say you have a query interface you want to write. You can provide the name of a field to filter by, along with a value. This is a JSON POST Body. Maybe you have a model like:

    export class BlogPost {
        title: string;
        body: string;
        postedOn: Date;
        postedBy: string;
    }

To query the query interface, you'll need to declare every field again independently, because you can't apply class validators to the results of keyof operators:

    export class QueryBlogPost {
        @ApiProperty()
        @IsString()
        @IsOptional()
        title?: string;

        @ApiProperty()
        @IsString()
        @IsOptional()
        body?: string;
        
        @ApiProperty()
        @IsString()
        @IsDateString()
        postedOn?: Date;

        @ApiProperty()
        @IsString()
        @IsOptional()
        postedBy?: string;
    }

let's say you want to specify all the fields which you want returned, so you can limit the size of the response (perhaps you don't want to return the blog post body in a search request). Again, you cannot use keyof here to get a list of valid fields at runtime. Instead, you need to specify them manually:

    export class QueryBlogPost {
        @ApiProperty()
        @IsString()
        @IsOptional()
        title?: string;

        @ApiProperty()
        @IsString()
        @IsOptional()
        body?: string;
        
        @ApiProperty()
        @IsString()
        @IsDateString()
        postedOn?: Date;

        @ApiProperty()
        @IsString()
        @IsOptional()
        postedBy?: string;

        @ApiProperty()
        @IsOptional()
        @IsEnum(["title", "body", "postedOn", "postedBy"], {each: true})
        fields?: string[]
    }

If you add a new field, remember to manually add that field to your enum

Don't forget non-primitive types: if you have a nested class, you need to add multiple decorators for that, since you can't get the constructor or type of the class at runtime:

    export class Author {
        @ApiProperty()
        @IsString()
        firstName: string;

        @ApiProperty()
        @IsString()
        lastName: string;         
    }


    export class QueryBlogPost {
        //...
        @ApiProperty()
        @ValidateNested()
        @Type(() => Author)
        @IsOptional()
        author?: Author;
    }

not to mention if it's an array, in which case every class needs @IsArray(), @ValidateNested({each: true}) added to it

Plus all the documentation decorators which add example queries, queries which take arrays (for operators such as "any" or "in".

This isn't to mention return type DTOs, or the decorators for the ORM.

Typescript is a frontend language. It is good for creating data. It is very bad for receiving it. With typescript as a backend, you end up writing the same code 5 times which introduces bugs. Do not write typescript backends just because your junior devs are afraid to learn Java or C# or a language more suited for backend development

danvk• 3 years ago

The lack of RTTI in TypeScript _is_ definitely a pain but the jump to "must use decorators" and "TypeScript is not a server language" is unjustified. There are many approaches to validation and RTTI with TS. For example, you can generate JSONSchema from your TS types (see https://github.com/danvk/crosswalk for an example of this approach) or you can use a tool like Zod (https://zod.dev/) to declare your schemas as runtime values and derive TS types from those.

Writing your backend in Java or C# comes with its own issues, i.e. you can't share code between backend and frontend.

shortrounddev22• 3 years ago

The solutions you described are hacks, at best, trying to compensate for the deficiencies of the language.

I have never wanted to share code between frontend and backend other than classes/type information. I don't know why you would want to repeat code

TypeScript and SQL: Six Ways to Bridge the Divide

Related Stories