unique constraint does not seem to be applying as intended.
``ts
export const PlayerDiscordCacheTable = pgTable(
tal_player_discord_cache_table`,
{
id: uuid().defaultRandom().primaryKey(),...Using magic SQL operator with 2 SQL dbs
Hi folks,
In my project I need to connect to both separate mysql and postgres databases.
I am able to successfully do so and create database objects but trying to execute raw SQL is not working currently using the magic SQL wrapper syntax.
Typescript is confused because it does not know how to assign the types for sql as it tries to assign them from mysql and pg types...
ExpoSQLite migration immediately fails... How to solve (without deleting migration files)?
I am trying to run my first update migrations after my first DB change (after initial migration to create initial migration from schema)...
But the migration immediatey fails:
```...
Is This SQL Query Efficient? Seeking Advice!
I'm quite beginner to SQL and Drizzle, and I wrote a query, but I have no idea how efficient it is. I don’t even know what to look for. I asked AI, and according to it, my query is inefficient because it contains a subquery that runs for every row in the database:
where: or(
eq(topping_categories.isGlobal, true),
inArray(...
Pkey violates unique constraint after Seed
After i add data to the DB with some data and i try to do an insert, i get the error: duplicate key value violates unique constraint "users_pkey". I can get around it by
but i was wondering if there was something im doing wrong when inserting?
...
await db.execute(sql.raw(
`ALTER SEQUENCE users_id_seq RESTART WITH ${Math.max(...users.map(u => u.id)) + 1}`
));
await db.execute(sql.raw(
`ALTER SEQUENCE users_id_seq RESTART WITH ${Math.max(...users.map(u => u.id)) + 1}`
));
Error: There are multiple relations between "__public__.*" and "*". Please specify relation name
I got this error and originally it was a legitimate error (which i fixed) however drizzle studio is still complaining about it.
The equivalent of connectOrCreate from Prisma
I'm still new to SQL and Drizzle, so I'll try to describe this as best I can.
With Prisma you have the connectOrCreate, if you're trying to create a relation that may or may not exist already. Is there anything equivalent in drizzle? So far I haven't been able to figure it out....
Version required mkcert.
when i run studio i get this error
This version of drizzle-kit requires newer version of drizzle-orm
Please update drizzle-orm package to the latest version 👍
...
Checking whether devs forgot to generate migrations
Hey there!
I'm looking to create a CI test, checking whether the developer has forgotten to generate migrations (it happens sometimes that someone pushes commits updating the drizzle schemas, but they forget to generate the corresponding migrations). My current approach is to run a
drizzle-kit generate from the CI, and see whether some files were generated in the migration folder. It works in most cases, but if the migration generation asks some questions (e.g column renamed), my CI job just skips generating migrations. As a result it considers that no migration was forgotten, which is wrong.
I'm wondering whether there are better approaches I can take? Maybe some Drizzle API allows checking for migrations, without requiring interaction?
Thanks for the great lib, and for any help you can provide!...Strategy to handle wrong migrations locally
How would I go about when I did a mistake in my schema locally when developing and migrated my local database. Do I need to always drop everything and run migrations from the start?..
I would love something where I can just push to the local database after I fixed my latest migration.
e.g.
drizzle-kit regenerate -> Generates the last migration file again containing my fixed schema and then drizzle-kit push but in a manner that it also correctly updates the migration table so I can proceed with additional migrations later on..
...Get all authors with latest book
With the classic library/author/book example, how can i query all authors, each with their latest book? (Assuming some sort of
book.releaseDate column)
The goal is to not load all books from the DB and perform the "latest" filter in the Node.js backend code.
With regular SQL, one possible solution is a subquery.
I've searched for existing solutions that use Drizzle but couldn't find any, so i asked ChatGPT and it went straight to "you gotta use raw SQL for the subquery" 💀...Solution:
Assuming at least one to many authors -> books
```ts
const authorsWithLatestBook = db.query.authors.findMany({...
Timestamp mode as date while drizzle-kit pull
Is it possible to specify the timestamp mode while pull command?
```ts
// I need so
createdAt: timestamp("created_at", {...
Is there a way to get typed results from executing a function on Postgres?
I have a function,
get_widgets that returns rows. The default signature for rows returned is Record<string, unknown>[] Is there a way to specify the row type without having to cast to unknown first?
```
interface Widget {
id: number...Are there any array size limitations for "inArray" filter method?
Are there are any such limits, either in MySQL or DrizzleORM itself?
Uuid always the same with Bun randomUUIDv7() (?)
Hey there I'm working on a sqlite db and have this table:
const session = sqliteTable("session", {
id: integer("id", { mode: "number" }).primaryKey({ autoIncrement: true }),
ipAddress: text("ip_address").notNull(),...
{mode: "json"} sqlite/libs sql does nothing
The types are reflected correctly, but no actual parsing stringifying seems to occur
```
"dependencies": {
"@jitl/quickjs-wasmfile-release-sync": "^0.31.0",...
COALESCE to json array not working in sql function
I am using the sql operator in a postgres db and when I try to coalesece to an empty array it doesn't work as expected. It returns null.
This is how I am doing it. Is it wrong?
``typescript
const productItemsImagesSQLAgg = sql<ProductItemImages[]>
COALESCE(...migrate using Bun SQL api
Hey, rookie here!
i'm testing bun 1.2.1 using drizzle, and I want to use SQL api for migrate schemas, how can I use it in drizzle.config.ts? do I have to create the migrate command?
ps. I'm using it for querys but dont get it in config...
[Solved] Drizzle Studio blocked by CORS, mkcert "no security databases found"
Running Linux Mint 21.3, tried using my daily driver Flatpak ungoogled-chromium as well as ...
apt Firefox (pre-installed on Mint).
# mkcert --install
The local CA is already installed in the system trust store! 👍
ERROR: no Firefox and/or Chrome/Chromium security databases found
# mkcert --install
The local CA is already installed in the system trust store! 👍
ERROR: no Firefox and/or Chrome/Chromium security databases found