使用TypeORM访问information_schema失败：报错relation不存在，如何解决？

Got it, let's break down why you're seeing that QueryFailedError: relation "information_schema.columns" does not exist error and fix it. The root cause almost always ties to your database type or small syntax/configuration details.

1. First, Confirm Your Database Type #

Different databases handle system metadata differently—information_schema is a standard for databases like PostgreSQL, MySQL, and SQL Server, but it doesn't exist in SQLite. Here's how to handle both cases:

Case A: You're Using PostgreSQL/MySQL/SQL Server #

If your database supports information_schema, the error is likely due to one of these issues:

• Case sensitivity: Some databases (like PostgreSQL) treat unquoted identifiers as lowercase. Your query uses information_schema.COLUMNS (uppercase), which might be causing the relation lookup to fail.
• Missing schema specification: In PostgreSQL, tables live in schemas (default is public). You might need to explicitly specify the schema for your target table.
• Permission issues: Ensure your database user has the right to query information_schema.

Fix the query with these adjustments:

const rawData = await connection.query(`
  SELECT column_name 
  FROM information_schema.columns 
  WHERE table_name = 'members'
  -- Add this line if using PostgreSQL to target the default schema
  AND table_schema = 'public';
`);

If you need to preserve uppercase identifiers (uncommon), wrap them in double quotes:

const rawData = await connection.query(`
  SELECT "COLUMN_NAME" 
  FROM "information_schema"."COLUMNS" 
  WHERE "TABLE_NAME" = 'members';
`);

Case B: You're Using SQLite #

SQLite doesn't have information_schema—instead, use its built-in PRAGMA commands or query the sqlite_master table to get table metadata.

Option 1: Use PRAGMA table_info (Simplest)

This returns direct column details for your table:

const columnData = await connection.query(`PRAGMA table_info('members')`);
// Extract column names from the result
const columnNames = columnData.map((row: any) => row.name);
console.log(columnNames); // Outputs your table's column names

Option 2: Query sqlite_master

If you need the full CREATE TABLE statement, use this:

const tableData = await connection.query(`
  SELECT sql 
  FROM sqlite_master 
  WHERE type = 'table' AND name = 'members';
`);
console.log(tableData[0].sql); // Shows the CREATE TABLE syntax for 'members'

2. Double-Check Your TypeORM Connection Configuration #

Make sure you're connecting to the correct database instance. For example, in MySQL, if you accidentally connect to the wrong database, information_schema might still exist, but your members table won't be found. Verify your ormconfig.json or connection setup points to the right database.

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