nelsonprsousa
nelsonprsousa
Explore posts from servers
DTDrizzle Team
Created by nelsonprsousa on 3/27/2024 in #help
Sub query with sql magic operator (for common table expression)
Hi friends, I have the following CTE that I can express using the magic sql operator:
const statement = sql`
WITH RECURSIVE location_tree AS
(SELECT *
FROM locations
WHERE id = 1
UNION ALL SELECT l.*
FROM locations l
INNER JOIN location_tree lt ON l.parent_id = lt.id)
SELECT DISTINCT package.*
FROM PACKAGE
INNER JOIN packages_locations ON package.id = packages_locations.package_id
INNER JOIN location_tree ON packages_locations.location_id = location_tree.id;`;
const statement = sql`
WITH RECURSIVE location_tree AS
(SELECT *
FROM locations
WHERE id = 1
UNION ALL SELECT l.*
FROM locations l
INNER JOIN location_tree lt ON l.parent_id = lt.id)
SELECT DISTINCT package.*
FROM PACKAGE
INNER JOIN packages_locations ON package.id = packages_locations.package_id
INNER JOIN location_tree ON packages_locations.location_id = location_tree.id;`;
This statement should return the package table. Now, I'd like to include this expression as a table, in order to be able to join it, something like this:
const result= await db
.select()
.from(packages)
.innerJoin("subQuery (above)", eq("subQuery (above)".packageId, packages.id));
const result= await db
.select()
.from(packages)
.innerJoin("subQuery (above)", eq("subQuery (above)".packageId, packages.id));
Are we able to do it? Maybe with some alias?! Thank you
3 replies
DTDrizzle Team
Created by nelsonprsousa on 3/23/2024 in #help
How to alter database with drizzle (sql magic operator)?
Hi everyone, I am trying to use the sql magic operator to alter a postgres database, but no luck yet. Here's what I am trying to do:
await db.execute(
sql`ALTER DATABASE ${databaseName} SET pg_trgm.similarity_threshold = 0.2;`
);
await db.execute(
sql`ALTER DATABASE ${databaseName} SET pg_trgm.similarity_threshold = 0.2;`
);
However, I am getting PostgresError: syntax error at or near "$1", looks like this operator does not support to use variables when using ALTER DATABASE. Any solution on how to actually perform this operation? It is only running on script (CI/CD), it is not exposed to the public, so security here isn't a huge concern. Thank you!
2 replies
DTDrizzle Team
Created by nelsonprsousa on 2/27/2024 in #help
What is nameIndex?
In the docs we have this example:
export const countries = pgTable('countries', {
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
}, (countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
});
export const countries = pgTable('countries', {
id: serial('id').primaryKey(),
name: varchar('name', { length: 256 }),
}, (countries) => {
return {
nameIndex: uniqueIndex('name_idx').on(countries.name),
}
});
What does nameIndex mean here? Is it a random text or it actually means something? Also, let's say we have another column called chineseName for example. If an app can search using a where statement with the name columns and, on another query, use a where clause with the chineseName, how should we treat indices? Thank you
1 replies
DTDrizzle Team
Created by nelsonprsousa on 2/27/2024 in #help
How to reference the same table in the table's definition?
No description
2 replies
CC#
Created by nelsonprsousa on 11/21/2023 in #help
How to let compiler know that List is not null?
No description
6 replies
DTDrizzle Team
Created by nelsonprsousa on 11/6/2023 in #help
Foreign key truncated for being too long?
Hi, I have multiple warnings / notices: { severity_local: 'NOTICE', severity: 'NOTICE', code: '42622', message: 'identifier "packagesToCategories_package_city_region_country_countryLanguages" will be truncated to "packagesToCategories_package_city_region_country_countryLanguag"', file: 'scansup.c', line: '99', routine: 'truncate_identifier' } Is there a way to name this identifier or silence this warning? Otherwise I am afraid this will start polluting our logs. Thank you
3 replies
DTDrizzle Team
Created by nelsonprsousa on 10/27/2023 in #help
How to filter query based on children table?
No description
6 replies
DTDrizzle Team
Created by nelsonprsousa on 10/25/2023 in #help
How to run migration script?
I am looking into this doc (https://orm.drizzle.team/kit-docs/overview#running-migrations) regarding running migrations and you provide a script that we can call. My question is how can we call this script outside our app. Maybe through the bash/terminal?
8 replies
DTDrizzle Team
Created by nelsonprsousa on 10/24/2023 in #help
How to express "children" or "parent" relation with typescript?
Let's say I have a country and region tables. A region belongs to a country, so on the regiontable, there's a foreign key country_id to the country table. The question is if it is possible to have a Country type generated like this:
type Country {
id: number;
}

type Region {
id: number;
country_id: number;
country: Country; // This one is the difficult/impossible one to get?
}
type Country {
id: number;
}

type Region {
id: number;
country_id: number;
country: Country; // This one is the difficult/impossible one to get?
}
The goal is to be able to query a region and join it with the country table as well. Thank you!
17 replies