how/what to provision for a serverless rds in aws?

Hello.

I'm interested in trying out serverless infrastructure and i assume by the name , we only get charged when we run query against the database/table.

Please check the price list below.
Aurora Serverless charges for the ACUs used.
https://aws.amazon.com/rds/aurora/pricing/?nc1=h_ls

Amazon Aurora Serverless is an on-demand, auto scaling configuration that automatically adjusts database capacity based on application needs. With Aurora Serverless, your database capacity automatically scales up or down to meet your application workload needs and shuts down during periods of inactivity, saving you money and administration time. Aurora Serverless measures database capacity in Aurora Capacity Units (ACUs) billed per second. 1 ACU has approximately 2 GiB of memory with corresponding CPU and networking, similar to that used in Aurora provisioned instances.

can one connect to the database via aws cli or are there any tools recommended?

As you may know, you can use something called the Data API to execute SQL via the AWS CLI.
However, please note that as of May 2024, DataAPI is not supported in Aurora Serverless V2 MySQL.
Aurora Serverless V2 PostgreSQL allows you to use DataAPI.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html