Type Generation

Generating TypeScript types from your ClickHouse database is fundamental to building type-safe applications with hypequery. This page explains how to use our CLI tool to generate type definitions from your database schema.

Usage

npx hypequery-generate-types [options]

Command Line Options

Environment Variables

The CLI will automatically read connection details from environment variables if not specified via command-line options:

Note for Framework Users: The CLI also supports framework-specific environment variable prefixes. For Vite applications, you can use VITE_CLICKHOUSE_* variables (e.g., VITE_CLICKHOUSE_HOST). For Next.js applications, you can use NEXT_PUBLIC_CLICKHOUSE_* variables (e.g., NEXT_PUBLIC_CLICKHOUSE_HOST). These alternatives are checked if the standard variables are not found.

Examples

Basic Usage

# Generate schema with default settings
npx hypequery-generate-types

# Specify an output path
npx hypequery-generate-types --output=./src/types/db-schema.ts

# Connect to a local ClickHouse instance
npx hypequery-generate-types --host=http://localhost:8123 --username=default --password=password --database=my_db

ClickHouse Cloud

# Connect to ClickHouse Cloud with secure connection
npx hypequery-generate-types --host=https://your-instance.clickhouse.cloud:8443 --username=default --password=your-password --database=your_db --secure

Filtering Tables

# Only include specific tables
npx hypequery-generate-types --include-tables=users,orders,products

# Exclude specific tables
npx hypequery-generate-types --exclude-tables=temp_tables,log_tables

Using Environment Variables

# Using standard environment variables
CLICKHOUSE_HOST=http://localhost:8123 CLICKHOUSE_PASSWORD=password npx hypequery-generate-types

# For Next.js projects
NEXT_PUBLIC_CLICKHOUSE_HOST=https://your-instance.clickhouse.cloud:8443 NEXT_PUBLIC_CLICKHOUSE_PASSWORD=your-password npx hypequery-generate-types --secure

Generated Types

The CLI generates two types of TypeScript definitions:

1. Schema Interface: An IntrospectedSchema interface that maps tables and columns to their ClickHouse types, used with the query builder.
2. Record Types: Type-safe interfaces for each table that map columns to their TypeScript equivalents.

Example output:

// Generated by @hypequery/clickhouse
// This file defines TypeScript types based on your ClickHouse database schema

/**
 * Schema interface for use with createQueryBuilder<IntrospectedSchema>()
 * The string literals represent ClickHouse data types for each column
 */
export interface IntrospectedSchema {
  users: {
    id: 'Int32';
    name: 'String';
    email: 'String';
    created_at: 'DateTime';
  };
  // Other tables...
}

// Type-safe record types for each table
export interface UsersRecord {
  id: number;
  name: string;
  email: string;
  created_at: string;
}
// Other record types...

Using Generated Types

Basic Usage

import { createQueryBuilder } from '@hypequery/clickhouse';
import { IntrospectedSchema } from './types/db-schema';

// Create a type-safe query builder
const db = createQueryBuilder<IntrospectedSchema>({
  host: process.env.CLICKHOUSE_HOST,
  username: process.env.CLICKHOUSE_USER,
  password: process.env.CLICKHOUSE_PASSWORD,
  database: process.env.CLICKHOUSE_DATABASE
});

// Now you have full type safety and autocomplete
async function getUsers() {
  return db.table('users')
    .select(['id', 'name', 'email'])
    .where('id', '>', 10)
    .execute();
  // Result type is inferred as { id: number, name: string, email: string }[]
}

In React Applications

With useState Hook

import { useState, useEffect } from 'react';
import { createQueryBuilder } from '@hypequery/clickhouse';
import { IntrospectedSchema, UsersRecord } from './types/db-schema';

function UsersList() {
  // Type is inferred from the query result
  const [users, setUsers] = useState<Pick<UsersRecord, 'id' | 'name'>[]>([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    const db = createQueryBuilder<IntrospectedSchema>({
      host: process.env.NEXT_PUBLIC_CLICKHOUSE_HOST!,
      username: process.env.NEXT_PUBLIC_CLICKHOUSE_USER,
      password: process.env.NEXT_PUBLIC_CLICKHOUSE_PASSWORD,
      database: process.env.NEXT_PUBLIC_CLICKHOUSE_DATABASE
    });

    async function fetchUsers() {
      setLoading(true);
      try {
        const result = await db.table('users')
          .select(['id', 'name'])
          .execute();
        setUsers(result);
      } catch (error) {
        console.error('Error fetching users:', error);
      } finally {
        setLoading(false);
      }
    }

    fetchUsers();
  }, []);

  if (loading) return <div>Loading...</div>;

  return (
    <ul>
      {users.map(user => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  );
}

With React Query

React Query (TanStack Query) provides excellent TypeScript support and can infer types from your query functions:

import { useQuery } from '@tanstack/react-query';
import { createQueryBuilder } from '@hypequery/clickhouse';
import { IntrospectedSchema } from './types/db-schema';

// Create db instance outside component to avoid recreating on each render
const db = createQueryBuilder<IntrospectedSchema>({
  host: process.env.NEXT_PUBLIC_CLICKHOUSE_HOST!,
  username: process.env.NEXT_PUBLIC_CLICKHOUSE_USER,
  password: process.env.NEXT_PUBLIC_CLICKHOUSE_PASSWORD,
  database: process.env.NEXT_PUBLIC_CLICKHOUSE_DATABASE
});

function UsersList() {
  // Types are automatically inferred from the query function
  const { data: users, isLoading, error } = useQuery({
    queryKey: ['users'],
    queryFn: () => db.table('users')
      .select(['id', 'name'])
      .execute()
  });

  if (isLoading) return <div>Loading...</div>;
  if (error) return <div>Error: {String(error)}</div>;

  return (
    <ul>
      {users?.map(user => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  );
}

Troubleshooting

Common Issues

• Connection Refused: Ensure your ClickHouse server is running and accessible from your current environment.
• Authentication Failed: Check your username and password.
• SSL/Certificate Issues: For secure connections (especially with ClickHouse Cloud), use the --secure flag.
• Database Not Found: Verify the database name is correct.

For detailed error messages and troubleshooting, run the CLI with the help flag:

npx hypequery-generate-types --help

Security Best Practices

Frontend Security Concerns

When building client-side applications like React, Next.js, or Vue apps, storing database credentials in frontend environment variables (like NEXT_PUBLIC_* variables) can pose security risks. These credentials are:

• Exposed to the client’s browser
• Potentially visible in your JavaScript bundle
• Accessible to any user of your application

Recommended Approaches

1. Backend API Layer (Recommended)

Create a backend API that handles database communication:

Client App → Backend API → ClickHouse Database

// Frontend code
import { useQuery } from '@tanstack/react-query';

function DataComponent() {
  const { data, isLoading } = useQuery({
    queryKey: ['data'],
    queryFn: () => fetch('/api/data').then(res => res.json())
  });
  
  // Render component with data...
}

// Backend API (e.g., Next.js API route, Express endpoint)
// Keep credentials in server-side environment variables
const db = createQueryBuilder({
  host: process.env.CLICKHOUSE_HOST, // Not exposed to client
  username: process.env.CLICKHOUSE_USER,
  password: process.env.CLICKHOUSE_PASSWORD,
  database: process.env.CLICKHOUSE_DATABASE
});

// API endpoint handles database queries
app.get('/api/data', async (req, res) => {
  const results = await db.table('your_table').execute();
  res.json(results);
});

2. Read-Only Access

If you must connect directly from the frontend:

1. Create a dedicated read-only database user with minimal permissions
2. Implement row-level security in ClickHouse where possible
3. Use HTTPS and ensure proper CORS configuration

3. Authentication Proxy

Deploy a service that sits between your frontend and ClickHouse:

1. Frontend authenticates with the proxy using tokens or API keys
2. Proxy validates requests and forwards them to ClickHouse
3. Proxy handles credentials securely server-side

For more information on secure application architectures, refer to our Security Guide (coming soon).