{"id":17940,"library":"schemats","title":"Schemats: TypeScript Interface Generator","description":"Schemats is a command-line interface (CLI) tool designed to automatically generate TypeScript interface definitions directly from existing SQL database schemas, specifically supporting PostgreSQL and MySQL. The current stable version is 3.0.3. While it doesn't adhere to a strict release cadence, updates are made to enhance compatibility and features. Its primary differentiator lies in enabling strong static typing for database queries by bridging the gap between relational databases and TypeScript applications. Users can generate type definitions for entire schemas or specific tables, either through command-line arguments or a `schemats.json` configuration file, which then allows for type-safe database interactions and enhanced developer experience with autocompletion and static checks in their application code.","status":"active","version":"3.0.3","language":"javascript","source_language":"en","source_url":"https://github.com/sweetiq/schemats","tags":["javascript","postgres","schema","typescript","sql"],"install":[{"cmd":"npm install schemats","lang":"bash","label":"npm"},{"cmd":"yarn add schemats","lang":"bash","label":"yarn"},{"cmd":"pnpm add schemats","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Runtime dependency for connecting to PostgreSQL databases.","package":"pg","optional":false},{"reason":"Runtime dependency for connecting to MySQL databases.","package":"mysql2","optional":false},{"reason":"Used for parsing command-line arguments for the CLI tool.","package":"commander","optional":false}],"imports":[{"note":"Schemats itself is primarily a CLI tool; its main 'import' interaction is when user code imports the *generated* TypeScript files. The generated file typically exports interfaces under a namespace or as direct interfaces.","symbol":"GeneratedTypes","correct":"import * as dbTypes from './path/to/generated/db.ts'"},{"note":"While Schemats is mostly a CLI tool, its `generate` function can be imported and used programmatically. However, the CLI is the common method. Ensure your project is set up for ESM if importing directly.","wrong":"const schemats = require('schemats'); schemats.generate(...)","symbol":"generate","correct":"import { generate } from 'schemats'"}],"quickstart":{"code":"import { Client } from 'pg';\nimport * as path from 'path';\nimport { execSync } from 'child_process';\n\n// 1. Ensure schemats is installed globally or accessible via npx\n// npm install -g schemats\n\nconst dbUrl = 'postgres://postgres:password@localhost:5432/mytestdb';\nconst outputFile = path.join(process.cwd(), 'db-types.ts');\n\nasync function setupAndGenerate() {\n  // For demonstration, ensure a test database exists\n  const client = new Client({\n    connectionString: 'postgres://postgres:password@localhost:5432/postgres'\n  });\n  try {\n    await client.connect();\n    await client.query(`DROP DATABASE IF EXISTS mytestdb;`);\n    await client.query(`CREATE DATABASE mytestdb;`);\n    await client.end();\n\n    const testClient = new Client({ connectionString: dbUrl });\n    await testClient.connect();\n    await testClient.query(`\n      CREATE TABLE IF NOT EXISTS users (\n        id SERIAL PRIMARY KEY,\n        username VARCHAR(255) NOT NULL UNIQUE,\n        email VARCHAR(255) NOT NULL,\n        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP\n      );\n      CREATE TABLE IF NOT EXISTS products (\n        product_id UUID DEFAULT gen_random_uuid() PRIMARY KEY,\n        name VARCHAR(255) NOT NULL,\n        price NUMERIC(10, 2) NOT NULL\n      );\n    `);\n    await testClient.end();\n\n    console.log('Database and tables created. Generating types...');\n\n    // 2. Generate TypeScript interfaces from the schema\n    // Using npx for local schemats binary or if not globally installed\n    const command = `npx schemats generate -c \"${dbUrl}\" -s public -o \"${outputFile}\"`;\n    execSync(command, { stdio: 'inherit' });\n\n    console.log(`\nGenerated TypeScript types to ${outputFile}:\n`);\n\n    // 3. Demonstrate usage of generated types\n    // (In a real app, you'd import generated types from outputFile)\n    // For this example, we'll simulate the types:\n    interface Users {\n      id: number;\n      username: string;\n      email: string;\n      created_at: Date;\n    }\n    interface Products {\n      product_id: string; // UUIDs often map to strings in TS\n      name: string;\n      price: string; // NUMERIC can be string or number, safer as string without specific parser\n    }\n\n    const exampleUser: Users = { id: 1, username: 'testuser', email: 'test@example.com', created_at: new Date() };\n    const exampleProduct: Products = { product_id: 'a1b2c3d4-e5f6-7890-1234-567890abcdef', name: 'Example Item', price: '99.99' };\n\n    console.log('Example user with generated type:', exampleUser);\n    console.log('Example product with generated type:', exampleProduct);\n\n  } catch (error) {\n    console.error('Error during setup or generation:', error);\n    process.exit(1);\n  }\n}\n\nsetupAndGenerate();\n","lang":"typescript","description":"This quickstart demonstrates how to set up a PostgreSQL database, create some tables, then use the `schemats` CLI to generate TypeScript interfaces, and finally shows a basic example of how these generated types would be used in application code."},"warnings":[{"fix":"Use environment variables (e.g., `PGUSER=postgres PGHOST=localhost schemats generate -c \"postgres://localhost/mydb\" ...`) or a separate `.env` file loaded securely at runtime.","message":"Embedding database credentials directly in command-line arguments or configuration files can pose a security risk, especially in shared environments or version control. Consider using environment variables for sensitive information.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Either install `schemats` globally (`npm install -g schemats`) or use `npx schemats` to execute the package binary from your `node_modules` without global installation.","message":"If `schemats` is not installed globally (`npm install -g schemats`), the `schemats` command might not be found directly in your shell's PATH. This leads to 'command not found' errors.","severity":"gotcha","affected_versions":">=1.0.0"},{"fix":"Carefully review the `schemats` README and any release notes for the new major version. Regenerate your TypeScript types and manually adapt any application code that consumes them, as underlying type inference or naming conventions might have changed.","message":"There's no documented automatic migration path or `ng update`-style schematic for major version upgrades (e.g., from v2 to v3). Users should review their setup and generated types for manual adjustments.","severity":"breaking","affected_versions":">=3.0.0"},{"fix":"When consuming the generated types, be aware that `price: string` might be expected instead of `price: number`. Parse or convert these values explicitly (e.g., `parseFloat(item.price)`) where numeric operations are required.","message":"Generated types for `NUMERIC` or `DECIMAL` SQL types in PostgreSQL often map to `string` in TypeScript by default to prevent precision loss. If you require `number` types, you'll need to handle the conversion manually or configure your ORM/database client.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"last_verified":"2026-04-23T00:00:00.000Z","next_check":"2026-07-22T00:00:00.000Z","problems":[{"fix":"Install globally: `npm install -g schemats` or run with npx: `npx schemats generate ...`","cause":"The `schemats` executable is not in your system's PATH.","error":"schemats: command not found"},{"fix":"Verify your database connection string, ensuring the username, password, host, and port are correct and the user has appropriate permissions. Example: `postgres://user:password@host:port/database`","cause":"Incorrect username, password, or host in the database connection string.","error":"Error: password authentication failed for user \"postgres\""},{"fix":"Ensure the database specified in your connection string (`-c`) has been created on your PostgreSQL or MySQL server.","cause":"The specified database in the connection string does not exist on the server.","error":"Error: database \"mytestdb\" does not exist"},{"fix":"Double-check the database name, schema name (`-s`), and table names (`-t`) in your command or `schemats.json` config. Ensure the connected user has permissions to see these tables.","cause":"No tables were found in the specified schema, or the schema/table names were incorrect.","error":"Error: No tables were found matching the specified criteria for schema 'public'."}],"ecosystem":"npm","meta_description":null,"install_score":null,"install_tag":null,"quickstart_score":null,"quickstart_tag":null}