Interesting project. Are there any plans to support other database drivers such as mysql2?

After updating to 0.0.36 app wont start and reports missing sqorn-adapter-pg.
Sqorn Demo page is also broken at the moment and is reporting same error.

Looks very cool! I'd love to see support for Oracle Database and bind variables.

Could you please add a coverage badge preferably coveralls so we can track and add unit tests accordingly?

Travis directly integrates once you set the api key so should be a walk in the park.

I am explaining my issue using examples to convey it better from my side.

const { text, args } = sq.fromorders.whereCustomerID=${'FRANK'}.joinorder_details.onorders.OrderID=order_details.OrderID.joinproducts.onorder_details.ProductID=products.ProductID.whereProductName=${'Tofu'}.query;

generates the following as text, but it wont work on pg because the column names doesn't follow prescribed naming conventions set by postgres, but I am guessing escaping the column names would easily resolve the issue.

"select * from orders join order_details on (orders.OrderID=order_details.OrderID) join products on (order_details.ProductID=products.ProductID) where (CustomerID=$1) and (ProductName=$2)"

After escaping the following query works with postgres.

select * from orders join order_details on (orders."OrderID"=order_details."OrderID") join products on (order_details."ProductID"=products."ProductID") where ("CustomerID"='FRANK') and ("ProductName"='Tofu')

From the docs:

Multiple objects passed to .where are joined with or.

sq.from`person`.where({ name: 'Rob' }, { name: 'Bob' }).query

{ text: 'select * from person where (name = $1 or name = $2)',
  args: ['Rob', 'Bob'] }

Any reason we couldn't join the objects with IN (?, ?) notation?

I'm trying to convert a query that uses a common table express with an insert + on conflict clause, I'm not having much luck

WITH vanity AS (
  INSERT INTO vanity_ids (organization_id, counter) 
  VALUES ($1, $2)
  ON CONFLICT (organization_id) DO UPDATE
   SET counter = vanity_ids.counter + 1
)
...

const pg = require('@sqorn/pg')
const vanity =
  pg
    .from `vanity_ids (organization_id, counter)`
    .insert`values (${1}, ${1})`
    .sql`ON CONFLICT (organization_id) ON CONFLICT`
    .sql`SET counter = vanity_ids.counter + 1`
    .returning`counter`

pg.with({vanity})

I am attempting to port working code from knex to sqorn and running into an issue handling dynamic column names in expressions.

In knex I can take an object like { string_example: { is: "foo" }} where "string_example" is the column name. "is" defines the SQL comparison operation i.e. "=", and "foo" is the value resulting in a sql expression string_example = "foo". I have about 19 different expressions working beyond "is" in this function: https://github.com/jakelowen/knex-graphql-filters/blob/master/lib/applyFilters.js

The above knex based example is very useful in my graphql-servers where I can allow the user build rich nested dynamic queries by choosing among many predefined and strongly typed columns and comparators:

But in attempting to port this over to sqorn I am having a very hard time groking how to handle dynamic column names in an expression. Can you give me an example of how to pull off the sqorn equivalent of query.where(key, "NOT ILIKE", %${opValue});

If I can get this working, I am happy to turn it into a little utillity npm package for community use.

Triple checked the docs and source code, is there a way to output a full query string?
eg:

const min = 20, max = 30
const People = sq.l`select * from person where age >= ${min} and age < ${max}`

People.query
{ text: 'select * from person where age >= $1 and age < $2',
  args: [20, 30] }

// Sample API
People.query.toString()
'select * from person where age >= 20 and age < 30'

Hi,

I reviewed this code a while ago and I have some security concerns. I am interested in how sqorn deals with nested tagged templates. As far as I understand it uses this helper function to differentiate parameters to be binded from nested tagged templates.

const isTaggedTemplate = ([strings]) => Array.isArray(strings) && strings.raw

Let say this is how my API finds users.

sq.where`id IN ${ctx.body.users}`

Now suppose that a malicious user submits a value for ctx.body.users that has the same structure as a tagged template, for instance ctx.body.users = [['(1234) ; DROP ALL TABLES; --', ''], 'dummy']. Is there any mechanism in sqorn that prevents interpreting this value as a tagged template?

I am not sure if there's a supported workaround for this issue, but still I would like to understand what could be done to mitigate this. I will be explaining my issue with an example to convey it better.

const { text, args } = sq.fromorders.where({CustomerID: 'FRANK'}).joinorder_details.onorders.OrderID=order_details.OrderID.joinproducts.onorder_details.ProductID=products.ProductID.where({ProductName: 'Tofu'}).query;

This line yields the following string in text, but CustomerID and ProductName were converted to SnakeCase implicitly which makes sense because pg recommends snake cased column names but this is not a strict convention AFAIK and it would be good if library doesn't auto convert, instead escape it.

"select * from orders join order_details on (orders.OrderID=order_details.OrderID) join products on (order_details.ProductID=products.ProductID) where (customer_i_d = $1) and (product_name = $2)"

NB: This issue is partially related to #54

const test = sq.from`users`.where({id: 1})
const cte = sq.with({ test }).from`test`.return`id`
console.log(cte.query)

/*
{ text:
   'with test (select * from users where (id = $1)) select id from test',
  args: [ 1 ],
  type: 'select' }
*/

This is not executable by PostgreSQL. adding the AS keyword makes this query work (with test AS (...) select id from test), but that's not what is generated with the sq.with method.

Hello,
I can't seem to make transaction work. I want to get the tx and use it later, because I can't use the callback method. I'm using the following exemple with version 0.0.23

const sq = sqorn({
    connection: {
        host: 'localhost',
        database: 'db_trad',
        user: 'aurelien',
        password: 'postgres',
        port: 5432
    }
});

(async () => {

    const tx = await sq.transaction();
    const res = await sq
        .from`trad.bcas`
        .return`*`
        .insert({ store_id: 'test', product_code: 'abc', quantity: 10, schedule_id: 1 })
        .one(tx);
})();

I get the following error:

UnhandledPromiseRejectionWarning: TypeError: client.query is not a function
at Object.query (/home/aurelien/workspace/sandbox/node_modules/sqorn-pg/src/database.js:26:35)
at Function.one (/home/aurelien/workspace/sandbox/node_modules/sqorn-core/src/index.js:27:33)
at /home/aurelien/workspace/sandbox/pg/sqorn.js:19:10

What am I doing wrong ?

Hello, we would like to feature your project to the Docusaurus users page.

Are you with me on this?

More info about this here

facebook/docusaurus#1422

Now that clauses are made explicit, should we change the filenames as well?

I can submit a PR. Wanted to ask first.

Hello! I have loved using this library. I really would love to get onto the latest version with all of the awesome features you have. Unfortunately, I'm using Typescript and I see that the typings PR you have open has stagnated since December.

Do you have any estimated timeline on when it could be completed?
Is there anything I can do to help?

Example:

const sqorn = require('@sqorn/pg')()
let resp = sqorn.from('mytable').return('id').distinct()
console.log(resp.query)

Output:

{ text: 'select distinct id from mytable, ',
  args: [],
  type: 'select' }

Package version:

"@sqorn/pg": "0.0.45"

I'm not sure if that is possible to use existing connection to Postgres server, couldn't find anything about that in Tutorial as it only mentions how to connect to it.
I want to migrate existing application to using this brilliant library, but without a way to use existing connection it would force me either move all application at once, which is not viable or use multiple pg instances with presumably separate pools of clients.

Is there away to extend the methods that are exposed by score with out having to build the queries manually. We are using postgres extensions that make liberal use variadic functions and custom operators

SELECT *
FROM table
INNER JOIN table on foo where foo.id = table.foo_id
WHERE  table ==> dsl.bool(
  dsl.should(
    dsl.term('field_one', 'abc123')
  , dsl.range('field_two', lt=>100, gte=>50)
  , ...
  )
)

Is anything possible do this or add support for this?
At first glance, it looks like I'd have to build much of it manually, or define an entirely new dialect.

The global monkeypatch in sqorn-pg makes this unusable for us :( we want to slowly migrate to this from node-pg, with our normal snake_case, but the forced camelCase is holding us back.

First of all thank you for this great library !
I would like to be able to build the following kind of query.
I think it isn't possible at the moment or maybe I just could not find how.

        update trad.bcas as t
        set quantity = v.quantity::integer
        from (values
            ($1, $2),
            ($3, $4),
            ($5, $6)
        ) as v(code, quantity)
        where t.store_id = $7
        and t.schedule_id = $8
        and t.product_code = v.code
        returning *

I did it using the following, code

    const bcas = [{
        quantity: 1,
        code: '2310'
    }, {
        quantity: 2,
        code: '2730'
    }, {
        quantity: 3,
        code: '3511'
    }];
    
    let qb = sq.l`
        update trad.bcas
        set quantity = v.quantity::integer
        from (values
    `;

    const last = bcas.length - 1;
    bcas.map((b, index) => {
        qb = qb.l`(${ b.code}, ${ b.quantity })`;
        if (index !== last) qb = qb.l`,`;
    });

    qb = qb.l`) as v(code, quantity)
        where store_id = ${ storeId }
        and schedule_id = ${ scheduleId }
        and product_code = v.code
    `;

is there a better way atm ?
Thanks :)

I have encountered weird case which is probably best explained with example, so this is my test query:

sq.return`t.id`
  .distinct.on`t.id`
  .from`test t`
  .join`foo f`.on`f.tid = t.id`.and`f.type = 2`
  .query

this will throw error TypeError: args is not iterable

If I remove .distinct line, it's working alright. If I remove the .and... it also works.
I presume it has something to do with sqorn using natural joins if you use distinct. If I write the above like sq.return`distinct on (t.id) t.id`... it then uses simple JOIN and returns sql statement without errors.

This can be tested on sqorn demo page.

Hi,
Thank you for creating this great library. I want to suggest the creation of migration and seed files like knex.
What do you think about adding some postgresql functions like create index and drop table and the other functions?

Any database driver for mysql?

Do you really need this import in packages/sqorn-pg/types/methods.d.ts?

Not sure about the correct way to do this. It's what allows:

const children = await sq`person``age < 13`
// and
sq`person``age < 13`.then(children => ... )

the last update was more than 4 months ago. I really wanted to use this package but I can't if it is deprecated

I’m a little put off by the naming convention here. It doesn’t seem very JS-like to me, and it makes it harder to remember the function names, all to save a few characters.

It would make more sense (to me) to have these short versions as optional aliases.

I hope this doesn’t sound too critical and you’ve obviously put a lot of work into this project, but I’m on the lookout for something to replace Knex, and so far the naming convention is a deal-killer to me.

Thanks!