Connect Rails to Xata

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(7,2) NOT NULL,
  rating INTEGER
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  qty INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

rails new my_xata_app --database=postgresql --api
cd my_xata_app

xata init

# .env
DATABASE_URL="postgresql://username:password@host:port/database"

xata branch url

# Gemfile
source "https://rubygems.org"

# ... existing gems ...

gem 'dotenv-rails', groups: [:development, :test]

bundle install

# config/database.yml
default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  url: <%= ENV['DATABASE_URL'] %>

test:
  <<: *default
  url: <%= ENV['DATABASE_URL'] %>

production:
  <<: *default
  url: <%= ENV['DATABASE_URL'] %>

# config/application.rb
require_relative "boot"
require "rails/all"

Bundler.require(*Rails.groups)

module MyXataApp
  class Application < Rails::Application
    config.load_defaults 7.0
    
    # Load environment variables
    Dotenv::Railtie.load if Rails.env.development? || Rails.env.test?
  end
end

rails generate model Product name:string price:decimal rating:integer
rails generate model Order created_at:datetime
rails generate model OrderItem order:references product:references qty:integer

# app/models/product.rb
class Product < ApplicationRecord
  has_many :order_items, dependent: :destroy
  has_many :orders, through: :order_items
  
  validates :name, presence: true
  validates :price, presence: true, numericality: { greater_than: 0 }
  validates :rating, numericality: { only_integer: true, greater_than: 0, less_than: 6 }, allow_nil: true
end

# app/models/order.rb
class Order < ApplicationRecord
  has_many :order_items, dependent: :destroy
  has_many :products, through: :order_items
end

# app/models/order_item.rb
class OrderItem < ApplicationRecord
  belongs_to :order
  belongs_to :product
  
  validates :qty, presence: true, numericality: { greater_than: 0 }
  validates :order_id, uniqueness: { scope: :product_id }
end

rails generate controller Api::Products index show create
rails generate controller Api::Orders index

# app/controllers/api/products_controller.rb
class Api::ProductsController < ApplicationController
  def index
    products = Product.all
    render json: products
  end

  def show
    product = Product.find(params[:id])
    render json: product
  rescue ActiveRecord::RecordNotFound
    render json: { error: 'Product not found' }, status: :not_found
  end

  def create
    product = Product.new(product_params)
    if product.save
      render json: product, status: :created
    else
      render json: { errors: product.errors.full_messages }, status: :unprocessable_entity
    end
  end

  private

  def product_params
    params.require(:product).permit(:name, :price, :rating)
  end
end

# app/controllers/api/orders_controller.rb
class Api::OrdersController < ApplicationController
  def index
    orders = Order.joins(:order_items, :products)
                  .select('orders.id as order_id, orders.created_at, products.name as product_name, order_items.qty, (products.price * order_items.qty) as total')
    
    render json: orders
  end
end

# config/routes.rb
Rails.application.routes.draw do
  namespace :api do
    resources :products, only: [:index, :show, :create]
    resources :orders, only: [:index]
  end
end

rails generate migration CreateExistingTables

# db/migrate/YYYYMMDDHHMMSS_create_existing_tables.rb
class CreateExistingTables < ActiveRecord::Migration[7.0]
  def up
    # Create products table if it doesn't exist
    unless table_exists?(:products)
      create_table :products do |t|
        t.string :name, null: false
        t.decimal :price, precision: 7, scale: 2, null: false
        t.integer :rating
        
        t.timestamps
      end
    end

    # Create orders table if it doesn't exist
    unless table_exists?(:orders)
      create_table :orders do |t|
        t.timestamps
      end
    end

    # Create order_items table if it doesn't exist
    unless table_exists?(:order_items)
      create_table :order_items do |t|
        t.references :order, null: false, foreign_key: true
        t.references :product, null: false, foreign_key: true
        t.integer :qty, null: false
        
        t.timestamps
      end
      
      add_index :order_items, [:order_id, :product_id], unique: true
    end
  end

  def down
    drop_table :order_items if table_exists?(:order_items)
    drop_table :orders if table_exists?(:orders)
    drop_table :products if table_exists?(:products)
  end
end

rails db:migrate

rails server

# Get all products
curl http://localhost:3000/api/products

# Create a new product
curl -X POST http://localhost:3000/api/products \
  -H "Content-Type: application/json" \
  -d '{"product":{"name":"Wireless Headphones","price":99.99,"rating":5}}'

# Get orders with products
curl http://localhost:3000/api/orders