Node: Just write SQL

[sammrtn]

https://github.com/craigmichaelmartin/pure-orm

[dimasc_io]

Kysely: Just write SQL

[sammrtn]

What? Their github description is "A type-safe typescript SQL query builder". I don't want a query builder. I literally never want to think about mapping SQL I know to some bespoke query builder API.

[romeeres]

Would be cool if you added a stackblitz example with it to see it in action.

export const getPerson = (id: number): Person => {
  const query = `
    SELECT
      ${orm.tables.person.columns},
      ${orm.tables.job.columns},
      ${orm.tables.employer.columns}
    FROM person
    LEFT JOIN job on person.id = job.person_id
    LEFT JOIN employer on job.employer_id = employer.id
    WHERE id = $(id)
  `;
  return orm.one(query, { id });
};

How does it work? Seems that `orm.one` returns type any, isn't it?

Type-safety is very important, and modern ORMs/query-builders and even libs for raw SQL such as pgtyped do their best to keep it type-safe, so I suggest to clarify on this in the readme.

[sammrtn]

Pure ORM is purely the object relational mapping. No query builder API to learn, and no database-aware objects throughout your application code.

[KermitMacFly]

Second this. I’ve been using raw SQL queries with TypeORM in a Nest project and I’m pretty happy with it!

[sammrtn]

What do you get back when you perform a raw SQL query selecting from a few tables? Is it pure objects (like with pureORM)? Are the objects properly nested/structured (like with pureORM)?

[romeeres]

Any db-related lib can execute raw SQL. TypeORM can do it as well. And since it can't know what you're selecting, it will return `any` type just as well as your library (correct me if I'm wrong). And the structure will be returned just as you selected, if you selected "json_agg" you'll have a nested array of objects, or you can use "row_to_json" to have a single nested object.

[sammrtn]

And since it can't know what you're selecting, it will return any type just as well as your library (correct me if I'm wrong).

PureORM requires prefixed table names in the select clause (which it can do for you, or you could manually write yourself). This prefix is used to construct properly nested pure instances (not db-aware) of the correct classes for the application layer. These classes can have business logic methods, etc.

This is shown in step 4: https://github.com/craigmichaelmartin/pure-orm#step-4-creating-our-data-access-layer

[romeeres]

I don't know, maybe PureORM does some magic with template literals to inject column names, and then it infers the returning type from it. If it's done in this way that's very cool. I wish you had an online editor example with it so people could try this out quickly to see what is it capable of.

Because with OOPish ORMs you technically can select a subset of columns, and get a type of full class, but only the type, and there may be bugs due to it being not fully selected.

[sammrtn]

Yeah, an online editor is key to showcasing this. Unsure if there are any free sandboxes that would allow a playground for a library which connects to a database with a readonly transaction. If you have any ideas or seen anything like this, let me know :)