First of all, thank you for creating and maintaining this fantastic library! It has been very helpful for working with PostgreSQL in my TypeScript projects.

Currently, OrchidORM handles transactions automatically using the $transaction method, which is a great feature. However, I have a use case where I need more manual control over my transactions, specifically using the BEGIN, COMMIT, and ROLLBACK statements directly.

Here's an example of what I'm trying to achieve:

const connection = await db.$transaction.getConnection();
try {
  await connection.query('BEGIN');
  await db.someTable.where(...conditions).update(...data).transacting(connection);
  await connection.query('COMMIT');
} catch (error) {
  await connection.query('ROLLBACK');
} finally {
  await connection.release();
}

I couldn't find any documentation or examples demonstrating how to perform transactions this way using OrchidORM. Could you please provide some guidance or examples on how to handle transactions manually using the library? If it's not currently supported, would you consider adding this feature in a future release?

Are you considering adding the ability to add error messages to the schema to zod package? That prevents me right now from using it in my GraphQL server as I want much better error messages returned than the default Zod ones, which it doesn't seem like I can do currently. So I end up functionally just repeating the schema in my code, only with custom error messages.

Is there a way to do composite uniques like this from Prisma? Or does that need to get built out?

model Post {
  id         Int        @id @default(autoincrement())
  createdAt  DateTime   @default(now())
  title      String
  published  Boolean    @default(false)
  author     User       @relation(fields: [authorId], references: [id])
  authorId   Int
  categories Category[] @relation(references: [id])

  @@unique([authorId, title])
}

original post with a problem

A simplified query that causes the bug:

 const results = db.accounts
    .select('id')
    .select({
      numPosts: () => db.posts.count(),
    });

  const results2 = await db.$from(results).as("accounts_view").selectAll();

The problem is in multiple select usages, so if we combine the first two select into one or if we remove the ending selectAll the bug disappears.

I'm switching to rake-db from db-migrate and I wonder if there is any kind of migrations that always get executed in each run.

Example usage: when using a custom way of storing an identifier - cuid, to be able to set in in the default method of the id column.

#34 (comment)

See #21 (comment) for more context.

Subqueries for joins are currently not supported.

i.e.

SELECT * FROM table
JOIN ( SELECT * FROM other ) t ON true

on windows here is an example generated definition file after db migrations are run.

import { orchidORM } from 'orchid-orm';
import { Expansion } from './definitons\tables\expansion.table';
import { Region } from './definitons\tables\region.table';
import { Realm } from './definitons\tables\realm.table';
import { Ingame_icon } from './definitons\tables\ingame_icon.table';
import { Achievement } from './definitons\tables\achievement.table';

export const db = orchidORM(
  {
    databaseURL: 'postgres://postgres:postgres@localhost:5432/wowtrackdev',
    ssl: false,
  },
  {
    expansion: Expansion,
    region: Region,
    realm: Realm,
    ingame_icon: Ingame_icon,
    achievement: Achievement,
  }
);

I'm not sure if path names are still okay but at first glance '\' may cause some headaches?

on mobile phone the website does need some tweaks

First of all, very excited for this ORM and set of libraries. I saw your post on reddit and am very much looking forward to tinkering with this.

Would you consider making the @orchid organization on npm and publishing your packages under @orchid/pqb or @orchid/orm etc? This way it's clear where they are coming from.

Totally just a suggestion, but I think the branding could do well 😄

I could fix this by just adding multiple primary keys individually, but it seems like ...t.primaryKey(['keyOne', 'keyTwo']) composite primaryKey is now broken as of one of the recent updates.

I upgraded my rake and orchid to the latest version and now my $transaction isn't working, it is just getting the db as type any.

Inspired by prisma generator feature. I find it very useful for generating json/types/schemas/validators/trpc/etc. The idea is to have a plugin type function that has access to the underlying orm schema/types with the ability to automatically generate and output code based on that.

Use cases include generating custom CRUD types, various API schema/routes patterns (trpc, openAPI, graphQL), non-zod validators, running complex multi-schema migrations, etc

I guess the main challenge is the entry point fur such function. Prisma includes/requires a convenient generation step for that purpose. In the case of orchid-orm this could be part of the connection configuration and can be conditionally (necessary for production builds) triggered during initialization. The user than installs the package which includes predifined initialization function accepting the schema and handles the rest. Bonus points for compatibility with Prisma generators (not sure how feasible that is).

In terms of syntax it could look like something like this:

orchidORM({
  // databaseURL has the following format:
  // postgres://user:password@localhost:5432/dbname
  // 
  // ssl option can be specified as a parameter:
  // postgres://user:password@localhost:5432/dbname?ssl=true
  databaseURL: process.env.DATABASE_URL,

  // ssl can also be specified as an option:
  ssl: true,
  generators: [
    {
       name: 'orchid-orm-trpc-generator',
       config: { ... },
       enabled: process.env.MODE === 'development',
    }
  ]
  
  log: true, // option for logging, false by default
  autoPreparedStatements: true, // see in query builder setup docs, false by default
  noPrimaryKey: 'ignore', // see in query builder setup docs, 'error' by default
}, {
  user: UserTable,
  message: MessageTable,
})

PS: Let me know, if I am missing something. I have a couple of more features in mind that I shared on reddit. I can follow the above template to create issues for them. OrchidORM looks really promising, I would love to also help with the project, so I will stay in touch once I free up my schedule a bit.

Take this example from prisma

const users = await prisma.user.findMany({
  where: {
    posts: {
      none: {
        views: {
          gt: 100,
        },
      },
      every: {
        likes: {
          lte: 50,
        },
      },
    },
  },
})

How can I do the equivalent? Where does not seem to auto be aware of relations like this.

I'm very grateful to all of you who have left inspiring comments, who have starred the project, and who are looking forward to its growth!

For me this means a lot, it is a credit of trust. And I'm absolutely set up for improving this project in the foreseeable future.

Feel free to leave any questions or suggestions, or point out unclean parts of the docs, - community feedback is in priority for me.

Thank you all for the motivation, hoping to build a small cozy community here!

The link to getCurrentUserId is not included in the docs.

i want to seed my static data within my migrations and only example is here with raw sql: https://orchid-orm.netlify.app/guide/migration-writing.html#writing-a-migration

is there a way to use orchid-orm to do so within migrations?

i was able to seed my data using

    for (const entry of regions) {
      await db.query({
        text: "INSERT INTO region (id, code, name) VALUES ($1, $2, $3)",
        values: [entry.id, entry.code, entry.name],
      });
    }

but i'm looking for a way to push all records once.

Hi Romeerez,

I tried going through the docs to build a demo application for your ORM which I found interesting, but when trying to create the database and initialize the schema it keeps throwing errors since Neon (the platform where I'm hosting the Postgres instance) requires ssl. When enabling SSL in die config or even on the db string itself I get infinitely prompted with the following image:

Any idea why this might be?
If I remove SSL then I get the following:

Suppose I have the following base query:

const accounts = db.accounts
    .leftJoin("cover")
    .selectAll()
    .select({
        cover: q => q.cover.selectAll(),
        num_posts: q => q.account_posts.count().whereNotExists(db.commentsOnPosts, "comment_id", "=", "id"),
        num_comments: q => q.account_posts.count().whereExists(db.commentsOnPosts, "comment_id", "=", "id"),
        num_posts_liked: q => q.account_likes.count().whereNotExists(db.commentsOnPosts, "comment_id", "=", "post_id"),
        num_comments_liked: q => q.account_likes.count().whereExists(db.commentsOnPosts, "comment_id", "=", "post_id"),
    })
    .limit(10);

I am attempting to represent the following query:

select * from (${accounts}) where num_posts_liked > 0

My attempt at building this query is as so, though is incorrect:

db.accounts.from(accounts).selectAll().where({ num_posts_liked: { gt: 0 } });

Is it possible to have all code interacting with the database work with camel-cased column keys, while the database has all table columns be snake-cased?

As I already mentioned in my PR, I think this is a fantastic project and I'd really like to see it gaining traction.
I believe there are a few (code-unrelated) things you should do to the GitHub repo that might help:

• Update the README:
  • delete the default TSDX stuff
  • include a link to the docs website (which is really awesome, by the way)
  • consider adding badges (i.e. from shields.io)
• Update your About section text in the right, add some topics (such as Postgres, orm, etc.) and make sure to include the website link here as well
• In your repo Settings you'll find a way to upload an image to customize your repository’s social media preview, your logo looks cool, so you could add it there.

I know the above sounds like a lot of hassle, but people need to know about your work, and you'll get a lot more attention if you tick a few boxes.

Have a look at my Mantine DataTable for inspiration: https://github.com/icflorescu/mantine-datatable

When running the db create command it responds saying the database is created and the version table is created (the base table), but the rest of the tables were not created. Am I missing something, because it just used the base generator which should set up the project for me correctly.

Any guidance would be appreciated.

When running db pull on a project which uses citext I got this:

$ ts-node src/db/dbScripts.ts pull
/home/capaj/work-repos/official/official-graphql2/graphql/node_modules/rake-db/src/pull/structureToAst.ts:259
       throw new Error(
             ^
Error: Cannot handle column official.email_prefs.email: column type `citext` is not supported
   at pushTableAst (/home/capaj/work-repos/official/official-graphql2/graphql/node_modules/rake-db/src/pull/structureToAst.ts:259:15)
   at structureToAst (/home/capaj/work-repos/official/official-graphql2/graphql/node_modules/rake-db/src/pull/structureToAst.ts:92:20)
   at processTicksAndRejections (node:internal/process/task_queues:95:5)
   at async pullDbStructure (/home/capaj/work-repos/official/official-graphql2/graphql/node_modules/rake-db/src/pull/pull.ts:15:15)
   at async rakeDb (/home/capaj/work-repos/official/official-graphql2/graphql/node_modules/rake-db/src/rakeDb.ts:33:7)
error Command failed with exit code 1.

Hi @romeerez,

Thanks for orchid-orm - I'm keeping a close eye on it as I agree with your assessment of other popular ORMs. One of my concerns with prisma is that transaction management seems very much an after thought, which is worrying given it's a database library. I like how you've incorporated transactions into the fluent API but, depending on how the application's code is organised this will require passing the transaction variable to function calls.

Sequelise solves this quite neatly using cls or async local storage. I wondered if you have plans to do the same?

I have a second question, which is how you specify different isolation levels. It doesn't appear possible with the current API.

Hi, found and trying this orm recently and loving it! I wonder is there a way to setup cascade delete for relationship for now?
Thanks!

Unsure if this is possible with pqb as-is, though is it possible to cast i.e. Table.count() strictly to an integer/numeric type?

From here: #58

I have a firstName and lastName column in table, I want to calculate a fullName property on the fly.

Computed column should support the following things:

Selecting raw SQL:

class Table extends BaseTable {
  columns = this.setColumns((t) => ({
    firstName: t.text(),
    lastName: t.text(),
    // combine first and last name on db side, it should be more efficient than doing so on JS side.
    fullName: t.text().computed(t.raw(`"firstName" || ' ' || "lastName"`)),
  }));
}

Selecting dynamic raw SQL, what if we want the field to be selected differently based on some variable? Imagine we have a multi-language web site and we want to fetch a product name, db is storing "name_en", "name_uk", "name_fr" and others, and we could define a column name to load a translated column:

// somewhere in the app:
const asyncLocalStorage = new AsyncLocalStorage();

(req, res) => {
  const result = await asyncLocalStorage.run(req.params.locale, () => {
    return db.product.select('name') // select name
  })
}

name: t.text().computed(() => {
  const locale = asyncLocalStorage.getStore();
  return t.raw(`name_${locale}`) // name reveals to name_*locale*
})

Would be nice to be able to have a full query builder to define queries, rather than just a raw SQL. But I think it's not possible for this case, so let it be just raw SQL, anyway computed columns aren't meant to be based on complex queries.

Columns that are calculated on JS side are more complex to design, need to think about it. Because:

• such columns depend on other columns, when you're selecting fullName it should ensure that firstName and lastName were selected from db
• it cannot be defined safely inside setColumns callback because at this point the columns aren't defined, and the callback cannot know the type of firstName and lastName, the callback cannot even know if such columns exist on this table.

Here is a draft for how the methods could be defined on the table.

file:///Users/dew/node_modules/orchid-core/src/utils.ts:125
  return `'${s.replaceAll('\\', '\\\\').replaceAll("'", "\\'")}'`;
               ^
TypeError: Cannot read properties of undefined (reading 'replaceAll')
    at singleQuote (file:///Users/dew/node_modules/orchid-core/src/utils.ts:125:16)
    at foreignKeyArgsToCode (file:///Users/dew/node_modules/pqb/src/columns/code.ts:224:9)
    at foreignKeyToCode (file:///Users/dew/node_modules/pqb/src/columns/code.ts:197:34)
    at createTable (file:///Users/dew/node_modules/rake-db/src/pull/astToMigration.ts:188:15)
    at astToMigration (file:///Users/dew/node_modules/rake-db/src/pull/astToMigration.ts:41:19)
    at pullDbStructure (file:///Users/dew/node_modules/rake-db/src/pull/pull.ts:25:18)

Adding a console.log(foreignKey) at foreignKeyArgsToCode yields the following output:

{
  columns: [ 'avatar_storage_id', 'avatar_etag' ],
  fnOrTable: 'etags',
  foreignColumns: [ 'storage_id', 'id' ],
  options: {
    name: undefined,
    match: undefined,
    onUpdate: 'CASCADE',
    onDelete: 'SET NULL'
  }
}

I believe the error is because options.name and options.match are undefined. Let me know if you need anymore info.

Schema:

CREATE TABLE public.accounts (
    id text NOT NULL,
    public_key text NOT NULL,
    name text DEFAULT ''::text NOT NULL,
    alias text,
    bio text DEFAULT ''::text NOT NULL,
    avatar_storage_id public."Storage",
    avatar_etag text,
    created_at timestamp(3) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp(3) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_seen_at timestamp(3) without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    is_verified boolean DEFAULT false NOT NULL
);

CREATE TABLE public.etags (
    storage_id public."Storage" NOT NULL,
    id text NOT NULL,
    type text NOT NULL,
    num_posts_attached integer DEFAULT 0 NOT NULL,
    num_avatars_attached integer DEFAULT 0 NOT NULL,
    num_pass_collection_avatars_attached integer DEFAULT 0 NOT NULL,
    num_covers_attached integer DEFAULT 0 NOT NULL,
    static boolean DEFAULT false NOT NULL
);

ALTER TABLE ONLY public.accounts
    ADD CONSTRAINT accounts_avatar_storage_id_avatar_etag_fkey FOREIGN KEY (avatar_storage_id, avatar_etag) REFERENCES public.etags(storage_id, id) ON UPDATE CASCADE ON DELETE SET NULL;

CREATE TYPE public."Storage" AS ENUM (
    'r2'
);

In the doc example for default, you have

date: t.date().default(t.raw('now()')),

But I'm not seeing raw on t.

import { createBaseTable, orchidORM } from "orchid-orm";

export const BaseTable = createBaseTable({
    columnTypes: t => ({
        ...t,
        text: (min = 0, max = 1024) => t.text(min, max),
    })
});

export class AccountsTable extends BaseTable {
    table = "accounts";

    columns = this.setColumns(t => ({
        id: t.text().primaryKey(),
        public_key: t.text(),
        name: t.text(),
        alias: t.text().nullable(),
        bio: t.text(),
        avatar_storage_id: t.text().nullable(),
        avatar_etag: t.text().nullable(),
        created_at: t.timestamp(),
        updated_at: t.timestamp(),
        last_seen_at: t.timestamp(),
        is_verified: t.boolean(),
    }));

    relations = {
        cover: this.hasOne(() => AccountCoversTable, {
            primaryKey: "id",
            foreignKey: "account_id",
        }),
        oauths: this.hasMany(() => AccountOauthsTable, {
            primaryKey: "id",
            foreignKey: "account_id",
        }),
        account_posts: this.hasMany(() => PostsTable, {
            primaryKey: "id",
            foreignKey: "author_id",
        }),
        account_likes: this.hasMany(() => LikesOnPostsTable, {
            primaryKey: "id",
            foreignKey: "liker_id",
        }),
    };
}

export class AccountCoversTable extends BaseTable {
    table = "account_covers";

    columns = this.setColumns(t => ({
        account_id: t.text().foreignKey("accounts", "id").primaryKey(),
        storage_id: t.text(),
        etag_id: t.text(),
        created_at: t.timestamp(),
        updated_at: t.timestamp(),
    }));
}

export class AccountOauthsTable extends BaseTable {
    table = "account_oauths";

    columns = this.setColumns(t => ({
        account_id: t.text().foreignKey("accounts", "id").primaryKey(),
        provider_id: t.enum("oauth_provider_type", ["instagram", "twitter"]),
        user_id: t.text(),
        username: t.text(),
    }));
}

export class PostsTable extends BaseTable {
    table = "posts";

    columns = this.setColumns(t => ({
        id: t.text().primaryKey(),
        author_id: t.text().foreignKey("accounts", "id"),
        content: t.text(),
        created_at: t.timestamp(),
        updated_at: t.timestamp(),
    }));

    relations = {
        comments: this.hasMany(() => CommentsOnPostsTable, {
            primaryKey: "id",
            foreignKey: "post_id",
        }),
        likes: this.hasMany(() => LikesOnPostsTable, {
            primaryKey: "id",
            foreignKey: "post_id",
        }),
    };
}

export class CommentsOnPostsTable extends BaseTable {
    table = "comments_on_posts";

    columns = this.setColumns(t => ({
        post_id: t.text().foreignKey("posts", "id").primaryKey(),
        comment_id: t.text().foreignKey("comments", "id"),
    }));
}

export class LikesOnPostsTable extends BaseTable {
    table = "likes_on_posts";

    columns = this.setColumns(t => ({
        liker_id: t.text().foreignKey("accounts", "id").primaryKey(),
        post_id: t.text().foreignKey("posts", "id").primaryKey(),
        created_at: t.timestamp(),
    }));
}

export const db = orchidORM({ databaseURL: process.env["DATABASE_URL"], autoPreparedStatements: true, log: true }, {
    accounts: AccountsTable,
    accountCovers: AccountCoversTable,
    accountOauths: AccountOauthsTable,
    posts: PostsTable,
    commentsOnPosts: CommentsOnPostsTable,
    LikesOnPosts: LikesOnPostsTable,
});

const results = db.accounts
    .leftJoin("cover")
    .selectAll()
    .select({
        cover: q => q.cover.selectAll(),
        num_posts: q => q.account_posts.count().whereNotExists(db.commentsOnPosts, "comment_id", "=", "id"),
        num_comments: q => q.account_posts.count().whereExists(db.commentsOnPosts, "comment_id", "=", "id"),
        num_posts_liked: q => q.account_likes.count().whereNotExists(db.commentsOnPosts, "comment_id", "=", "post_id"),
        num_comments_liked: q => q.account_likes.count().whereExists(db.commentsOnPosts, "comment_id", "=", "post_id"),
    })
    .limit(10);

console.log(await results);

Calling await at the end yields an arbitrary error:

% node --no-warnings --loader ts-node/esm src/db.orchid.ts
(39.7ms) SELECT "accounts".*, (SELECT row_to_json("t".*) FROM (SELECT * FROM "account_covers" AS "cover" WHERE "cover"."account_id" = "accounts"."id" LIMIT $1) AS "t") AS "cover", (SELECT count(*) FROM "posts" AS "account_posts" WHERE NOT EXISTS (SELECT 1 FROM "comments_on_posts" WHERE "comments_on_posts"."comment_id" = "account_posts"."id" LIMIT 1) AND "account_posts"."author_id" = "accounts"."id") AS "num_posts", (SELECT count(*) FROM "posts" AS "account_posts" WHERE EXISTS (SELECT 1 FROM "comments_on_posts" WHERE "comments_on_posts"."comment_id" = "account_posts"."id" LIMIT 1) AND "account_posts"."author_id" = "accounts"."id") AS "num_comments", (SELECT count(*) FROM "likes_on_posts" AS "account_likes" WHERE NOT EXISTS (SELECT 1 FROM "comments_on_posts" WHERE "comments_on_posts"."comment_id" = "account_likes"."post_id" LIMIT 1) AND "account_likes"."liker_id" = "accounts"."id") AS "num_posts_liked", (SELECT count(*) FROM "likes_on_posts" AS "account_likes" WHERE EXISTS (SELECT 1 FROM "comments_on_posts" WHERE "comments_on_posts"."comment_id" = "account_likes"."post_id" LIMIT 1) AND "account_likes"."liker_id" = "accounts"."id") AS "num_comments_liked" FROM "accounts" LEFT JOIN "account_covers" AS "cover" ON "cover"."account_id" = "accounts"."id" LIMIT $2 [1, 10]
file:///Users/dew/node_modules/pqb/src/queryMethods/then.ts:41
    temporaryError = new Error();
                     ^
Error
    at Db.get then [as then] (file:///Users/dew/node_modules/pqb/src/queryMethods/then.ts:41:22)
    at file:///Users/dew/src/db.orchid.ts:139:19
    at ModuleJob.run (node:internal/modules/esm/module_job:193:25)
    at async Promise.all (index 0)
    at async ESMLoader.import (node:internal/modules/esm/loader:530:24)
    at async loadESM (node:internal/process/esm_loader:91:5)
    at async handleMainPromise (node:internal/modules/run_main:65:12)

The query alone however is built correctly and works should I manually execute it in my PostgreSQL database.

When running pnpm run check under packages/core, there seems to have an error

Hi and first off thanks and props for this project, the docs are more in depth than a lot i've seen in a while 👍

I wanted to get a basic experiment running by generating a project with the quick start guide and after a clean run of db pull imported the db. No matter if i ran a basic query or just tried to log the db object to console, i ran into the following error:

> [email protected] dev C:\Users\Leif\dev\orchid-test\server
> ts-node src/index.ts

C:\Users\Leif\dev\orchid-test\node_modules\.pnpm\[email protected][email protected]\node_modules\orchid-orm\src\orm.ts:74
    const table = new tables[key]();
                  ^
TypeError: tables[key] is not a constructor
    at orchidORM (C:\Users\Leif\dev\orchid-test\node_modules\.pnpm\[email protected][email protected]\node_modules\orchid-orm\src\orm.ts:74:19)
    at Object.<anonymous> (C:\Users\Leif\dev\orchid-test\server\src\db\db.ts:52:28)
    at Module._compile (node:internal/modules/cjs/loader:1275:14)
    at Module.m._compile (C:\Users\Leif\dev\orchid-test\node_modules\.pnpm\[email protected]_@[email protected]_@[email protected][email protected]\node_modules\ts-node\src\ind
ex.ts:1618:23)
    at Module._extensions..js (node:internal/modules/cjs/loader:1329:10)
    at Object.require.extensions.<computed> [as .ts] (C:\Users\Leif\dev\orchid-test\node_modules\.pnpm\[email protected]_@[email protected]_@[email protected][email protected]\
node_modules\ts-node\src\index.ts:1621:12)
    at Module.load (node:internal/modules/cjs/loader:1133:32)
    at Function.Module._load (node:internal/modules/cjs/loader:972:12)
    at Module.require (node:internal/modules/cjs/loader:1157:19)
    at require (node:internal/modules/helpers:119:18)

The project is using pnpm workspaces, but i tried installing with npm directly in the folder and received the same error message.

Should you need the code to debug i'll see that i reduce it to a minimum since it's cluttered with docker stuff at the moment.

export class test extends BaseTable {
  columns = this.setColumns((t) => ({
    id: t.cuid(),
    time: t.timestamp(),
    ...t.timestamps(),
  }));
}

db.test.find('xxx').update({
  updatedAt: new Date(),  // type error
  test: new Date(),  // type error
});

Unless I set the column asDate() explicitly, then I can update it with Date object, I don't know if it's a bug or design choice.

I think it maybe a bug because I see the same type error in a test file:

Hi, thanks for creating this library!

I can't seem to follow the quickstart from the documentation. When trying to use npx orchid-orm@latest, I get this error:

root@38bf68c725c0:/# npx orchid-orm@latest
Need to install the following packages:
  orchid-orm@latest
Ok to proceed? (y) y
sh: 1: orchid-orm: not found

I get this error both on my Arch Linux machine and in a minimal Ubuntu image (see Dockerfile below):

FROM ubuntu:22.04

RUN apt-get update
RUN apt-get install -y npm --no-install-recommends

WORKDIR /root

CMD ["/bin/bash"]

I'm trying to use rake-db and orchid-rom within my esm module but having issues as it's currently a commonjs module.

any chances we can get esm support?

import { rakeDb } from "rake-db";
         ^^^^^^
SyntaxError: Named export 'rakeDb' not found. The requested module 'rake-db' is a CommonJS module, which may not support all module.exports as named exports.
CommonJS modules can always be imported via the default export, for example using:

import pkg from 'rake-db';
const { rakeDb } = pkg;

once i use the method above:

import rakedb_pkg from "rake-db";
const { rakeDb } = rakedb_pkg;

getting

(node:11244) Warning: To load an ES module, set "type": "module" in the package.json or use the .mjs extension.
(Use `node --trace-warnings ...` to show where the warning was created)
C:\Coding\wowtrack.gg\migrator2\node_modules\rake-db\dist\index.esm.js:1
import { singleQuote, quote, isRaw, getRaw, toArray, columnTypes, raw, getColumnTypes, getTableData, resetTableData, ColumnType, emptyObject, TransactionAdapter, logParamToLogObject, Adapter, columnsByType, instantiateColumn, codeToString, addCode, quoteObjectKey, primaryKeyToCode, indexToCode, foreignKeyToCode, TimestampColumn } from 'pqb';
^^^^^^

SyntaxError: Cannot use import statement outside a module
    at Object.compileFunction (node:vm:360:18)
    at wrapSafe (node:internal/modules/cjs/loader:1048:15)
    at Module._compile (node:internal/modules/cjs/loader:1083:27)
    at Module._extensions..js (node:internal/modules/cjs/loader:1173:10)
    at Module.load (node:internal/modules/cjs/loader:997:32)
    at Module._load (node:internal/modules/cjs/loader:838:12)
    at ModuleWrap.<anonymous> (node:internal/modules/esm/translators:170:29)
    at ModuleJob.run (node:internal/modules/esm/module_job:193:25)
    at async Promise.all (index 0)
    at async ESMLoader.import (node:internal/modules/esm/loader:527:24)

also tried

import rakedb_package from "rake-db";

rakedb_package.rakeDb(
  {
    databaseURL,
    ssl: true,
  },
  {
    migrationsPath: "migrations",
  }
);

but same error

(node:2632) Warning: To load an ES module, set "type": "module" in the package.json or use the .mjs extension.
C:\Coding\wowtrack.gg\migrator2\node_modules\rake-db\dist\index.esm.js:1
import { singleQuote, quote, isRaw, getRaw, toArray, columnTypes, raw, getColumnTypes, getTableData, resetTableData, ColumnType, emptyObject, TransactionAdapter, logParamToLogObject, Adapter, columnsByType, instantiateColumn, codeToString, addCode, quoteObjectKey, primaryKeyToCode, indexToCode, foreignKeyToCode, TimestampColumn } from 'pqb';
^^^^^^

SyntaxError: Cannot use import statement outside a module
    at Object.compileFunction (node:vm:360:18)
    at wrapSafe (node:internal/modules/cjs/loader:1048:15)
    at Module._compile (node:internal/modules/cjs/loader:1083:27)
    at Module._extensions..js (node:internal/modules/cjs/loader:1173:10)
    at Module.load (node:internal/modules/cjs/loader:997:32)
    at Module._load (node:internal/modules/cjs/loader:838:12)
    at ModuleWrap.<anonymous> (node:internal/modules/esm/translators:170:29)
    at ModuleJob.run (node:internal/modules/esm/module_job:193:25)
    at async Promise.all (index 0)
    at async ESMLoader.import (node:internal/modules/esm/loader:527:24)

Node.js v18.9.0

Hi @romeerez,

I'm curious about Orchid's ON CONFLICT semantics, e.g.

Table.create(data).onConfict('email')

PostgreSQL allows a conflict target to be specified so you can handle conflicts differently depending on which constraint is violated. Is this supported by orchid?

Hello I am just trying this project out and wanted to test it doing a Todo app with SvelteKit, but when trying to run the migrations, I don't get any output in the terminal, is there a way to have like a DEBUG mode to see what it is doing, and why there is not any output, I am pretty sure I am doing something wrong but with no error message or output it is very hard to find out what. Creating the database worked fine and dropping it as well. Is it supposed to work in SvelteKit?

Hello again :)

i'm currently messing around with a koa server and the generated Zod schemas and ran into a problem that i think might come up for other people too:

I am building a route to query filtered results from a table by the means of the requests body, which then is getting a type by using z.infer on the schema. When encountering a column that consists of timestamp the body values are Date objects, while the parameter for findBy requires a string.

Since i'm primarily a frontend dev i can't tell if this goes against best practices, but my instinct tells me not having to serialize / deserialize data for validation and using the db as a single source of truth might be something useful.

Happy to hear thoughts on it :)

If I had a schema where I had a post and I wanted to check if the title of the post equals some text string, or the content of a comment had some specific text, how would I go about doing that? This is not my actual use case and it's a bit of a convoluted example, but basically I want to also check a where on a connection, but the connection does not appear as an option in the query.

Here's a basic example of this kind of code in MikroORM for comparison

      $or: [
               { title: { $like: likeText } },
               { description: { $like: likeText } },
               { comments: { $or: [{ title: { $like: likeText } }, { content: { $like: likeText } }] } },
            ],

And I want to do the equivalent. However, the "comments" part does not show up and I'm trying to figure out how. The closest thing I've seen in the documentation is only in regards to select, which does do this same thing.

I'm using a cuid as the id instead of a serial or uuid, so I call this method createId() from @paralleldrive/cuid2, and if I add that line, I am then unable to add a connection as just the id, I have to call a connect on the relation, for example

await db.state.create({
      ...state,
      id: createId(),
      country: { connect: { id: createdCountry.id } },
    })

as opposed to just setting countryId: createdCountry.id

However, if I remove the id field, I can do this (it does not work as id is null, but typescript doesn't freak out).

Is this normal, expected behavior? And if so, why?

The new syntax for addTo on rake-db doesn't seem to be working properly. I have not yet tried other commands, but I tried every variation of addTo[Table], addTo [Table], etc., and it fills inchangeTable(tableName). I mean literally the string tableName, not the name of the table ie changeTable('user'), so it seems like it's not getting the arg correctly from the command line. If I'm doing it wrong... then the wording is very confusing and needs to be made more clear. Either way I would use [] as args in the text on the command line and not *, which seems to be much more normal cli convention to me.

With a database where table names and column names are snake-cased, and snakeCase: true, in .where conditions, table names seem to only be accepted should they be specified as snake-cased whereas column names may be either camel-cased or snake-cased.

The following code throws error: missing FROM-clause entry for table "commentsOnPosts":

.select({
    numPosts: q => db.posts.count().whereNotExists(db.commentsOnPosts, "commentsOnPosts.commentId", "posts.id").where(q.raw("posts.author_id = accounts.id")),
})

The following code however works:

.select({
    numPosts: q => db.posts.count().whereNotExists(db.commentsOnPosts, "comments_on_posts.commentId", "posts.id").where(q.raw("posts.author_id = accounts.id")),
})

Possibly dumb question, but why am I getting connections like books in this example:

const authorWithBooks: AuthorResult = await db.author.select(
  'id',
  'name',
  {
    books: (q) => q.books
      .select('id', 'title')
      .where(...conditions)
      .order('title')
      .limit(5),
  }
).take()

being returned as a nested array? I've tried it several different ways and they all end up returning books: [Array[]], which is obviously not what I'm looking for, I'm looking for books: Array[]. I then would have to go in and flatten books, which is inconvenient, and the documentation makes no mention of this, and even implies it should return as the latter.

If trying to do it this way (which btw this documentation is also wrong)

// if the `required` option is not set in the table class,
// type of author will be Author | null 
const booksWithAuthor: Book & { author: Author } = await db.book
  .select('*', 'author')
  .take();

const authorWithBooks: Author & { books: Book[] } = await db.book
  .select('*', 'author')
  .take();

I get the same thing, so I'm wondering why this is happening?

After running db pull with snakeCase: true, I noticed that the table class names were not PascalCase'd. Is this intended?

From the generated db.ts:

import { Account_coversTable } from '../tables/account_covers.table';
import { Account_oauthsTable } from '../tables/account_oauths.table';

Use case: user already has db views, no need to create them or manage them, only to be able to query them.

I imagine it to be defined in a similar way as tables are, but to exclude methods for updating/creating/deleting.

So is it just me or were there breaking changes on migrations that aren't actually built out yet and make no reference in the docs?

timestamps does this

createdAt: t.timestamp().default('now()', undefined),
    updatedAt: t
      .timestamp()
      .default('now()', undefined)
      .modifyQuery((q) => {
        orchidCore.pushOrNewArrayToObject(q.query, 'updateData', updatedAtInjector)
      }),

There's no mention of orchid core anywhere in the docs, I see it's a new package, but it's not like it's imported automatically on a migration, and when I did try to, 'updateData' wasn't even an option. They also throw an error on undefined, as it's expecting one arg, as well as 'now()' because it's expecting orchidCore.raw('now()') so there's a lot going on here haha

When following the building a sample app, I noticed that there is a missing link to routeHandler

I would love to open a pr fixing it 🙂

In the Quickstart documentation page, the instructions assume that the reader has orchid-orm installed globally - as such the installation step is missing:

npm i orchid-orm # or yarn|pnpm add orchid-orm

It may be obvious for seasoned developers, but may present a problem for newbies.