App in Node.js

• Initializing a database connection
• Creating tables
• Adding data
• Retrieving data with a Select
• Parameterized queries
• Scan queries
• Managing transactions
• Handling errors

This page contains a detailed description of the code of a test app that is available as part of the YDB Node.js SDK.

Initializing a database connection

To interact with YDB, create an instance of the driver, client, and session:

• The YDB driver lets the app and YDB interact at the transport layer. The driver must exist throughout the YDB access lifecycle and be initialized before creating a client or session.
• The YDB client runs on top of the YDB driver and enables the handling of entities and transactions.
• The YDB session contains information about executed transactions and prepared queries, and is part of the YDB client context.

App code snippet for driver initialization:

const authService = getCredentialsFromEnv();
logger.debug('Driver initializing...');
const driver = new Driver({endpoint, database, authService});
const timeout = 10000;
if (!await driver.ready(timeout)) {
    logger.fatal(`Driver has not become ready in ${timeout}ms!`);
    process.exit(1);
}

App code snippet for creating a session:

await driver.tableClient.withSession(async (session) => {
    ...
});

Creating tables

Creating tables to be used in operations on a test app. This step results in the creation of DB tables of the series directory data model:

• Series
• Seasons
• Episodes

Once the tables are created, the method for getting information about data schema objects is called and the result of its execution is output.

To create tables, use the Session.CreateTable() method:

async function createTables(session: Session, logger: Logger) {
    logger.info('Creating tables...');
    await session.createTable(
        'series',
        new TableDescription()
            .withColumn(new Column(
                'series_id',
                Types.optional(Types.UINT64),
            ))
            .withColumn(new Column(
                'title',
                Types.optional(Types.UTF8),
            ))
            .withColumn(new Column(
                'series_info',
                Types.optional(Types.UTF8),
            ))
            .withColumn(new Column(
                'release_date',
                Types.optional(Types.DATE),
            ))
            .withPrimaryKey('series_id')
    );
    await session.createTable(
        'seasons',
        new TableDescription()
            .withColumn(new Column(
                'series_id',
                Types.optional(Types.UINT64),
            ))
            .withColumn(new Column(
                'season_id',
                Types.optional(Types.UINT64),
            ))
            .withColumn(new Column(
                'title',
                Types.optional(Types.UTF8),
            ))
            .withColumn(new Column(
                'first_aired',
                Types.optional(Types.DATE),
            ))
            .withColumn(new Column(
                'last_aired',
                Types.optional(Types.DATE),
            ))
            .withPrimaryKeys('series_id', 'season_id')
    );
    await session.createTable(
        'episodes',
        new TableDescription()
            .withColumn(new Column(
                'series_id',
                Types.optional(Types.UINT64),
            ))
            .withColumn(new Column(
                'season_id',
                Types.optional(Types.UINT64),
            ))
            .withColumn(new Column(
                'episode_id',
                Types.optional(Types.UINT64),
            ))
            .withColumn(new Column(
                'title',
                Types.optional(Types.UTF8),
            ))
            .withColumn(new Column(
                'air_date',
                Types.optional(Types.DATE),
            ))
            .withPrimaryKeys('series_id', 'season_id', 'episode_id')
    );
}

You can use the Session.DescribeTable() method to output information about the table structure and make sure that it was properly created:

async function describeTable(session: Session, tableName: string, logger: Logger) {
    const result = await session.describeTable(tableName);
    for (const column of result.columns) {
        logger.info(`Column name '${column.name}' has type ${JSON.stringify(column.type)}`);
    }
}
await describeTable(session, 'series', logger);
await describeTable(session, 'seasons', logger);
await describeTable(session, 'episodes', logger);

Adding data

Adding data to the created tables using an UPSERT statement of YQL. A data update request is sent within a single request to the server with transaction auto-commit mode enabled.

Code snippet for inserting and updating data:

async function upsertSimple(session: Session, logger: Logger): Promise<void> {
    const query = `
${SYNTAX_V1}
UPSERT INTO episodes (series_id, season_id, episode_id, title) VALUES
(2, 6, 1, "TBD");`;
    logger.info('Making an upsert...');
    await session.executeQuery(query);
    logger.info('Upsert completed');
}

Retrieving data with a Select

Retrieving data using a SELECT statement in YQL. Handling the retrieved data selection in the app.

To execute YQL queries, use the Session.executeQuery() method.

async function selectSimple(session: Session, logger: Logger): Promise<void> {
    const query = `
${SYNTAX_V1}
SELECT series_id,
       title,
       release_date
FROM series
WHERE series_id = 1;`;
    logger.info('Making a simple select...');
    const {resultSets} = await session.executeQuery(query);
    const result = Series.createNativeObjects(resultSets[0]);
    logger.info(`selectSimple result: ${JSON.stringify(result, null, 2)}`);
}

Parameterized queries

Querying data using parameters. This query execution option is preferable as it allows the server to reuse the query execution plan for subsequent calls and also protects from such vulnerabilities as SQL Injection.

The code snippet below shows the use of queries prepared with Session.prepareQuery() and parameters in the Session.executeQuery() method.

async function selectPrepared(session: Session, data: ThreeIds[], logger: Logger): Promise<void> {
    const query = `
    ${SYNTAX_V1}
    DECLARE $seriesId AS Uint64;
    DECLARE $seasonId AS Uint64;
    DECLARE $episodeId AS Uint64;
    SELECT title,
           air_date
    FROM episodes
    WHERE series_id = $seriesId AND season_id = $seasonId AND episode_id = $episodeId;`;
    async function select() {
        logger.info('Preparing query...');
        const preparedQuery = await session.prepareQuery(query);
        logger.info('Selecting prepared query...');
        for (const [seriesId, seasonId, episodeId] of data) {
            const episode = new Episode({seriesId, seasonId, episodeId, title: '', airDate: new Date()});
            const {resultSets} = await session.executeQuery(preparedQuery, {
                '$seriesId': episode.getTypedValue('seriesId'),
                '$seasonId': episode.getTypedValue('seasonId'),
                '$episodeId': episode.getTypedValue('episodeId')
            });
            const result = Series.createNativeObjects(resultSets[0]);
            logger.info(`Select prepared query ${JSON.stringify(result, null, 2)}`);
        }
    }
    await withRetries(select);
}

Scan queries

Making a scan query that results in a data stream. Streaming lets you read an unlimited number of rows and amount of data.

async function executeScanQueryWithParams(session: Session, logger: Logger): Promise<void> {
    const query = `
        ${SYNTAX_V1}        
        DECLARE $value AS Utf8;
        SELECT key
        FROM ${TABLE}
        WHERE value = $value;`;
    logger.info('Making a stream execute scan query...');
    const params = {
        '$value': TypedValues.utf8('odd'),
    };
    let count = 0;
    await session.streamExecuteScanQuery(query, (result) => {
        logger.info(`Stream scan query partial result #${++count}: ${formatPartialResult(result)}`);
    }, params);
    logger.info(`Stream scan query completed, partial result count: ${count}`);
}

Managing transactions

Transactions are managed through TCL Begin and Commit calls.

In most cases, instead of explicitly using Begin and Commit calls, it’s better to use transaction control parameters in execute calls. This helps you avoid unnecessary requests to YDB and run your queries more efficiently.

Code snippet for Session.beginTransaction() and Session.commitTransaction() calls for beginning and ending a transaction:

async function explicitTcl(session: Session, ids: ThreeIds, logger: Logger) {
    const query = `
    ${SYNTAX_V1}
    DECLARE $seriesId AS Uint64;
    DECLARE $seasonId AS Uint64;
    DECLARE $episodeId AS Uint64;
    UPDATE episodes
    SET air_date = CurrentUtcDate()
    WHERE series_id = $seriesId AND season_id = $seasonId AND episode_id = $episodeId;`;
    async function update() {
        logger.info('Running prepared query with explicit transaction control...');
        const preparedQuery = await session.prepareQuery(query);
        const txMeta = await session.beginTransaction({serializableReadWrite: {}});
        const [seriesId, seasonId, episodeId] = ids;
        const episode = new Episode({seriesId, seasonId, episodeId, title: '', airDate: new Date()});
        const params = {
            '$seriesId': episode.getTypedValue('seriesId'),
            '$seasonId': episode.getTypedValue('seasonId'),
            '$episodeId': episode.getTypedValue('episodeId')
        };
        const txId = txMeta.id as string;
        logger.info(`Executing query with txId ${txId}.`);
        await session.executeQuery(preparedQuery, params, {txId});
        await session.commitTransaction({txId});
        logger.info(`TxId ${txId} committed.`);
    }
    await withRetries(update);
}

Handling errors

For more information about error handling, see Error handling in the API.