DT
Drizzle Team•2mo ago
metowo

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 ⚡
rphlmr ⚡•2mo ago
How schema is imported? Looks like it is missing when creating drizzle
metowo
metowo•2mo 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 ⚡
rphlmr ⚡•2mo 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(…)?
metowo
metowo•2mo 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 ⚡
rphlmr ⚡•2mo 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 ⚡
rphlmr ⚡•2mo 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 ⚡
rphlmr ⚡•2mo 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 ...
metowo
metowo•2mo ago
thanks
Want results from more Discord servers?
Add your server