jet: ERROR: relation schema.table does not exist (SQLSTATE 42P01)

[aenawi]

Describe the bug I'm using CockroachDB with Postgres driver (pgx), I'm trying to do some CRUDs from mydb with a different schema other than public (the default). The values provided through an API.

The code:

stmt := privateTable.UserAccount.INSERT(
        privateTable.UserAccount.Email,
        privateTable.UserAccount.PasswordHash,
    ).VALUES(
        data.Email,
        data.Password,
    ).RETURNING(
        privateTable.UserAccount.ID,
        privateTable.UserAccount.Email,
    )

The debug statement out of stmt.DebugSql() command looks like this:

INSERT INTO private.user_account (email, password_hash)
VALUES ('user1@local.host', 'password')
RETURNING user_account.id AS "user_account.id",
          user_account.email AS "user_account.email";

I noticed that the database name mydb is not included with the generated code from the Jet CLI!

When I tried to run the code to insert the data, I receive the following error from CockroachDB:

jet: ERROR: relation "private.user_account" does not exist (SQLSTATE 42P01)

and to confirm that it's not my code that's causing this error, I ran the same statement from CockroachDB sql terminal, it displays the same error! But then I tried to solve the issue by adding the database name "mydb" prefix before the schema name and ran it again from CockroachDB Sql terminal, and it worked. Technically, if I'm in the same DB (USE mydb) then it should work without prefixing the db name, but it's a must if you're in other DBs:

INSERT INTO mydb.private.user_account (email, password_hash)
VALUES ('user1@local.host', 'password')
RETURNING user_account.id AS "user_account.id",
          user_account.email AS "user_account.email";

The result:

            user_account.id            | user_account.email 
---------------------------------------+--------------------
  35ecb288-10a3-49d4-bf6f-9976e092f2f7 | user1@local.host 
(1 row)

Time: 5ms total (execution 5ms / network 0ms)

NB: I tried to execute USE mydb sql statement from code using ExecContext(), but that didn't help either!

Environment (please complete the following information):

• OS: MacOS M2
• Database: CockroachDB v22.2.8
• Database driver: pgx v4.18.1
• Jet v2.10.0

Expected behavior One or multiple of the following scenarios:

1. Include the database name before the schema name (maybe the default)
2. Having a method to add/remove it programmatically from the code
3. Have a flag from the Jet CLI to include it with the generated code

[houten11]

Does your database connection string contain a database name?

[aenawi]

This is my connection string (dsn): postgresql://admin@localhost:26257?sslmode=disable Yes; I think I'm missing something! This line hidden somewhere inside a config file, where I didn't check!

I guess adding the dbname comes after the port number, just like Postgres. So, it should be: postgresql://admin@localhost:26257/mydb?sslmode=disable

I need to check this very soon.

Thanks for the hint.

[aenawi]

Yes it worked! The answer was staring at me all the time, but I wasn't looking!

So, this is not considered a bug anymore.

Thanks a lot.