Raw sql query

[Aranir]

For my application I would need to create a Union between two tables.

I do not expect that typeorm would support the union query, but instead is it possible to execute an arbitrary sql string and get back raw json objects?

[pleerock]

right, you can use repository.query or entityManager.query methods which return you raw database results.

[mathsalmi]

@pleerock How should I pass parameters to the query method in order to avoid SQL injection attack?

[pleerock]

@mathsalmi added support of parameters in 0.0.9-alpha.2

[mathsalmi]

Wow that was fast! Thanks for the great work! =D

[Knaackee]

how to reference a parameter in the query string? Cant find something in the docs.

[pleerock]

@Knaackee using query method its second argument: entityManager.query("SQL", [params]);, using QueryBuilder its setParameter method

[Knaackee]

yes but how to reference the parameter inside "SQL"

[pleerock]

it depends on underlying driver. For mysql its :param, for postgres its $param.

[valVk]

I thought that Postgres does not have named params in queries I'm using parametrized queries like below

entityManager.query('SELECT u.name FROM users AS u WHERE u.name = $1 AND u.lastName = $2', ['John', 'Doe']);

[pleerock]

@valVk yes you are right, thats a correct way of doing it for postgres. I just confused with multiple drivers 😖

[roelzkie15]

It is also possible to perform raw queries like INSERT DELETE UPDATE using entityManager.query?

[pleerock]

yeah any sql query

[roelzkie15]

Very nice! thanks @pleerock

[chriszrc]

Let me know if this is an actual issue or not, but the docs say that the query method:

Executes a raw SQL query and returns a raw database results.
query(query: string): Promise<any>

So I expected this to fail:
Entity:

@Entity()
@Index(["country","iso3","region","name","pollutant"], { unique: true })
export class ExposureRegion implements Region {

    @PrimaryColumn()
    @Column({ length: 56 })
    country?: string;

    @PrimaryColumn()
    @Column({ length: 6})
    iso3?: string;

    @PrimaryColumn()
    @Column({ length: 12})
    region?: string;

    @PrimaryColumn()
    @Column({ length: 56})
    name?: string;

    @PrimaryColumn()
    @Column({ length: 5})
    pollutant?: string;

    constructor(args: ExposureRegion){
      Object.assign(this, args);
    }
}

Service

  public async findByExposureRegionAndGroupBy(exposureRegion:ExposureRegion, groupBy:string): Promise<ExposureRegion[]>{
    return this.exposureRegionRepository.query(`
      select name
      from exposure_region
      where region = $1 and pollutant = $2
      group by name
    `,[exposureRegion.region,exposureRegion.pollutant]);
}

The return type of the query should be any, but typescript is allowing me to set the signature of my method to a promise with a real type (and without any casting). In the controller that calls this service method, I can even do:

let results:Promise<ExposureRegion[]> = this.regionService.findByExposureRegionAndGroupBy(new ExposureRegion({region:region, pollutant:pollutant}),groupBy);

results.then(exposureRegions => {
   console.log(exposureRegions[0].name);
});

I can see that the object is indeed not a true instance of ExposureRegion, but for all intents and purposes, the result is the same, and the json response still matches, so even though the query method is returning a raw db result, it looks like we can still fudge it and keep all our types in order too. This is quite nice!

[pleerock]

Be careful with such approach because you actually lying to compiler. This can bring problems for example if your entity has methods and you pass entity somewhere to some function that use this method, but in this case you are passing to this method your fake object - this will cause a runtime errors.

[chriszrc]

Hi, thanks for the reply, yes, I could certainly see the problem that would present (although in this case, the entity doesn't have methods so maybe its ok).

So would you recommend just doing a conversion like this to handle the raw data mapping then?

public async findByExposureRegionAndGroupBy(exposureRegion:ExposureRegion, groupBy:string): Promise<ExposureRegion[]>{
    return this.exposureRegionRepository.query(`
      select name
      from exposure_region
      where region = $1 and pollutant = $2
      group by name
    `,[exposureRegion.region,exposureRegion.pollutant]).then(exposureRegions=>{
      return exposureRegions.map(val => new ExposureRegion(val));
    });

[pleerock]

conversion is better then nothing. But you may have issues anyway. For example if you use custom database column names - it will break because raw results return database column names. Or your relations won't be instances of real entities too - you may need to perform conversion in cycle and maybe recursively if needed.

[chriszrc]

Right, yes, thank you for pointing that out. As far as names go, that's not too bad since we can include the entity name as an alias for the column in the query. But hydrating the rest of the entity relations could definitely get hairy. Fortunately, the majority of times that I turn to these raw custom queries is mainly for analytic queries that aren't trying to actually include joined table data. Thanks for all the clarification around this issue!

[pleerock]

@chriszrc also in most cases when you are loading your entities you don't really need to use raw queries. Im sure QueryBuilder functionality is enough for 90% of use cases, and maybe even that 10% you can rethink and bring them to QueryBuilder functionality. So please make sure to try QueryBuilder first, before doing raw sql

[chriszrc]

Yes, I whole heartedly agree, applications that reduce the amount of native sql will be easier to maintain down the line, easier to add new features to, etc., etc. I've been using hibernate/jpa and then querydsl (all within spring) for many years, and when I could use those technologies for my queries I did. It did help though that jpa added support for mapping native queries to entities for those queries that couldn't be done without native (for a while, this was primarily queries that used the db's spatial functions).

Looking ahead however, I can see that graphql is eating into the orm market, and it does look like a great fit for the types of queries it supports (partial/whole selects with where criteria and optional fetching of child entities). I especially liked hearing your response here:

#934 (comment)

because I think that kind of support would really shine as features you can't get with graphql. I would say that custom selections like that were probably the #1 reason in the past I've had to return to raw sql-

[chriszrc]

I did also notice that sequelize let's you return entities from raw sql as well - http://docs.sequelizejs.com/manual/tutorial/raw-queries.html

[fullofcaffeine]

Wondering why typeorm chose not to return entities from raw sql queries, it really makes life harder than needed for the cases where custom / direct interface with the RDBMs is needed... any hints?

[suyashgulati]

At least it should provide generic type to query function so developer can add the return type by himself.

[rish2792]

How to pass array in queryparam in query method of typeorm dynamically using IN operator in sql query

[ChunYoung0518]

One question might not related, but would love to know if typeorm has a size limit for the raw sql? If I have a in clause, does it restrict how large is the final raw sql?

[simplenotezy]

@ChunYoung0518 no, but javascript (memory) or your database could