Meexa Posts - Answer Overflow

Meexa

Explore posts from servers

RRailway TTCTheo's Typesafe Cult HHono DTDrizzle Team

•Created by Meexa on 2/28/2024 in #help

What should the driver be in drizzle.config.ts when using bun sqlite?

I get an error when I leave out driver in my drizzle config

Either "turso", "libsql", "better-sqlite" are available options for "--driver"

Either "turso", "libsql", "better-sqlite" are available options for "--driver"

I tried better-sqlite but then it asks me to install better-sqlite3

1 replies

DTDrizzle Team

•Created by Meexa on 1/9/2024 in #help

Error: Cannot open database because the directory does not exist

This is my config (in root)

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  driver: 'better-sqlite',
  dbCredentials: {
    url: 'file:/Users/meexa/Documents/personal/my-app/sqlite.db',
  },
  verbose: true,
  strict: true,
  out: 'drizzle',
});

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  driver: 'better-sqlite',
  dbCredentials: {
    url: 'file:/Users/meexa/Documents/personal/my-app/sqlite.db',
  },
  verbose: true,
  strict: true,
  out: 'drizzle',
});

My client file (/src/db/index.ts)

import { drizzle } from 'drizzle-orm/bun-sqlite';
import Database from 'better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import { users } from './schema';

const sqlite = new Database('file:/Users/meexa/Documents/personal/my-app/sqlite.db');
export const db = drizzle(sqlite);

migrate(db, { migrationsFolder: 'drizzle' });

const result = await db.select().from(users);

import { drizzle } from 'drizzle-orm/bun-sqlite';
import Database from 'better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import { users } from './schema';

const sqlite = new Database('file:/Users/meexa/Documents/personal/my-app/sqlite.db');
export const db = drizzle(sqlite);

migrate(db, { migrationsFolder: 'drizzle' });

const result = await db.select().from(users);

sqlite.db is in the root of my project

4 replies

DTDrizzle Team

•Created by Meexa on 12/7/2023 in #help

How to do "onConflictDoUpdate" when inserting an array of values?

I want to add multiple rows to my table but it has a unique key, so it has to update the row if there is a conflict. How do I do this with the ORM?

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      // What do I put in here? This is outside of the `map` context
    },
  });

await db
  .insert(items)
  .values(
    chunk.map((item) => ({
      auctionHouseId: Number(auctionHouseId),
      itemId: item.itemId,
      // ...
    })),
  )
  .onConflictDoUpdate({
    target: [items.itemId, items.auctionHouseId],
    set: {
      // What do I put in here? This is outside of the `map` context
    },
  });

16 replies

DTDrizzle Team

•Created by Meexa on 11/21/2023 in #help

sqlite insert results in error: "SQLite error: no such table: main.__old_push_items"

2 of my 3 table insert work fine, but my auctions table inserts do not work for some reason. When I try to insert data into my sqlite db on Turso I get the following error:

LibsqlError: SQLITE_UNKNOWN: SQLite error: no such table: main.__old_push_items

LibsqlError: SQLITE_UNKNOWN: SQLite error: no such table: main.__old_push_items

Versions

{
  "drizzle-kit": "^0.20.4",
  "drizzle-orm": "^0.29.0"
}

{
  "drizzle-kit": "^0.20.4",
  "drizzle-orm": "^0.29.0"
}

Insert code uses Zod to validate, which it passes succesfully

const newscanmeta = await db
  .insert(scanmeta)
  .values({
    realm: entry.realm,
    faction: entry.faction as 'Alliance' | 'Horde' | 'Neutral',
    scanner: entry.char,
    timestamp: new Date(entry.ts * 1000).toISOString(),
  })
  .onConflictDoUpdate({
    target: [scanmeta.timestamp, scanmeta.scanner],
    set: {
      scanner: entry.char,
      timestamp: new Date(entry.ts * 1000).toISOString(),
    },
  })
  .returning({ id: scanmeta.id });

const scanID = newscanmeta[0]?.id;

const newAuction = insertAuctionsSchema.safeParse({
  scanId: scanID,
  itemId: item,
  timestamp: new Date(scan.ts * 1000).toISOString(),
  seller,
  timeLeft: a.TimeLeft,
  itemCount: a.ItemCount,
  minBid: a.MinBid,
  buyout: a.Buyout,
  curBid: a.CurBid,
});
if (!newAuction.success) {
  console.error(fromZodError(newAuction.error));
  continue;
}

await db.insert(auctions).values(newAuction.data);

const newscanmeta = await db
  .insert(scanmeta)
  .values({
    realm: entry.realm,
    faction: entry.faction as 'Alliance' | 'Horde' | 'Neutral',
    scanner: entry.char,
    timestamp: new Date(entry.ts * 1000).toISOString(),
  })
  .onConflictDoUpdate({
    target: [scanmeta.timestamp, scanmeta.scanner],
    set: {
      scanner: entry.char,
      timestamp: new Date(entry.ts * 1000).toISOString(),
    },
  })
  .returning({ id: scanmeta.id });

const scanID = newscanmeta[0]?.id;

const newAuction = insertAuctionsSchema.safeParse({
  scanId: scanID,
  itemId: item,
  timestamp: new Date(scan.ts * 1000).toISOString(),
  seller,
  timeLeft: a.TimeLeft,
  itemCount: a.ItemCount,
  minBid: a.MinBid,
  buyout: a.Buyout,
  curBid: a.CurBid,
});
if (!newAuction.success) {
  console.error(fromZodError(newAuction.error));
  continue;
}

await db.insert(auctions).values(newAuction.data);

I also tried raw sql insert but it gives the same error

await db.run(sql`
  INSERT INTO ${auctions} (scanId, itemId, ts, seller, timeLeft, itemCount, minBid, buyout, curBid)
  VALUES (${newAuction.data.scanId}, ${newAuction.data.itemId}, ${newAuction.data.timestamp}, ${newAuction.data.seller}, ${newAuction.data.timeLeft}, ${newAuction.data.itemCount}, ${newAuction.data.minBid}, ${newAuction.data.buyout}, ${newAuction.data.curBid})
`);

await db.run(sql`
  INSERT INTO ${auctions} (scanId, itemId, ts, seller, timeLeft, itemCount, minBid, buyout, curBid)
  VALUES (${newAuction.data.scanId}, ${newAuction.data.itemId}, ${newAuction.data.timestamp}, ${newAuction.data.seller}, ${newAuction.data.timeLeft}, ${newAuction.data.itemCount}, ${newAuction.data.minBid}, ${newAuction.data.buyout}, ${newAuction.data.curBid})
`);

7 replies

DTDrizzle Team

•Created by Meexa on 8/18/2023 in #help

Update column A with the value of column B

I want to update column B with an updated value of column A So, something like this

db
    .update(characters)
    .set({
      normalized_name: normalize(characters.name),
    })

db
    .update(characters)
    .set({
      normalized_name: normalize(characters.name),
    })

Obviously doesn't work this way, but what would be the way to do it in Drizzle?

6 replies

DTDrizzle Team

•Created by Meexa on 8/16/2023 in #help

SQL_PARSE_ERROR: SQL string could not be parsed: near LP, "None"

Full error:

Error: SQL_PARSE_ERROR: SQL string could not be parsed: near LP, "None": syntax error at (1, 50)

Error: SQL_PARSE_ERROR: SQL string could not be parsed: near LP, "None": syntax error at (1, 50)

I'm getting this error with this query. Is the query correct? Trying to get a list of characters that are not in the DB yet from a list of IDs

db
  .select({ id: characters.id })
  .from(characters)
  .where(
    notExists(
      db
        .select({ id: characters.id })
        .from(characters)
        .where(
          inArray(
            characters.id,
            roster.body.members.map((member) => member.character.id),
          ),
        ),
    ),
  )
  .all();

db
  .select({ id: characters.id })
  .from(characters)
  .where(
    notExists(
      db
        .select({ id: characters.id })
        .from(characters)
        .where(
          inArray(
            characters.id,
            roster.body.members.map((member) => member.character.id),
          ),
        ),
    ),
  )
  .all();

schema

const characters = sqliteTable(
  'characters',
  {
    id: integer('id').notNull(),
    name: text('name').notNull(),
    realm: integer('realm').notNull(),
    region: text('region').notNull(),
    class: integer('class').notNull(),
    faction: integer('faction').notNull(),
  },
  (c) => ({
    unq: unique().on(c.name, c.realm, c.region),
    pk: primaryKey(c.name, c.realm, c.region),
    idIdx: index('id_index').on(c.id),
    nameIdx: index('name_index').on(c.name),
    charIdx: uniqueIndex('char_index').on(c.name, c.realm, c.region),
  }),
);

const characters = sqliteTable(
  'characters',
  {
    id: integer('id').notNull(),
    name: text('name').notNull(),
    realm: integer('realm').notNull(),
    region: text('region').notNull(),
    class: integer('class').notNull(),
    faction: integer('faction').notNull(),
  },
  (c) => ({
    unq: unique().on(c.name, c.realm, c.region),
    pk: primaryKey(c.name, c.realm, c.region),
    idIdx: index('id_index').on(c.id),
    nameIdx: index('name_index').on(c.name),
    charIdx: uniqueIndex('char_index').on(c.name, c.realm, c.region),
  }),
);

8 replies

DTDrizzle Team

•Created by Meexa on 8/15/2023 in #help

Drizzle can't find local sqlite file

I'm trying to play around with sqlite but I can't seem to connect my config and local sqlite db file. Config:

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema.ts',
  out: './drizzle',
  driver: 'better-sqlite',
  dbCredentials: {
    url: 'file:./sqlite.db',
  },
  verbose: true,
} satisfies Config;

import type { Config } from 'drizzle-kit';

export default {
  schema: './src/db/schema.ts',
  out: './drizzle',
  driver: 'better-sqlite',
  dbCredentials: {
    url: 'file:./sqlite.db',
  },
  verbose: true,
} satisfies Config;

The sqlite db file is in the root of my project, just like the drizzle config I've also set up this file in src/db/index.ts

import Database from 'better-sqlite3';
import { BetterSQLite3Database, drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('sqlite.db');
export const db: BetterSQLite3Database = drizzle(sqlite);

import Database from 'better-sqlite3';
import { BetterSQLite3Database, drizzle } from 'drizzle-orm/better-sqlite3';

const sqlite = new Database('sqlite.db');
export const db: BetterSQLite3Database = drizzle(sqlite);

Error:

> classic-armory@0.1.0 db:push
> drizzle-kit push:sqlite

No config path provided, using default 'drizzle.config.ts'
Reading config file '/Users/___/Documents/personal/classic-armory/drizzle.config.ts'
drizzle-kit: v0.19.12
drizzle-orm: v0.28.2

/Users/___/Documents/personal/classic-armory/node_modules/.pnpm/better-sqlite3@8.5.0/node_modules/better-sqlite3/lib/database.js:59
        throw new TypeError('Cannot open database because the directory does not exist');
              ^

TypeError: Cannot open database because the directory does not exist
    at new Database (/Users/___/Documents/personal/classic-armory/node_modules/.pnpm/better-sqlite3@8.5.0/node_modules/better-sqlite3/lib/database.js:59:9)
    at connectToSQLite (/Users/___/Documents/personal/classic-armory/node_modules/.pnpm/drizzle-kit@0.19.12/node_modules/drizzle-kit/index.cjs:51793:36)
    at async Command.<anonymous> (/Users/___/Documents/personal/classic-armory/node_modules/.pnpm/drizzle-kit@0.19.12/node_modules/drizzle-kit/index.cjs:53315:22)

Node.js v18.12.1

> classic-armory@0.1.0 db:push
> drizzle-kit push:sqlite

No config path provided, using default 'drizzle.config.ts'
Reading config file '/Users/___/Documents/personal/classic-armory/drizzle.config.ts'
drizzle-kit: v0.19.12
drizzle-orm: v0.28.2

/Users/___/Documents/personal/classic-armory/node_modules/.pnpm/better-sqlite3@8.5.0/node_modules/better-sqlite3/lib/database.js:59
        throw new TypeError('Cannot open database because the directory does not exist');
              ^

TypeError: Cannot open database because the directory does not exist
    at new Database (/Users/___/Documents/personal/classic-armory/node_modules/.pnpm/better-sqlite3@8.5.0/node_modules/better-sqlite3/lib/database.js:59:9)
    at connectToSQLite (/Users/___/Documents/personal/classic-armory/node_modules/.pnpm/drizzle-kit@0.19.12/node_modules/drizzle-kit/index.cjs:51793:36)
    at async Command.<anonymous> (/Users/___/Documents/personal/classic-armory/node_modules/.pnpm/drizzle-kit@0.19.12/node_modules/drizzle-kit/index.cjs:53315:22)

Node.js v18.12.1

5 replies

DTDrizzle Team

•Created by Meexa on 4/17/2023 in #help

How do I get the values of an insert inside a transaction?

I have this transaction where I create a project and add variables and a history of those variables. I need the IDs of the variables for variables_history. How can I get those values?

await db.transaction(async (tx) => {
  // Add project to DB
  await tx.insert(projects).values({
    // ...
  });

  // Add variables to DB
  await tx.insert(variables).values(
    repoVariables.data
      .filter((v) => v.variable_type === 'env_var')
      .map((v) => ({
        // ...
      })),
  );

  // Add variables history to DB
  const projectVariables = await getProjectVariables(projectId);

  console.log(projectVariables); // <-- empty array

  await tx.insert(variablesHistory).values(
    projectVariables.map((v) => ({
      // ...
    })),
  );
});

await db.transaction(async (tx) => {
  // Add project to DB
  await tx.insert(projects).values({
    // ...
  });

  // Add variables to DB
  await tx.insert(variables).values(
    repoVariables.data
      .filter((v) => v.variable_type === 'env_var')
      .map((v) => ({
        // ...
      })),
  );

  // Add variables history to DB
  const projectVariables = await getProjectVariables(projectId);

  console.log(projectVariables); // <-- empty array

  await tx.insert(variablesHistory).values(
    projectVariables.map((v) => ({
      // ...
    })),
  );
});

12 replies

DTDrizzle Team

•Created by Meexa on 4/12/2023 in #help

How do I connect to sqlite on fly.io?

I followed the instructions on fly.io on how to setup a project using sqlite. I believe I did it all correctly. I've created a volume and an app and I've linked the volume to the app. Now I don't know how to connect to the sqlite db on there. On local I simply do new Database('sqlite.db') but I don't know what to use on fly.io. There are no docs on fly.io about this either, and I already asked on their forums. Has anyone done this before? 🙂

15 replies

DTDrizzle Team

•Created by Meexa on 4/6/2023 in #help

"The supplied SQL string contains more than one statement" after running "generate:sqlite"

I just updated one of my schemas with a few columns and now my project is broken. This is the generated SQL

ALTER TABLE projects ADD `project_id` integer;
ALTER TABLE projects ADD `icon` text;
ALTER TABLE projects ADD `last_updated_at` text;
ALTER TABLE projects ADD `created_at` text;
ALTER TABLE projects ADD `archived` integer;

ALTER TABLE projects ADD `project_id` integer;
ALTER TABLE projects ADD `icon` text;
ALTER TABLE projects ADD `last_updated_at` text;
ALTER TABLE projects ADD `created_at` text;
ALTER TABLE projects ADD `archived` integer;

5 replies

DTDrizzle Team

•Created by Meexa on 3/30/2023 in #help

SQLite migrations are not being applied

First time using SQLite and drizzleORM, so forgive me if this is a stupid question. I've been trying to set this up for an hour now. I have installed drizzleORM and drizzle-kit, I've added the setup from the docs and I have ran my first migration with drizzle-kit generate:sqlite. I have a db file in my root and folder with the migration that creates a users table. I get an error when I try query the users table, saying it does not exist.

30 replies

Gaming

Programming