Client is Angular 8 with NgRx-Data; Server is NestJs + MySQL. How to get data using criteria and SQL?

Question

As Angular, NgRx-Data and NestJs are becomming more and more popular, I feel there may be quite a few programmers who are wondering about the querying syntax for the following.

I have a running prototype of a client (front end) composed in Angular 8 with NgRx-Data. On the back end is a NestJs based server + MySQL.

I can nicely retrieve and pass data between all parts, except queries. I do not seem to be able to find proper documentation on the syntax.

Here is the example of how the client is set:

                        // Simple entity example (all ngrx-data metadata are declared and set):
export class Hero { 
    id: number;
    name?: string;
    age?: number;
}

Entity Service / for fetching data

@Injectable({providedIn: 'root'})
export class HeroService extends EntityCollectionServiceBase<Hero> {
  constructor(serviceElementsFactory: EntityCollectionServiceElementsFactory) {
    super('Hero', serviceElementsFactory);
  }
}

Component for showing data

@Component({
  selector: 'hero-comp',
  templateUrl: './hero.component.html', 
  styleUrls: ['./hero.component.scss']
})
export class HeroComponent {
    heroData$: Observable<Hero[]>;
    constructor(private heroDatService: HeroService) {
        this.heroData$ = this.heroDatService.entities$;
    }

    private getAllData() {
                          // This works nicely, I get all records from the db via server
        this.heroDatService.getAll();
    }

    private queryData() {
                          // This queryParams syntax fails - server complains with this error:
                          //      [HttpExceptionFilter] GET /hero/?age>20 
                          //      QueryFailedError: ER_EMPTY_QUERY: Query was empty
                          // QUESTION: What is the proper syntax?
        let queryParams: QueryParams = {
            'age > 20'
        }
        this.fetchDataService.getWithQuery(queryParams);
    }

Here is the server related code excerpt: - (there is a service, but for simplicity here, I moved the repo functions to the controller functions):

@Controller('hero')
export class HeroController <Hero> {
    constructor(readonly repo: Repository<Hero>) {}

                          // This returns nicely all Hero records from the MySQL db server
@Get()
async getAll(): Promise<Hero[]> {
    return await this.repo.find();
} 
                          // This does not work !
                          // I am not sure about any piece of the code here !
@Get('query')
async query(@Query() sql): Promise<any> {
                          // Does the sql argument need to be manipulated into parameters: {...} ?
                          // If yes - how ?
    let parameters: undefined;
    return await this.repo.query(sql, parameters);
}

Please see the comments above each code line - the problems are spelled out there.

And here are the important questions:

On the client how do we properly pass query criteria for some of these examples: - {'age > 20'} - {'age BETWEEN 20 AND 40'} - {'age = 20 OR age = 30 OR age = 40'} - {'name = "Superman"'} - {'name LIKE "Super%"'} - etc.

Also, what would be the syntax for passing a full SQL sentence, such as: - {'SELECT * FROM Heroes WHERE name LIKE "Super%" AND Age > 20;'} and getting the result from the server.

What needs to be done on both ends (client and server) for these queries to work?

All inputs much appreciated.

Answer 1

It seems like you're confused about HTTP Query parameters and SQL querying, which are two different topics. Query parameters, in the context of HTTP, are parameters that can be passed from the client to the server and modify the outcome of the HTTP call. Query parameters are always passed starting with a ? in the URL in the form of <key>=<value> and separated with an &.

A SQL Query is a specific string that tells a SQL server what table to query against, for what columns, with what conditions. Usually in the form of SELECT <columns> FROM <table> WHERE <conditions>;, but they can be much more complex than that.

Now that definitions are out of the way, the endpoint you are trying to reach should be /hero/query. You'll need to end up doing a lot of data processing on the server side of things, sanitization, ensuring that the incoming string is proper for SQL WHERE clauses, ensuring that you won't be vulnerable to SQL Injections (if you pass the query params straight to your query, you will be), but a very very naive approach would look something like this:

@Controller('hero')
export class HeroController {

  constructor(@InjectRepository(Hero) private readonly repo: Repository<Hero>) {}
  @Get('query')
  queryForHero(@Query() queryParam) {
    return this.repo.query(`SELECT <field_go_here> FROM Hero WHERE ${queryParams.query};`);
  }
}

For the love of all that is good in the world, do not actually use the above code. It is 100% vulnerable to all kinds of SQL injections.

A corresponding request could look something like

curl http://<host>/hero/query?query=Name%3D%27Superman%27

This would cause the server to use the query

SELECT <fields_go_here> FROM Hero WHERE Name='Superman';

You'll really want to add in a ton of validations on what is coming into your server before just sending it to your SQL server lest you end up like Little Bobby Table.

Hopefully this helps get you on the right path.