DT
Drizzle Team•2y ago
JT

error: there is no transaction in progress

I created a drizzle table: 
export const passwordTypeEnum = pgEnum('passwordType', ['bcrypt']);
export const useAsDisplayNameEnum = pgEnum('useAsDisplayName', ['username', 'email', 'realName']);
export const users = pgTable('users',
    {
        id: serial('id').primaryKey(),
        createdAt: timestamp('createdAt').defaultNow().notNull(),
        updatedAt: timestamp('updatedAt').defaultNow().notNull(),
        username: varchar('username', { length: 60 }).notNull(),
        email: varchar('email', { length: 255 }).notNull(),
        realName: varchar('realName', { length: 60 }).notNull(),
        password: varchar('password', { length: 255 }).notNull().default('no-password-specified'),
        passwordType: passwordTypeEnum('passwordType').default('bcrypt').notNull(),
        useAsDisplayName: useAsDisplayNameEnum('useAsDisplayName').default('username').notNull(),
        admin: boolean('admin').notNull().default(false),
        developer: boolean('admin').notNull().default(false),
    },
    (users) => ({
        usernameIndex: uniqueIndex('usernameIndex').on(users.username),
        emailIndex: uniqueIndex('emailIndex').on(users.email),
    })
);
export const passwordTypeEnum = pgEnum('passwordType', ['bcrypt']);
export const useAsDisplayNameEnum = pgEnum('useAsDisplayName', ['username', 'email', 'realName']);
export const users = pgTable('users',
    {
        id: serial('id').primaryKey(),
        createdAt: timestamp('createdAt').defaultNow().notNull(),
        updatedAt: timestamp('updatedAt').defaultNow().notNull(),
        username: varchar('username', { length: 60 }).notNull(),
        email: varchar('email', { length: 255 }).notNull(),
        realName: varchar('realName', { length: 60 }).notNull(),
        password: varchar('password', { length: 255 }).notNull().default('no-password-specified'),
        passwordType: passwordTypeEnum('passwordType').default('bcrypt').notNull(),
        useAsDisplayName: useAsDisplayNameEnum('useAsDisplayName').default('username').notNull(),
        admin: boolean('admin').notNull().default(false),
        developer: boolean('admin').notNull().default(false),
    },
    (users) => ({
        usernameIndex: uniqueIndex('usernameIndex').on(users.username),
        emailIndex: uniqueIndex('emailIndex').on(users.email),
    })
);
Which then generated this migration: 
DO $$ BEGIN
 CREATE TYPE "passwordType" AS ENUM('bcrypt');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 CREATE TYPE "useAsDisplayName" AS ENUM('username', 'email', 'realName');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY NOT NULL,
    "createdAt" timestamp DEFAULT now() NOT NULL,
    "updatedAt" timestamp DEFAULT now() NOT NULL,
    "username" varchar(60) NOT NULL,
    "email" varchar(255) NOT NULL,
    "realName" varchar(60) NOT NULL,
    "password" varchar(255) DEFAULT 'no-password-specified' NOT NULL,
    "passwordType" passwordType DEFAULT 'bcrypt' NOT NULL,
    "useAsDisplayName" useAsDisplayName DEFAULT 'username' NOT NULL,
    "admin" boolean DEFAULT false NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS usernameIndex ON users ("username");
CREATE UNIQUE INDEX IF NOT EXISTS emailIndex ON users ("email");
DO $$ BEGIN
 CREATE TYPE "passwordType" AS ENUM('bcrypt');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 CREATE TYPE "useAsDisplayName" AS ENUM('username', 'email', 'realName');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY NOT NULL,
    "createdAt" timestamp DEFAULT now() NOT NULL,
    "updatedAt" timestamp DEFAULT now() NOT NULL,
    "username" varchar(60) NOT NULL,
    "email" varchar(255) NOT NULL,
    "realName" varchar(60) NOT NULL,
    "password" varchar(255) DEFAULT 'no-password-specified' NOT NULL,
    "passwordType" passwordType DEFAULT 'bcrypt' NOT NULL,
    "useAsDisplayName" useAsDisplayName DEFAULT 'username' NOT NULL,
    "admin" boolean DEFAULT false NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS usernameIndex ON users ("username");
CREATE UNIQUE INDEX IF NOT EXISTS emailIndex ON users ("email");
That creates this error when I try to use the migrator to run it. 
error: there is no transaction in progress
error: there is no transaction in progress
It looks like transactions were generated to wrap around the enums, but not the create table or create indexes. Did I do something wrong here? Or is this error pointing to something else?
5 Replies
JT
JTOP•2y ago
actually i solved that part. Turns out using Pool with the migrator makes it throw that error. So instead I tried using Client instead. Then that error goes away. However... now i get: 
error: at or near "do": syntax error
    at Parser.parseErrorMessage (/Users/jtsmith/ving/.output/drizzle/migrate.js:2950:98)
    at Parser.handlePacket (/Users/jtsmith/ving/.output/drizzle/migrate.js:2791:25)
    at Parser.parse (/Users/jtsmith/ving/.output/drizzle/migrate.js:2715:34)
    at TLSSocket.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:2991:44)
    at TLSSocket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:228:10)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 106,
  severity: 'ERROR',
  code: '42601',
  detail: 'source SQL:\nDO $$ BEGIN\n^',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'lexer.go',
  line: '271',
  routine: 'Error'
}
error: at or near "do": syntax error
    at Parser.parseErrorMessage (/Users/jtsmith/ving/.output/drizzle/migrate.js:2950:98)
    at Parser.handlePacket (/Users/jtsmith/ving/.output/drizzle/migrate.js:2791:25)
    at Parser.parse (/Users/jtsmith/ving/.output/drizzle/migrate.js:2715:34)
    at TLSSocket.<anonymous> (/Users/jtsmith/ving/.output/drizzle/migrate.js:2991:44)
    at TLSSocket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:228:10)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 106,
  severity: 'ERROR',
  code: '42601',
  detail: 'source SQL:\nDO $$ BEGIN\n^',
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'lexer.go',
  line: '271',
  routine: 'Error'
}
I'm running this on Cockroach DB rather than Postgres, if that matters. I've been using Cockroach with my other Postgres apps and it hasn't been a problem, but I thought I'd bring it up in case.
JT
JTOP•2y ago
ok, actually it does apparently matter. at least according to this post from 2020, CDB doesn't support the DO syntax. https://github.com/jumpn/ecto_cockroachdb/issues/4
GitHub
syntax error at or near "do" - DO $$ BEGIN CREATE INDEX (...);EXCEP...
Hi! First of all, thanks for creating this adaptor! 🙂 I tried to use create_if_not_exists to create an index in an ecto migration, but it generates SQL that Cockroach doesn&#39;t seem to suppor...
Andrii Sherman
Andrii Sherman•2y ago
We don't have Cockroach DB support yet. It should work with all common queries, so you can still try it. But we are not aware of all cases and Cockroach specifics yet. Just so you know Regarding generated migration with DO syntax - it was our approach to execute migrations on table, that already had all types and tables from schema We are going to add --strict mode, that will disable any kind of DO syntax To unblock you for now with Cockroach - you can modify sql file, by deleting DO part. Just be sure, that you have modified this file BEFORE executing migrations on your database So you can have this: 
CREATE TYPE "passwordType" AS ENUM('bcrypt');

CREATE TYPE "useAsDisplayName" AS ENUM('username', 'email', 'realName');
CREATE TYPE "passwordType" AS ENUM('bcrypt');

CREATE TYPE "useAsDisplayName" AS ENUM('username', 'email', 'realName');
instead of this 
DO $$ BEGIN
 CREATE TYPE "passwordType" AS ENUM('bcrypt');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 CREATE TYPE "useAsDisplayName" AS ENUM('username', 'email', 'realName');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
DO $$ BEGIN
 CREATE TYPE "passwordType" AS ENUM('bcrypt');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 CREATE TYPE "useAsDisplayName" AS ENUM('username', 'email', 'realName');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
We are allowing for such modifications in sql before executing it on database, just in case some sql generation is broken or even using unknown dialect for drizzle, like in your case. And it still won't block you in using drizzle for your needs But anyway we have it in backlog and thanks so much for your feedback!
Afi
Afi•9mo ago
I just started using cockroachdb and encountered this issue, has the --strict mode been implemented? is there a workaround for this other than removing the DO $$ ?
Eduarte
Eduarte•3mo ago
in my case I did the DO removal thing and it's working fine, thanks a lot
Want results from more Discord servers?
Add your server