drizzle-team / drizzle-orm

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅
https://orm.drizzle.team
Apache License 2.0
24.53k stars 643 forks source link

[BUG]: Push to turso db (libsql/sqlite) fails with SQL_PARSE _ERROR on integer specifically #1353

Open JPTomorrow opened 1 year ago

JPTomorrow commented 1 year ago

### What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

I am working with SvelteKit, Turso, and drizzle-orm to and drizzle pushing to Turso was working a couple of days ago, but may have cropped up when I accidentally somehow uninstalled better-sqlite3 which drizzle was using for migration and push. I reinstalled it and was met with a new error. This error comes up whenever I try to add a new integer column of any configuration to an existing table. I have a table called test-products in the following schema. Adding the column 'ratings' and running drizzle push yields the following error.

npm run drizzle:push

> stoners-hearth-website-svelte@0.0.1 drizzle:push
> drizzle-kit push:sqlite - schema=./drizzle/schema.ts

No config path provided, using default path
Reading config file '/home/jmorrow/code/canna-brothers-dist-website-svelte/drizzle.config.ts'
drizzle-kit: v0.19.13
drizzle-orm: v0.28.6

LibsqlError: SQL_PARSE_ERROR: SQL string could not be parsed: near MINUS, "None": syntax error at (1, 18)
    at mapHranaError (/home/jmorrow/code/canna-brothers-dist-website-svelte/node_modules/drizzle-kit/index.cjs:48441:12)
    at HranaClient.execute (/home/jmorrow/code/canna-brothers-dist-website-svelte/node_modules/drizzle-kit/index.cjs:48481:17)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async TursoSqlite.run (/home/jmorrow/code/canna-brothers-dist-website-svelte/node_modules/drizzle-kit/index.cjs:42935:9)
    at async Command.<anonymous> (/home/jmorrow/code/canna-brothers-dist-website-svelte/node_modules/drizzle-kit/index.cjs:53505:9) {
  code: 'SQL_PARSE_ERROR',
  [cause]: [ResponseError: SQL string could not be parsed: near MINUS, "None": syntax error at (1, 18)] {
    code: 'SQL_PARSE_ERROR',
    proto: {
      message: 'SQL string could not be parsed: near MINUS, "None": syntax error at (1, 18)',
      code: 'SQL_PARSE_ERROR'
    }
  }
}

This is my schema.

import { sqliteTable, text, numeric, uniqueIndex, integer } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';

export const libsqlWasmFuncTable = sqliteTable('libsql_wasm_func_table', {
    name: text('name').primaryKey().notNull(),
    body: text('body')
});

export const drizzleMigrations = sqliteTable('__drizzle_migrations', {
    id: numeric('id').primaryKey().notNull(),
    hash: text('hash').notNull(),
    createdAt: numeric('created_at')
});

export const blogPosts = sqliteTable(
    'blog-posts',
    {
        id: integer('id').primaryKey().notNull(),
        name: text('name').notNull(),
        headline: text('headline').notNull(),
        body: text('body').notNull(),
        createdAt: integer('created_at')
            .default(sql`(cast (unixepoch() as int))`)
            .notNull()
    },
    (table) => {
        return {
            idUnique: uniqueIndex('blog-posts_id_unique').on(table.id)
        };
    }
);

export const testProducts = sqliteTable(
    'test-products',
    {
        id: integer('id').primaryKey().notNull(),
        name: text('name').notNull(),
        strainType: text('strain_type').notNull(),
        thcContent: text('thc_content').notNull(),
        cbdContent: text('cbd_content').notNull(),
        img: text('img').notNull(),
        price: text('price').notNull(),
        description: text('description').notNull(),
        createdAt: integer('created_at')
            .default(sql`(cast (unixepoch() as int))`)
            .notNull(),
        rating: integer('rating').default(0)
    },
    (table) => {
        return {
            idUnique: uniqueIndex('test-products_id_unique').on(table.id)
        };
    }
);

Here is my package.json and package.lock. I feel they may be valuable because of the better-sqlite3 missing message I mentioned before.

"devDependencies": {
        "@skeletonlabs/skeleton": "^1.10.0",
        "@sveltejs/adapter-auto": "^2.0.0",
        "@sveltejs/adapter-vercel": "^3.0.3",
        "@sveltejs/kit": "^1.20.4",
        "@typescript-eslint/eslint-plugin": "^5.45.0",
        "@typescript-eslint/parser": "^5.45.0",
        "autoprefixer": "^10.4.14",
        "dotenv": "^16.3.1",
        "drizzle-kit": "^0.19.13",
        "eslint": "^8.28.0",
        "eslint-config-prettier": "^8.5.0",
        "eslint-plugin-svelte": "^2.30.0",
        "postcss": "^8.4.24",
        "postcss-load-config": "^4.0.1",
        "prettier": "^2.8.0",
        "prettier-plugin-svelte": "^2.10.1",
        "svelte": "^4.0.5",
        "svelte-check": "^3.4.3",
        "tailwindcss": "^3.3.2",
        "tslib": "^2.4.1",
        "tsx": "^3.12.10",
        "typescript": "^5.0.0",
        "vite": "^4.4.2"
    },
    "type": "module",
    "dependencies": {
        "@floating-ui/dom": "^1.5.1",
        "@libsql/client": "^0.3.5",
        "@skeletonlabs/tw-plugin": "^0.2.0",
        "@vercel/analytics": "^1.0.2",
        "@vercel/node": "^2.15.10",
        "ai": "^2.2.14",
        "drizzle-orm": "^0.28.6",
        "openai": "^4.11.1",
        "postmark": "^3.0.19",
        "svelte-email": "^0.0.4",
        "svelte-particles": "^2.12.0",
        "svelte-time": "^0.8.0",
        "tabler-icons-svelte": "^1.10.0",
        "web-vitals": "^3.4.0"
    }

I really have no idea where to begin on this so any help is appreciated. Also, new to issues so sorry for any bad etiquette.

Expected behavior

I expect to push the new integer column 'ratings' to my Turso sqlite/libsql database.

Environment & setup

Svetekit, drizzle-orm, Turso, tailwind, skeleton UI, typescript

stunaz commented 8 months ago

check the generated sql, it helped me see the incompatibility

L-Mario564 commented 1 month ago

@JPTomorrow Hey there, is this still an issue in the latest versions of ORM and Kit?