Sequelize调用findOne()/findAll()返回空数组问题排查咨询

Hey there! Let's figure out why your Sequelize queries are returning empty arrays even though you can see the data in pgAdmin. I'll walk through the most likely issues based on your code and PostgreSQL's behavior.

1. Table Name Case Sensitivity (Most Likely Culprit) #

PostgreSQL has a key quirk with table names: if you created your table without double quotes (like CREATE TABLE users (...)), PostgreSQL automatically converts the table name to lowercase. But in your Sequelize code, you defined the model as "Users"—this makes Sequelize run queries against the case-sensitive "Users" table.

When you run select * from Users; in pgAdmin, PostgreSQL ignores the case and targets the lowercase users table (which has your data). But Sequelize's query uses FROM "Users" AS "Users" (with double quotes), which looks for a literal "Users" table that either doesn't exist or is empty in your database.

Fix: #

Adjust your model to explicitly match your actual table name:

const User = sequelize.define("User", { // Use singular model name for clarity
  // ... your existing field definitions
}, {
  timestamps: true,
  versionKey: false,
  tableName: 'users', // Explicitly set to your lowercase table name
  hooks:{ /* ... your existing hooks ... */ }
});

Alternatively, if you intentionally want a case-sensitive table name, make sure your PostgreSQL table was created with double quotes:

CREATE TABLE "Users" ( /* ... your table schema ... */ );

Or enable freezeTableName in your Sequelize instance config to prevent automatic table name modifications:

// In your db/database.js file
const sequelize = new Sequelize({
  // ... your existing connection configs
  define: {
    freezeTableName: true
  }
});

2. Verify Your Sequelize Connection Targets the Right Database #

It sounds simple, but double-check that your Sequelize instance is connected to the same database you're querying in pgAdmin. Add a quick log to confirm:

// In your User model file, after importing sequelize
console.log(`Connected to database: ${sequelize.config.database}`);

If this database name doesn't match the one in pgAdmin, that's exactly why you're getting empty results.

3. Check for Timestamp Field Format Mismatches #

Your model has timestamps: true, which tells Sequelize to expect createdAt and updatedAt columns. If your actual PostgreSQL table uses snake_case (created_at and updated_at) instead of camelCase, Sequelize will still try to select the camelCase versions. While this usually throws an error, it's worth checking by enabling underscored in your model config:

const User = sequelize.define("Users", {
  // ... your fields
}, {
  timestamps: true,
  versionKey: false,
  underscored: true, // Automatically maps camelCase fields to snake_case in the database
  hooks:{ /* ... */ }
});

4. Ensure sync() Doesn't Cause Timing Issues #

You're calling User.sync(); at the end of your model file, which is an asynchronous method. If your app runs queries before sync() completes (possible in some rapid initialization setups), you might hit unexpected behavior. Fix this by awaiting the sync before starting your app:

// In your app entry point (e.g., server.js)
const { User } = require('./path-to-your-user-model');

async function startApp() {
  await User.sync(); // Wait for table synchronization to finish
  // ... start your server or execute queries
}

startApp();

Quick Debug Test #

To narrow it down further, run a raw Sequelize query to see if you get results:

const users = await sequelize.query('SELECT * FROM users;'); // Use your actual table name
console.log(users);

If this returns data, the issue is definitely with your model's table name configuration.

内容的提问来源于stack exchange，提问作者user19580280