Drizzle Team•2y ago

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.

18 Replies

MeexaOP•2y ago

this is my folder structure:

server
  prime_env.db
  drizzle.config.json
  migrations/
  src/
    db.ts
    db/schema.ts

server
  prime_env.db
  drizzle.config.json
  migrations/
  src/
    db.ts
    db/schema.ts

This is my code so far:

// src/db.ts

import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import Database from 'better-sqlite3';

import { users } from './db/schema';

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

migrate(db, { migrationsFolder: './migrations' });

export async function getUsers() {
  return db.select().from(users).all();
}

// src/db.ts

import { drizzle } from 'drizzle-orm/better-sqlite3';
import { migrate } from 'drizzle-orm/better-sqlite3/migrator';
import Database from 'better-sqlite3';

import { users } from './db/schema';

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

migrate(db, { migrationsFolder: './migrations' });

export async function getUsers() {
  return db.select().from(users).all();
}

// src/db/schema.ts

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

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

// src/db/schema.ts

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

export const users = sqliteTable('users', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

When I run getUsers() I get an error that the users table does not exist.

{
  "err": {
    "type": "SqliteError",
    "message": "no such table: users",
    "stack": "SqliteError: no such table: users\n    at Database.prepare ...",
    "code": "SQLITE_ERROR"
  },
  "msg": "no such table: users"
}

{
  "err": {
    "type": "SqliteError",
    "message": "no such table: users",
    "stack": "SqliteError: no such table: users\n    at Database.prepare ...",
    "code": "SQLITE_ERROR"
  },
  "msg": "no such table: users"
}

Dan•2y ago

You generated the migrations. Now you have to actually apply them to the DB.

MeexaOP•2y ago

Does this not do that for me?

migrate(db, { migrationsFolder: './migrations' });

migrate(db, { migrationsFolder: './migrations' });

Dan•2y ago

Yeah, it should

MeexaOP•2y ago

No errors when I start the server, so it seems to do something right.

Dan•2y ago

@Andrii Sherman help needed

Andrii Sherman•2y ago

👀

MeexaOP•2y ago

I can make the repo public if that helps. It's just boilerplate code right now so no big deal

Andrii Sherman•2y ago

yeah, would be great will help me a lot

MeexaOP•2y ago

https://github.com/sandervspl/prime-env

GitHub

GitHub - sandervspl/prime-env

Contribute to sandervspl/prime-env development by creating an account on GitHub.

Andrii Sherman•2y ago

okey, could you please remove migrations folder and run generate:sqlite 1 more time? and then run your script

MeexaOP•2y ago

sure yeah that worked 🤔

Dan•2y ago

"did you try turning it off and on again"

MeexaOP•2y ago

lmao yep I guess it didnt add the migration to the entries in the journal? thats what I see in the git diff

Andrii Sherman•2y ago

case was that _journal for some reason was empty I guess you tried to run migration firstly and did have some problems with that, so journal remained empty yes We will add this part to backlog so drizzle-kit will chek journal and warn you if needed thanks for the great case!

MeexaOP•2y ago

Okay! Thanks for the help 🙂

Dan•2y ago

I read that as "warm you if needed" could be a useful feature

MeexaOP•2y ago

Haha my struggle is not for nothing

Gaming

Programming

SQLite migrations are not being applied