Cannot create index on non-empty table

Question

I'm currently using AWS Lambda (NodeJS) with AWS QLDB.

The scenario is like this. I have the first table and its indexes when I deployed the service. So the table and indexes will be created. My problem is that, once I need to add new table and its indexes; it can't create the index because there's existing table.

My workaround to be able to create new table even if there's an existing table in my Ledger is that I'm querying the list of tables I have.

const getTables = async (transactionExecutor: TransactionExecutor) => {
  const statement = `SELECT name FROM information_schema.user_tables`;
  return await transactionExecutor.execute(statement);
};

Then I have this condition to check if the table is already existing

const tables = JSON.stringify(result.getResultList());

            if (
              !JSON.parse(tables).some((object): boolean => object.name === process.env.TABLE_NAME)
            ) {
              console.log('TABLE A NOT EXISTING');
              await createTable(transactionExecutor, process.env.TABLE_NAME);
            }
            if (
              !JSON.parse(tables).some(
                (object): boolean => object.name === process.env.TABLE_NAME_1,
              )
            ) {
              console.log('TABLE B NOT EXISTING');
              await createTable(transactionExecutor, process.env.TABLE_NAME_1);
            }

I don't know how to do it with indexes, I tried using SQL commands in QLDB but it's not working. I hope you can help me.

Thank you

Answer 1

I'm not quite sure what your question is (the post title and body hint at different things), but I'm going to do my best to answer.

First, QLDB stores data in Ion, not JSON. So, please use the Ion APIs to parse data and not the JSON ones. The reason your code works at all is because Ion is a superset of JSON and the result set doesn't include types that are unknown to JSON. So, for example, if the result set was changed to include an Ion Timestamp, then your code would break.

Next, actually getting a list of tables has first class support in the driver. Simply use driver.getTableNames.

Third, I think you have a question "can I add an index to a non-empty table?". The answer is "no". This is planned functionality and I will update this answer when it is available. UPDATE: Now you can! https://aws.amazon.com/about-aws/whats-new/2020/09/amazon-qldb-launches-index-improvements/

Finally, I think you're also asking if there is a way to list indexes on a table in the same way as you can list tables in a ledger. The answer to that is 'yes'. The documents returned in information_schema.user_tables look like this:

{
  tableId:"...",
  name:"THE_TABLE_NAME",
  indexes:[
    {
      expr:"[THE_FIELD_BEING_INDEXED]"
    }
  ],
  status:"ACTIVE"
}