@gqlite/gql2sql

gql2sql - GraphQL to SQLite Converter

A powerful TypeScript library that converts GraphQL queries to optimized SQLite SQL with automatic schema generation, relationship handling, and advanced filtering capabilities.

Features

✅ GraphQL to SQL Conversion - Convert GraphQL queries to optimized SQLite SQL
✅ Automatic Schema Generation - Generate GraphQL schema from existing SQLite databases
✅ Relationship Handling - Support for one-to-many, many-to-one relationships
✅ Deep Nesting Support - Handle complex nested queries with unlimited depth
✅ WHERE Operators - Advanced filtering with >, <, >=, <=, like, != operators
✅ SQL Injection Protection - Prepared statements with parameter binding
✅ N+1 Problem Prevention - Optimized JOIN queries to avoid N+1 issues
✅ JSON Response Format - Clean JSON output using SQLite's JSON functions

Quick Start

import { Database } from "bun:sqlite";
import { graphqlToSqlite } from "./src/gq2sql";
import { createSchemaFromDatabase } from "./src/schema-generator";

const db = new Database("database.sqlite");
const schema = await createSchemaFromDatabase(db);

const query = `
  query {
    users(where: { name: { like: "%John%" } }) {
      id
      name
      posts(where: { views: { gt: 100 } }) {
        id
        title
        views
      }
    }
  }
`;

const result = await graphqlToSqlite(query, schema, db);
console.log(JSON.stringify(result, null, 2));

WHERE Operators

Support for advanced filtering with multiple comparison operators:

Operator	SQL	Description	Example
eq	=	Equal	{ id: { eq: 1 } }
ne	!=	Not equal	{ status: { ne: "deleted" } }
gt	>	Greater than	{ views: { gt: 100 } }
gte	>=	Greater or equal	{ price: { gte: 50.0 } }
lt	<	Less than	{ age: { lt: 30 } }
lte	<=	Less or equal	{ rating: { lte: 4.5 } }
like	LIKE	Pattern matching	{ name: { like: "%John%" } }

Complex Filtering Examples

# Multiple conditions (AND)
query {
  products(where: { 
    price: { gte: 10, lte: 100 },
    category: { ne: "discontinued" },
    name: { like: "%phone%" }
  }) {
    id
    name
    price
  }
}

# Filtering with relationships
query {
  categories(where: { name: { like: "%Tech%" } }) {
    id
    name
    posts(where: { views: { gt: 500 } }) {
      id
      title
      views
      user {
        name
      }
    }
  }
}

Schema Generation

Automatically generate GraphQL schema from existing SQLite databases:

import { createSchemaFromDatabase } from "./src/schema-generator";

const dbConnection = {
  async all(sql: string): Promise<any[]> {
    return db.query(sql).all();
  },
};

const schema = await createSchemaFromDatabase(dbConnection);

The schema generator automatically detects:

• Table structures and column types
• Foreign key relationships
• Primary keys and constraints
• Nullable fields

Deep Nesting Support

Handle complex nested relationships with unlimited depth:

query {
  categories {
    id
    name
    posts {
      id
      title
      user {
        id
        name
        posts {
          id
          title
          category {
            id
            name
          }
        }
      }
    }
  }
}

SQL Generation Examples

The library generates optimized SQL with proper JOINs:

-- GraphQL: users { id, name, posts { title } }
SELECT 
  t0.id AS id, 
  t0.name AS name, 
  json_group_array(
    CASE WHEN t1.id IS NOT NULL 
    THEN json_object('title', t1.title) 
    ELSE NULL END
  ) AS posts 
FROM users AS t0 
LEFT JOIN posts AS t1 ON t0.id = t1.user_id 
GROUP BY t0.id;

Security Features

SQL Injection Prevention

All queries use prepared statements with parameter binding:

// Safe parameterized query
WHERE t0.name LIKE ? AND t0.views > ?
// Parameters: ["%John%", "100"]

Field Name Sanitization

Column names are sanitized to prevent injection:

private sanitizeFieldName(fieldName: string): string {
  return fieldName.replace(/[^a-zA-Z0-9_]/g, '');
}

Performance Optimization

Automatic JOIN Optimization

Prevents N+1 queries by generating optimal JOINs:

# Single optimized query instead of N+1
users {
  id
  posts {
    id
    category {
      name
    }
  }
}

Index Recommendations

For optimal performance, create indexes on commonly filtered fields:

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_views ON posts(views);
CREATE INDEX idx_users_name ON users(name);

Installation

# Using Bun
bun add graphql

# Using npm
npm install graphql

Usage Examples

Basic Query

const query = `
  query {
    users {
      id
      name
      email
    }
  }
`;

With Filtering

const query = `
  query {
    posts(where: { views: { gt: 100 } }) {
      id
      title
      views
    }
  }
`;

With Relationships

const query = `
  query {
    users {
      id
      name
      posts {
        id
        title
        category {
          name
        }
      }
    }
  }
`;

With Pagination

const query = `
  query {
    users(limit: 10, offset: 20) {
      id
      name
    }
  }
`;

API Reference

graphqlToSqlite(query, schema, database)

Main function to convert and execute GraphQL queries.

Parameters:

• query (string): GraphQL query string
• schema (Schema): Generated schema object
• database (Database): SQLite database instance

Returns: Promise<any[]> - Query results as JSON

createSchemaFromDatabase(dbConnection)

Generate schema from existing SQLite database.

Parameters:

• dbConnection (object): Database connection with all() method

Returns: Promise - Generated schema object

Testing

Run the test suite:

# Basic functionality
bun examples/gqlite.ts

# WHERE operators
bun test-where-operators.ts

# Advanced filtering
bun test-advanced-where.ts

# Relationship filtering
bun test-where-relations.ts

Error Handling

The library provides comprehensive error handling:

try {
  const result = await graphqlToSqlite(query, schema, db);
  console.log(result);
} catch (error) {
  console.error('GraphQL conversion error:', error.message);
}

Common error scenarios:

• Invalid GraphQL syntax
• Unknown fields in schema
• Database connection issues
• SQL generation errors

Contributing

• Fork the repository
• Create a feature branch
• Add tests for new functionality
• Ensure all tests pass
• Submit a pull request

Documentation

• WHERE Operators Guide - Comprehensive guide to filtering
• Schema Generation - Database schema detection
• Query Conversion - GraphQL to SQL conversion logic

Roadmap

🔄 In Progress:

• Advanced WHERE operators (in, notIn, isNull)
• OR condition support
• Aggregation functions (COUNT, SUM, AVG)

📋 Planned:

• Mutation support (INSERT, UPDATE, DELETE)
• Subscription support for real-time queries
• Query optimization hints
• Caching layer
• GraphQL schema validation