Drizzle Team•7mo ago

db.query returns empty object

I'm using commonjs for swapping out the query builder from knex and bookshelf with drizzle and am seeing that logging db.query returns an empty object which I found because db.query.users.findFirst was giving me a can't call findfirst on undefined error. this is my schema

const users = mySchema.table('users', {
  id: int('id').primaryKey().autoincrement(),
  username: varchar('username', { length: 100 }),
  password: varchar('password', { length: 100 }),
});

const users = mySchema.table('users', {
  id: int('id').primaryKey().autoincrement(),
  username: varchar('username', { length: 100 }),
  password: varchar('password', { length: 100 }),
});

and my db config

const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  charset: 'utf8',
});

module.exports = connection
  .then(
    (conn) =>
      drizzle(conn, {
        schema,
        mode: 'default',
      })
  ).then((db) => console.log(db) || db); // logging here shows me that db.query is {}

const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  charset: 'utf8',
});

module.exports = connection
  .then(
    (conn) =>
      drizzle(conn, {
        schema,
        mode: 'default',
      })
  ).then((db) => console.log(db) || db); // logging here shows me that db.query is {}

8 Replies

rphlmr ⚡•7mo ago

How schema is imported? Looks like it is missing when creating drizzle

metowoOP•7mo ago

this is the more complete setup: schema.js

const { mysqlSchema, int, varchar, text } = require('drizzle-orm/mysql-core');

const mySchema = mysqlSchema('schema');

const users = mySchema.table('users', {
  id: int('id').primaryKey().autoincrement(),
  username: varchar('username', { length: 100 }),
  password: varchar('password', { length: 100 }),
});

const { mysqlSchema, int, varchar, text } = require('drizzle-orm/mysql-core');

const mySchema = mysqlSchema('schema');

const users = mySchema.table('users', {
  id: int('id').primaryKey().autoincrement(),
  username: varchar('username', { length: 100 }),
  password: varchar('password', { length: 100 }),
});

when logging mySchema, I get

schema before import> MySqlSchema {
  table: [Function: table],
  view: [Function: view],
  schemaName: 'schema'
}

schema before import> MySqlSchema {
  table: [Function: table],
  view: [Function: view],
  schemaName: 'schema'
}

and config.js

const mysql = require('mysql2/promise');
require('dotenv').config();
const { drizzle } = require('drizzle-orm/mysql2');
const { schema } = require('./schema.js');

const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  charset: 'utf8',
});

module.exports = connection
  .then(
    async (conn) =>
      await drizzle(conn, {
        schema,
        mode: 'default',
      })
  )
  .then((db) => console.log(db.schema) || db);

const mysql = require('mysql2/promise');
require('dotenv').config();
const { drizzle } = require('drizzle-orm/mysql2');
const { schema } = require('./schema.js');

const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  charset: 'utf8',
});

module.exports = connection
  .then(
    async (conn) =>
      await drizzle(conn, {
        schema,
        mode: 'default',
      })
  )
  .then((db) => console.log(db.schema) || db);

which does show undefined for db.schema

rphlmr ⚡•7mo ago

Ok! This is not the const schema that you have to set to the schema property of drizzle (if schema you import is mySchema in your example)😬 The schema property should be all your exported schema, tables and relations. For esm it is import * as schema, so for commonjs maybe const schema = require(…)?

metowoOP•7mo ago

this is what I get when I log db

 MySqlDatabase {
  query: {},
  dialect: MySqlDialect {},
  session: MySql2Session {
    dialect: MySqlDialect {},
    logger: NoopLogger {},
    mode: 'default',
    client: PromiseConnection {
      _events: [Object: null prototype],
      _eventsCount: 2,
      _maxListeners: undefined,
      connection: [Connection],
      Promise: [Function: Promise],
      [Symbol(shapeMode)]: false,
      [Symbol(kCapture)]: false
    },
    schema: { fullSchema: [Object], schema: {}, tableNamesMap: {} },
    options: { logger: undefined, mode: 'default' }
  },
  mode: 'default',
  _: {
    schema: {},
    fullSchema: {
      id: [MySqlInt],
      name: [MySqlText],
      [Symbol(drizzle:Name)]: 'users',
      [Symbol(drizzle:OriginalName)]: 'users',
      [Symbol(drizzle:Schema)]: 'my_schema',
      [Symbol(drizzle:Columns)]: [Object],
      [Symbol(drizzle:ExtraConfigColumns)]: [Object],
      [Symbol(drizzle:BaseName)]: 'users',
      [Symbol(drizzle:IsAlias)]: false,
      [Symbol(drizzle:ExtraConfigBuilder)]: undefined,
      [Symbol(drizzle:MySqlInlineForeignKeys)]: []
    },
    tableNamesMap: {}
  }
}

 MySqlDatabase {
  query: {},
  dialect: MySqlDialect {},
  session: MySql2Session {
    dialect: MySqlDialect {},
    logger: NoopLogger {},
    mode: 'default',
    client: PromiseConnection {
      _events: [Object: null prototype],
      _eventsCount: 2,
      _maxListeners: undefined,
      connection: [Connection],
      Promise: [Function: Promise],
      [Symbol(shapeMode)]: false,
      [Symbol(kCapture)]: false
    },
    schema: { fullSchema: [Object], schema: {}, tableNamesMap: {} },
    options: { logger: undefined, mode: 'default' }
  },
  mode: 'default',
  _: {
    schema: {},
    fullSchema: {
      id: [MySqlInt],
      name: [MySqlText],
      [Symbol(drizzle:Name)]: 'users',
      [Symbol(drizzle:OriginalName)]: 'users',
      [Symbol(drizzle:Schema)]: 'my_schema',
      [Symbol(drizzle:Columns)]: [Object],
      [Symbol(drizzle:ExtraConfigColumns)]: [Object],
      [Symbol(drizzle:BaseName)]: 'users',
      [Symbol(drizzle:IsAlias)]: false,
      [Symbol(drizzle:ExtraConfigBuilder)]: undefined,
      [Symbol(drizzle:MySqlInlineForeignKeys)]: []
    },
    tableNamesMap: {}
  }
}

I also tried to eliminate the potential issue of it being import related by having everything in the same file and I'm still seeing the same thing

const { mysqlSchema, int, text } = require('drizzle-orm/mysql-core');
const mysql = require('mysql2/promise');
require('dotenv').config();
const { drizzle } = require('drizzle-orm/mysql2');

const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  charset: 'utf8',
});

const mySchema = mysqlSchema('my_schema');
const mySchemaUsers = mySchema.table('users', {
  id: int('id').primaryKey().autoincrement(),
  name: text('name'),
});

module.exports = connection
  .then(
    async (conn) =>
      await drizzle(conn, {
        schema: { ...mySchemaUsers },
        mode: 'default',
      })
  )
  .then((db) => console.log('schema>>', db) || db);

const { mysqlSchema, int, text } = require('drizzle-orm/mysql-core');
const mysql = require('mysql2/promise');
require('dotenv').config();
const { drizzle } = require('drizzle-orm/mysql2');

const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  charset: 'utf8',
});

const mySchema = mysqlSchema('my_schema');
const mySchemaUsers = mySchema.table('users', {
  id: int('id').primaryKey().autoincrement(),
  name: text('name'),
});

module.exports = connection
  .then(
    async (conn) =>
      await drizzle(conn, {
        schema: { ...mySchemaUsers },
        mode: 'default',
      })
  )
  .then((db) => console.log('schema>>', db) || db);

is how I updated it

rphlmr ⚡•7mo ago

https://github.com/rphlmr/drizzle-mysql-commonjs/blob/main/config.js

GitHub

drizzle-mysql-commonjs/config.js at main · rphlmr/drizzle-mysql-com...

Contribute to rphlmr/drizzle-mysql-commonjs development by creating an account on GitHub.

rphlmr ⚡•7mo ago

You must use the sync mysql2 instead of the promise version

const mysql = require("mysql2");
const { drizzle } = require("drizzle-orm/mysql2");
const schema = require("./schema.js");

const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "root",
  database: "demo",
  charset: "utf8",
});

module.exports = drizzle(connection, {
  schema,
  mode: "default",
});

const mysql = require("mysql2");
const { drizzle } = require("drizzle-orm/mysql2");
const schema = require("./schema.js");

const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "root",
  database: "demo",
  charset: "utf8",
});

module.exports = drizzle(connection, {
  schema,
  mode: "default",
});

rphlmr ⚡•7mo ago

There is a bug with custom schema and drizzle kit for MySQL :/ https://github.com/drizzle-team/drizzle-orm/issues/2134

GitHub

[BUG]: Incorrect migration generation for indexes and constraints w...

What version of drizzle-orm are you using? 0.30.7 What version of drizzle-kit are you using? 0.20.14 Describe the Bug Drizzle kit generates incorrect migrations for tables with indexes and foreign ...

metowoOP•7mo ago

thanks

Gaming

Programming

db.query returns empty object

Did you find this page helpful?