A TypeScript library for retrieving and formatting database schemas from multiple database types
npm install mschemajs> A powerful database schema extraction and formatting library



MSchemaJS is the JavaScript/TypeScript implementation of the M-Schema project by XGenerationLab.
M-Schema is a semi-structured database schema representation format that transforms complex database information into a concise, LLM-friendly format, significantly enhancing SQL generation accuracy in Text-to-SQL applications.
```
[DB_ID] my_database
[Schema]Table: users
[(id: INT, Primary Key),
(name: VARCHAR(100), Examples: [John Doe, Jane Smith, Bob Wilson]),
(email: VARCHAR(255), Examples: [john@example.com, jane@example.com, bob@example.com]),
(created_at: DATETIME, Examples: [2024-01-15 10:30:00, 2024-01-16 14:20:00])]Table: orders
[(order_id: INT, Primary Key),
(user_id: INT),
(amount: DECIMAL(10,2), Examples: [99.99, 149.50, 299.00]),
(status: VARCHAR(50), Examples: [pending, completed, cancelled])]
- π Multi-Database Support: Works with MySQL, PostgreSQL, SQLite, and OceanBase Oracle mode
- π Complete Schema Information: Automatically extracts table structures, column types, primary keys, comments, and other metadata
- π‘ Intelligent Sample Data: Automatically retrieves sample values for columns to help LLMs better understand data content
`bash`
npm install mschemajsor
pnpm install mschemajsor
yarn add mschemajs
This package supports both ESM (ECMAScript Modules) and CommonJS:
`javascript
// ESM
import { MSchema } from 'mschemajs';
// CommonJS
const { MSchema } = require('mschemajs');
`
`typescript
import { MSchema } from 'mschemajs';
const mschema = new MSchema({
host: 'localhost',
port: 3306,
type: 'mysql',
user: 'root',
password: 'your_password',
});
// Connect to database
await mschema.connect();
// Get all databases
const databases = await mschema.getDatabaseNames();
console.log('Available databases:', databases);
// Get complete information for a specific database
const db = await mschema.getDatabase('my_database');
console.log(db.toString()); // Output formatted M-Schema
// Get information for a specific table
const table = db.getTable('users');
console.log(table.toString());
// Disconnect
await mschema.disconnect();
`
`typescript
const mschema = new MSchema({
host: 'localhost',
port: 5432,
type: 'postgresql',
user: 'postgres',
password: 'your_password',
database: 'postgres',
});
await mschema.connect();
const db = await mschema.getDatabase('my_database');
console.log(db.toString());
await mschema.disconnect();
`
`typescript
const mschema = new MSchema({
host: '',
port: 0,
type: 'sqlite',
database: './example.db', // SQLite file path
});
await mschema.connect();
const databases = await mschema.getDatabaseNames();
const db = await mschema.getDatabase(databases[0]);
console.log(db.toString());
await mschema.disconnect();
`
`typescript
const mschema = new MSchema({
host: 'localhost',
port: 2883,
type: 'oceanbase-oracle',
user: 'your_username',
password: 'your_password',
sampleData: {
limit: 3,
excludeBinaryData: true,
},
});
await mschema.connect();
const schemas = await mschema.getDatabaseNames();
const db = await mschema.getDatabase('YOUR_SCHEMA');
console.log(db.toString());
await mschema.disconnect();
`
`typescript
interface DatabaseConfig {
host: string; // Database host address
port: number; // Database port
type: DatabaseType; // Database type
user?: string; // Username
password?: string; // Password
database?: string; // Database name (optional)
sampleData?: {
limit?: number; // Number of sample data rows, default 3
excludeBinaryData?: boolean; // Exclude binary data, default true
excludeTextData?: boolean; // Exclude text data, default false
};
}
type DatabaseType = 'mysql' | 'postgresql' | 'sqlite' | 'oceanbase-oracle';
`
#### MSchema Class
` #### Database Class #### Table Class async function textToSQL(question: string, evidence: string) { await mschema.connect(); // Build LLM Prompt γSchemaγ γQuestionγ γEvidenceγ Based on the user's question and evidence, generate an executable SQL statement. // Call LLM API // Example usage ` // If you need a custom connector // Register custom connector ` Issues and Pull Requests are welcome! If you're interested in the project or have any questions, feel free to contact us. This project is licensed under the MIT License. See the LICENSE file for details.typescript
class MSchema {
constructor(config: DatabaseConfig);
// Connect to database
async connect(): Promise
// Disconnect from database
async disconnect(): Promise
// Get all database names
async getDatabaseNames(): Promise
// Get complete information for a specific database
async getDatabase(databaseName: string): Promise
// Get information for a specific table
async getTable(databaseName: string, tableName: string): Promise;
// Clear cache
clearCache(): void;
// Get configuration
getConfig(): DatabaseConfig;
}
``typescript`
class Database {
// Get database ID
getId(): string;
// Get database name
getName(): string;
// Get schema (OceanBase Oracle mode)
getSchema(): string | undefined;
// Get all table names
getTableNames(): string[];
// Get specific table
getTable(tableName: string): Table | undefined;
// Get all tables
getAllTables(): Table[];
// Format as M-Schema string
toString(): string;
// Convert to JSON
toJSON(): DatabaseInfo;
}`typescript`
class Table {
// Get table name
getName(): string;
// Get all columns
getColumns(): ColumnInfo[];
// Get specific column
getColumn(columnName: string): ColumnInfo | undefined;
// Format as string
toString(): string;
// Convert to JSON
toJSON(): TableInfo;
}`$3
typescriptYou are a MySQL data analyst. The database schema is as follows:
import { MSchema } from 'mschemajs';
const mschema = new MSchema({
host: 'localhost',
port: 3306,
type: 'mysql',
user: 'root',
password: 'password',
});
const db = await mschema.getDatabase('my_database');
const schemaStr = db.toString();
const prompt =
${schemaStr}
${question}
${evidence};
const response = await callLLM(prompt);
await mschema.disconnect();
return response;
}
const sql = await textToSQL(
"How many users registered in January 2024?",
"User information is stored in the users table, with the registration date field being created_at"
);
`$3
typescript
import { BaseConnector, ConnectorFactory } from 'mschemajs';
class MyCustomConnector extends BaseConnector {
// Implement custom logic
}
// Then use it in configuration
`π§ Development
$3
bashInstall dependencies
pnpm installCompile TypeScript
pnpm buildWatch mode
pnpm watchClean build artifacts
pnpm clean
``$3
bash``Set environment variables
export MYSQL_HOST=localhost
export MYSQL_PORT=3306
export MYSQL_USER=root
export MYSQL_PASSWORD=your_passwordRun MySQL example
npx ts-node examples/mysql-example.tsRun PostgreSQL example
npx ts-node examples/postgresql-example.tsRun SQLite example
npx ts-node examples/sqlite-example.tsRun OceanBase Oracle example
export OCEANBASE_HOST=localhost
export OCEANBASE_PORT=2883
export OCEANBASE_USER=your_username
export OCEANBASE_PASSWORD=your_password
export OCEANBASE_SCHEMA=YOUR_SCHEMA
npx ts-node examples/oceanbase-oracle-example.tsπ€ Contributing
π License
π Related Links