hi im just testing xata, i need to
hi im just testing xata, i need to create a inventory management system for my business, you guys recommend me xata? xata have branches? like one branch for dev and another for staging and one for prod?
46 Replies
Hi and welcome! What languages and frameworks are you planning to use?
Xata offers SDKs for TypeScript and Python and is well tested with frontend frameworks such as NextJS.
Xata does have branches! Every database comes with the default
main branch, but you can create new branches and perform migrations or merge them together from the UI or CLI (xata pull / xata push commands).
In case you'd prefer to use SQL, then that's possible as well from the SDK or REST API https://xata.io/docs/sdk/sql/overview.
We also offer a new Beta database type that is Postgres-Enabled so you can connect from the Wire protocol (meaning, using any client that supports native postgres connectivity) - but be aware that this database type doesn't support merging branches yet.
The Free plan allows you to create 10 branches in your Workspacehi i will use react so i just need rest part of the db
Great, then the TypeScript SDK will be a good fit for your use case.
Each branch acts as a standalone instance of a database in terms of rate limits (https://xata.io/docs/concepts/pricing#concurrency-limit) so you can work with dev/staging without affecting production workloads (main branch)
and what if i have already a postgresql db with defined columns, how i move all those and the data to xata?
With the current production dbs, the easiest way to migrate from Postgres is with CSV export/import. Both the Xata UI and the CLI offer CSV import options (https://xata.io/docs/csv-data/import-data).
With the Beta postgres-enabled databases, you can use standard postgres tooling (pg_dump/pg_restore https://xata.io/docs/postgres#export) to bring your data to Xata. Note that you will additionally neeed to "Adapt your tables" in the Xata UI if you use this method, for the REST API to work. This introduces additional xata-internal columns to your tables, which are necessary for the SDK and Xata's added features (search, aggregations).
sounds good, and how i can manage join two tables with xata?
You can either
- do joins using actual SQL (it's possible even with the TS SDK, proxied over HTTP)
- use Xata's special-purpose link column type: https://xata.io/docs/concepts/data-model#link which links records between tables based on the record id.
how i do it with actual sql? or the question is which method you recommend
im using rest to get data and fill a table in rest
in react
There are various options:
- We have a Kysely integration for typed SQL JOIN statements. https://xata.io/docs/sdk/get#many-to-many-relationships
- If you don't mind about types, use plain SQL over HTTP:
https://xata.io/docs/sdk/sql/overview
SELECT vehiculos.id,vehiculos.marca,vehiculos.modelo,vehiculos.year,vehiculos.placa,vehiculos.vin,vehiculos.imagen,CONCAT(users.first_name, ' ' , users.last_name) as asignado,users.id as idcolaborador,seguro_expiracion,vehiculos.usuario_crea
FROM vehiculos JOIN users ON users.id = vehiculos.asignado_user_id order by id i got this already but i want to use rest with the link table column i already got users table in xata
FROM vehiculos JOIN users ON users.id = vehiculos.asignado_user_id order by id i got this already but i want to use rest with the link table column i already got users table in xata
In order to create a link column, linking a user record to a vehiculos record, the vehiculos table needs to be created first and each record in it must have an id column. If you import from CSV and your file already has an id column, xata will use that, otherwise it adds an id column and autogenerates values for each row.
Then, the id values of vehiculos records need to be set in the users table, in the link column that links a user to a vehicle. It cannot happen on-the-fly, relations need to be established by inserting an id of a vehicle to the relevant column of each user record.
Alternatively, with plain SQL you can do the join exactly as you described - without using the link column.
Yea inside vehiculos table i already got a column name asignado_id and that id is linked to the column id from users table
Thats why this query works good
Great, then you can use a call like this to "expand" a vehiculos record to bring the linked user content:
https://xata.io/docs/sdk/get#selecting-columns-from-the-linked-tables
You can also do the reverse, find a linking vehicule from the users table with the backwards relation operator
<-
https://xata.io/docs/concepts/data-model#links-and-relationshipsbut with rest how i can do it?
first i need to create inside xata a link table column?
something like this?
yes, you need to create the link column in your Xata schema like this
its done, and this is the part where im confused
how i then show that in rest
sorry to ask too much
Can you clarify what do you mean by showing it in rest, do you mean how to get it with a REST API call?
thats it yes
https://alexis-gaitan-s-workspace-r0aiag.us-east-1.xata.sh/db/dbsnetworkdb:main/tables/vehiculos/query i use this
"records": [
{
"asignado_user_id": 2,
"fecha_actualiza": "2023-11-30T16:55:26Z",
"fecha_crea": "2023-11-14T18:02:33Z",
"id": "1",
"imagen": "https://dbsnetworkimagenes.s3.amazonaws.com/vehiculos/2016_Toyota_HiLux_%28GUN136R%29_SR5_4-door_utility_%282018-09-03%29_01.jpg",
"imagen_name": "2016_Toyota_HiLux_Invincible_D-4D_4WD_2.4_Front.jpg",
"marca": "Toyota",
"modelo": "Hilux",
"placa": "BU895",
"seguro_expiracion": "2023-11-30T00:00:00Z",
"usuario_actualiza": "Alexis Gaitan",
"usuario_crea": "Alexis Gaitan",
"vin": "455878855",
"xata": {
"createdAt": "2024-05-24T14:50:23.303267Z",
"updatedAt": "2024-05-24T14:50:23.303267Z",
"version": 0
},
"year": 2020
},
Great, there's a helper tool in the table UI in the button "Get code snippet". It shows you hw to get the current results view with various methods (SDKs or rest). Choose "shell" there and you will see the REST API call to bring the same results.
Basically, you just need to include the linked field with an asterisk in your requested columns as :
asignado.*{"columns":["xata_id","asignado_user_id","fecha_actualiza","fecha_crea","imagen","imagen_name","marca","modelo","placa","seguro_expiracion","usuario_actualiza","usuario_crea","vin","year"],"page":{"size":15}} in here?
in the body of json rest
Yes. By default it will only bring the exact value of the linked column. You need to explictly ask it to "expand" to bring the entire linked record.
https://xata.io/docs/sdk/get#selecting-columns-from-the-linked-tables
See the "JSON" tab in the linked examples
{"columns":["xata_id","asignado_user_id","fecha_actualiza","fecha_crea","imagen","imagen_name","marca","modelo","placa","seguro_expiracion","usuario_actualiza","usuario_crea","vin","year","asignado."],"page":{"size":15}}
in theory i got asignado.
"message":string"columns invalid: column [asignado.]: column not found"
You need to use the name of the link column - I said "asignado" because that's what was shown in your screenshot above 😄 What's the link column named?
yeah same name
but its not showing just and id col
Seems that you're using a Postgres Beta database right? In that case you must either have a xata_id or an id column, not both
Otherwise the REST API gets them mixed up
Do your tables have both
id and xata_id ?
For the rest API to work well you'll need to use just xata_idonly xata_id
The REST API returns xata_id values as "id"
Can you enable the "Allow support to access your workspace" toggle so I can have a quick look?
It's in the workspace settings
done
on it
Workspace name
Alexis Gaitan's workspace
Workspace slug
Alexis-Gaitan-s-workspace-r0aiag
i just removed the asignado link table
ON users.id = vehiculos.asignado_user_id order by id but at the end is like that the vehiculos_Asignado_userid should be the id of user table
not sure if im being clear
so i already had a asignaedo_user_id so i suppouse i need to recreate that to use as link column
So what we're looking for, is a one-to-one relationship between vehiculos and users, right?
one record in vehiculos would be linked to one user?
Or would you need to link one vehicle to many users, or even many users to many vehicles?
yeah for example in vehicles if i got user_id = 2, then the assigned user is Carlos that have id = 2 in user table
one to one only
Great, then this is a perfect use case for a link column
Can I create a link column in your db and provide the relevant rest call?
yeah i got already one asignaedo_user_id but it doesnt have any linked col
you can create a new column for sure
i saw you did it, works good, but how i can join first and last name with resT?
from asignado* i just need first_name last_name
in one column
I added an asignado column to the vehiculos table and set the same value (2) as asignado_user_id
So the REST API call looks like this (giving curl example, you can adapt it to your REST client accordingly)
Similarly, the same can be done with SQL over HTTP without necessarily relying on the link column (just for reference, you can use asignado_user_id for the join instead):
ok but could be id 1 too, not only 2
yes, you can change the values as you wish. I set id 2 just to create some links as examples
When the link value is updated, the vehiculos record is linked to another user record
o i see, awesome, i like xata, will keep trying so i can set a paid plan after, thank you for the great support
I'll be happy to help if you have any questions anytime
hi me again, in rest api theres any field with something like row count? to show how many records i have ?
so i can use that as pagination
Hey, no there isn't a total result count in the query response. You'll need to paginate until meta.page.more is false in the response, see: https://xata.io/docs/sdk/get#cursor-based-pagination
In case you need to show the total number of results in your app, you'd have to do a separate summarize count call to get the number of results https://xata.io/docs/sdk/summarize#count-all-records-of-a-table
awesome that works, thank you!