Unleashing the Power of DuckDB with TypeScript

Table of Contents

1. Fundamental Concepts
   • What is DuckDB?
   • What is TypeScript?
   • Why Combine DuckDB and TypeScript?
2. Installation
3. Usage Methods
   • Connecting to DuckDB
   • Executing Queries
   • Working with Results
4. Common Practices
   • Data Insertion
   • Data Retrieval
   • Error Handling
5. Best Practices
   • Type Definitions for Queries
   • Performance Optimization
   • Testing
6. Conclusion
7. References

Fundamental Concepts

What is DuckDB?

DuckDB is an open - source, in - process SQL OLAP database management system. It’s optimized for analytical workloads, providing fast query execution and low - latency data processing. DuckDB can handle large datasets efficiently and is well - suited for data exploration, data science, and application embedded analytics.

What is TypeScript?

TypeScript is a programming language developed and maintained by Microsoft. It’s a superset of JavaScript, which means any valid JavaScript code is also valid TypeScript code. TypeScript adds static type checking to JavaScript, allowing developers to catch type - related errors at compile - time rather than at runtime. This leads to more reliable and maintainable code.

Why Combine DuckDB and TypeScript?

Combining DuckDB and TypeScript offers several benefits. TypeScript’s static typing helps in writing more reliable code when interacting with DuckDB. It allows developers to define types for database tables, query results, and other data structures, making the code more self - explanatory and easier to understand. Additionally, TypeScript’s tooling support, such as autocompletion and refactoring, can significantly improve the development experience when working with DuckDB.

Installation

First, make sure you have Node.js and npm (Node Package Manager) installed on your system. To install the duckdb and @types/duckdb packages, run the following command in your project directory:

npm install duckdb @types/duckdb

The duckdb package provides the core functionality for interacting with DuckDB, and @types/duckdb provides TypeScript type definitions.

Usage Methods

Connecting to DuckDB

import * as duckdb from 'duckdb';

// Create a new DuckDB database instance
const db = new duckdb.Database(':memory:');

// Open the database
db.open((err) => {
    if (err) {
        console.error('Error opening database:', err);
    } else {
        console.log('Database opened successfully');
    }
});

In this example, we create a new in - memory DuckDB database. If you want to create a persistent database, you can pass the path to a file instead of ':memory:'.

Executing Queries

db.all('CREATE TABLE users (id INTEGER, name TEXT)', (err) => {
    if (err) {
        console.error('Error creating table:', err);
    } else {
        db.all('INSERT INTO users (id, name) VALUES (1, \'John\')', (err) => {
            if (err) {
                console.error('Error inserting data:', err);
            } else {
                db.all('SELECT * FROM users', (err, rows) => {
                    if (err) {
                        console.error('Error executing query:', err);
                    } else {
                        console.log('Query results:', rows);
                    }
                });
            }
        });
    }
});

This code first creates a users table, then inserts a row into the table, and finally retrieves all rows from the table.

Working with Results

The rows variable in the previous example contains the result of the query. Each row is an object where the keys are the column names and the values are the column values.

db.all('SELECT * FROM users', (err, rows) => {
    if (!err) {
        rows.forEach((row) => {
            console.log(`User ID: ${row.id}, Name: ${row.name}`);
        });
    }
});

Common Practices

Data Insertion

const data = [
    { id: 2, name: 'Jane' },
    { id: 3, name: 'Bob' }
];

const insertQuery = 'INSERT INTO users (id, name) VALUES (?,?)';
data.forEach((user) => {
    db.run(insertQuery, [user.id, user.name], (err) => {
        if (err) {
            console.error('Error inserting user:', err);
        }
    });
});

This code inserts multiple rows into the users table using prepared statements.

Data Retrieval

db.all('SELECT * FROM users WHERE id >?', [1], (err, rows) => {
    if (!err) {
        console.log('Filtered users:', rows);
    }
});

This query retrieves all users with an id greater than 1.

Error Handling

It’s important to handle errors properly when working with DuckDB in TypeScript. In the previous examples, we used conditional statements to check for errors after each database operation. You can also use try - catch blocks in more complex scenarios.

try {
    db.all('INVALID SQL QUERY', (err, rows) => {
        if (err) {
            throw err;
        }
    });
} catch (error) {
    console.error('Database error:', error);
}

Best Practices

Type Definitions for Queries

interface User {
    id: number;
    name: string;
}

db.all<User>('SELECT * FROM users', (err, rows: User[]) => {
    if (!err) {
        rows.forEach((user: User) => {
            console.log(`User ID: ${user.id}, Name: ${user.name}`);
        });
    }
});

By defining a User interface, we can specify the type of the query results, making the code more type - safe.

Performance Optimization

• Batch Inserts: Instead of inserting data row by row, use batch inserts for better performance.

const batchData = [
    [4, 'Alice'],
    [5, 'Charlie']
];
const batchQuery = 'INSERT INTO users (id, name) VALUES ' + batchData.map(() => '(?,?)').join(',');
const flatData = batchData.flat();
db.run(batchQuery, flatData, (err) => {
    if (err) {
        console.error('Error inserting batch data:', err);
    }
});

• Indexing: Create indexes on columns that are frequently used in WHERE clauses to speed up query execution.

db.all('CREATE INDEX idx_users_id ON users (id)', (err) => {
    if (err) {
        console.error('Error creating index:', err);
    }
});

Testing

Use testing frameworks like Jest to write unit tests for your DuckDB - TypeScript code.

import * as duckdb from 'duckdb';

describe('DuckDB operations', () => {
    let db: duckdb.Database;

    beforeEach(() => {
        db = new duckdb.Database(':memory:');
        db.open();
    });

    afterEach(() => {
        db.close();
    });

    test('Should insert and retrieve data', (done) => {
        db.all('CREATE TABLE test (id INTEGER)', (err) => {
            expect(err).toBeNull();
            db.all('INSERT INTO test (id) VALUES (1)', (err) => {
                expect(err).toBeNull();
                db.all('SELECT * FROM test', (err, rows) => {
                    expect(err).toBeNull();
                    expect(rows.length).toBe(1);
                    expect(rows[0].id).toBe(1);
                    done();
                });
            });
        });
    });
});

Conclusion

Combining DuckDB with TypeScript provides a powerful and reliable way to build data - driven applications. TypeScript’s static typing enhances the code’s reliability and maintainability, while DuckDB’s performance makes it suitable for various data processing tasks. By following the usage methods, common practices, and best practices outlined in this blog, developers can efficiently use DuckDB with TypeScript and create high - quality applications.

References

• DuckDB official documentation: https://duckdb.org/docs/
• TypeScript official documentation: https://www.typescriptlang.org/docs/
• Node.js official website: https://nodejs.org/

• ←Previous
• Next→