Xata•4mo ago

Transactions API

i'm using typescript SDK, in the transactions API, it appears to be not accepting any other types except primitive types, to create a new record with json column, i need to pass json object in a stringified version apparently for it to work

16 Replies

kostas•4mo ago

Thank you for the report, you are right and it feels like the transaction endpoint should handle objects directly just like the create and other endpoints do. I submitted an issue in the client repo for it and notified the dev team: https://github.com/xataio/client-ts/issues/1501 Feel free to add your comments there.

GitHub

Transactions do not accept JSON objects · Issue #1501 · xataio/clie...

Describe the bug Reproduced with client version: 0.29.5 The transactions endpoint does not support objects. The object must be stringified to work in a transaction. For reference, record create cal...

kostas•4mo ago

Some feedback after discussing priorities with the team: as we are currently focused on the SQL-based model with Postgres-enabled databases and SQL native transactions, we may not be adding fixes to REST API-based calls like this one in the near term

Oreki•4mo ago

then if i had to give a suggestion, any way i can convert my current database to a beta postgres enabled database?

kostas•4mo ago

The easiest way at the moment is to export each of your database tables to CSV from the Actions menu in the table view (Actions -> export full table to CSV) and Import them to the Postgres-enabled database (https://xata.io/docs/csv-data/import-data#import-a-csv-file-using-the-ui). In case the table content exceeds 1 million rows (UI limit), you can use this script to export your db to csv: https://github.com/xataio/xtools/tree/main/xreplay#converting-file-outputs-to-csv During the current Beta phase, there isn't a built-in migration option to convert a db to Postgres-enabled, but a migration path will be available later down the road around GA time.

Oreki•4mo ago

Also do you mind if i post another bug here? related to typescript SDK, i am unable to filter based on xata meta records based on dates

kostas•4mo ago

Sure, can you give an example query?

Oreki•4mo ago

yes i'll show you my whole usecase

const users = await variables.database.db.rolls
        .filter({
          remaining: {
            $lt: 30,
          },
          xata: {
            updatedAt: {
              $gt: dayjs().subtract(3, 'hour').toDate(),
            },
          },
        })
        .getAll();

      const chunks = chunk(users, 1000);

      for await (const chunked of chunks) {
        const mapped = chunked.map(
          (user) =>
            ({
              update: {
                table: 'rolls',
                id: user.id,
                fields: {
                  remaining: {
                    $increment: 1,
                  },
                },
              },
            }) as const,
        );

        await variables.database.transactions.run([...mapped]);
      }

const users = await variables.database.db.rolls
        .filter({
          remaining: {
            $lt: 30,
          },
          xata: {
            updatedAt: {
              $gt: dayjs().subtract(3, 'hour').toDate(),
            },
          },
        })
        .getAll();

      const chunks = chunk(users, 1000);

      for await (const chunked of chunks) {
        const mapped = chunked.map(
          (user) =>
            ({
              update: {
                table: 'rolls',
                id: user.id,
                fields: {
                  remaining: {
                    $increment: 1,
                  },
                },
              },
            }) as const,
        );

        await variables.database.transactions.run([...mapped]);
      }

Basically, i need to update every single row in rolls table, if it had been updated recently (3 hours) this results into

Error: filter invalid: column [xata.updatedAt]: type mismatch: expected datetime (A timestamp string representation in RFC 3339 format (e.g. 2022-05-22T16:34:54.123Z))

Error: filter invalid: column [xata.updatedAt]: type mismatch: expected datetime (A timestamp string representation in RFC 3339 format (e.g. 2022-05-22T16:34:54.123Z))

during the filter query itself

kostas•4mo ago

checking The general expression would be along the lines:

await xata.db.rolls
        .filter({
          $all: [
            {
              remaining: {
                $lt: 30,
              }
            },
            {
              'xata.updatedAt': { $gt: new Date('2022-10-25T02:00:00Z') }
            }
          ]
        })
        .getAll();

await xata.db.rolls
        .filter({
          $all: [
            {
              remaining: {
                $lt: 30,
              }
            },
            {
              'xata.updatedAt': { $gt: new Date('2022-10-25T02:00:00Z') }
            }
          ]
        })
        .getAll();

Need to check the return type of dayjs.toDate vs the Date type expected by xata date fields

Oreki•4mo ago

dayjs.toDate() returns a Date, also wrapping this around $all seems to work

kostas•4mo ago

ah great, so it just needed the all clause to match things properly Was just about to test it more but sounds like it worked? Let me know if you hit more problems

Oreki•4mo ago

thank you very much for the support, currently i am happy with xata's http usage so i don't think i'll move to postgres version anytime soon also because i don't wanna use an orm and add addiotional deps since i'm on serverless environment the SDK itself is pretty much capable of doing everything

kostas•4mo ago

As long as the REST API has the features you need, there's no need to start thiking about moving over. There's always the option of running SQL over HTTP with the xata.sql call in the current prod databases (https://xata.io/docs/sdk/sql/overview) so you can still use it as an escape hatch if you step on limitations with the REST API's expressions. The Postgres-enabled dbs provide an extra escape hatch that is going down to the PG Wire protocol so you have even more freedom. These dbs also work with the SDK as well (with some minor changes for internal field names xata_ instead of xata. and xata_id instead of id) . We're currently focusing on building Wire protocol compatibility with ORMs, but if you don't have a need for that and can do with the SDK, then no worry. For the record, you will find a reference for nested expressions with more complicated and/or/not logic here, I guess you may need it eventually: https://xata.io/docs/sdk/filtering#nested-operators

Oreki•4mo ago

ohh i havent played with the sql over HTTP part yet but if that works for me, i'd definetly change it to that

kostas•4mo ago

Yes it is good to know that it exists as an option 🙂

Oreki•4mo ago

turns out i could have just used the sql over HTTP feature much more performant this way, thank you!

Gaming

Programming

Transactions API