Database Setup

The Sistema de Gestión de Propiedades API uses Cloudflare D1, a serverless SQLite database that runs on Cloudflare’s edge network.

What is D1?

Cloudflare D1 is a distributed SQLite database:

Serverless - No servers to manage
Global - Replicated to Cloudflare’s edge network
SQL - Full SQLite compatibility
Integrated - Native Workers integration

Database Schema

The database has a single propiedades table:

CREATE TABLE propiedades (
  codigo_id VARCHAR(6) PRIMARY KEY,
  pais VARCHAR(100),
  ciudad VARCHAR(100),
  direccion VARCHAR(255),
  ambientes INTEGER,
  metros_cuadrados DECIMAL(10,2),
  precio DECIMAL(15,2),
  tipo_contratacion TEXT CHECK(tipo_contratacion IN ('Alquiler', 'Venta')),
  estado TEXT CHECK(estado IN ('Disponible', 'Reservado', 'Alquilado', 'Vendido')),
  descripcion TEXT,
  fecha_publicacion DATE DEFAULT CURRENT_DATE
);

Source: Backend/schema.sql

Table Structure

Column	Type	Constraints	Description
codigo_id	VARCHAR(6)	PRIMARY KEY	Unique property identifier
pais	VARCHAR(100)		Country
ciudad	VARCHAR(100)		City
direccion	VARCHAR(255)		Street address
ambientes	INTEGER		Number of rooms
metros_cuadrados	DECIMAL(10,2)		Square meters
precio	DECIMAL(15,2)		Price
tipo_contratacion	TEXT	CHECK constraint	”Alquiler” or “Venta”
estado	TEXT	CHECK constraint	Property status
descripcion	TEXT		Optional description
fecha_publicacion	DATE	DEFAULT CURRENT_DATE	Publication date

Constraints

PRIMARY KEY: codigo_id ensures each property has a unique identifier
CHECK constraints: Enforce valid enum values for tipo_contratacion and estado
DEFAULT: fecha_publicacion automatically set to current date

Creating the Database

Create D1 database

wrangler d1 create propiedades-db

Output:

✅ Successfully created DB 'propiedades-db'

[[d1_databases]]
binding = "DB"
database_name = "propiedades-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

Copy the database_id for the next step.

Update wrangler.toml

Add the database configuration:

[[d1_databases]]
binding = "DB"
database_name = "propiedades-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

Run schema migration

wrangler d1 execute propiedades-db --file=./schema.sql

This creates the propiedades table.

Verify table creation

wrangler d1 execute propiedades-db --command="SELECT name FROM sqlite_master WHERE type='table'"

Should output: propiedades

Database Operations

Query the Database

# List all properties
wrangler d1 execute propiedades-db --command="SELECT * FROM propiedades"

# Count properties
wrangler d1 execute propiedades-db --command="SELECT COUNT(*) FROM propiedades"

# Get specific property
wrangler d1 execute propiedades-db --command="SELECT * FROM propiedades WHERE codigo_id='ZN1001'"

Insert Sample Data

Create a file sample-data.sql:

INSERT INTO propiedades (
  codigo_id, pais, ciudad, direccion, ambientes,
  metros_cuadrados, precio, tipo_contratacion, estado, descripcion
) VALUES (
  'ZN1001',
  'Argentina',
  'Tigre',
  'Av. Cazón 123',
  3,
  75.5,
  120000,
  'Venta',
  'Disponible',
  'Hermosa vista al río'
),
(
  'BA4567',
  'Argentina',
  'Buenos Aires',
  'Av. Corrientes 1234',
  2,
  65,
  95000,
  'Alquiler',
  'Disponible',
  'Departamento luminoso en el centro'
);

Execute it:

wrangler d1 execute propiedades-db --file=./sample-data.sql

Update Records

# Update property status
wrangler d1 execute propiedades-db --command="
  UPDATE propiedades 
  SET estado='Vendido' 
  WHERE codigo_id='ZN1001'
"

# Update price
wrangler d1 execute propiedades-db --command="
  UPDATE propiedades 
  SET precio=125000 
  WHERE codigo_id='ZN1001'
"

Delete Records

# Delete specific property
wrangler d1 execute propiedades-db --command="
  DELETE FROM propiedades 
  WHERE codigo_id='ZN1001'
"

# Clear all data (careful!)
wrangler d1 execute propiedades-db --command="DELETE FROM propiedades"

Local vs Production

Local Development Database

When running wrangler dev, a local SQLite database is used:

Located in .wrangler/state/v3/d1/
Independent from production
Persists between dev server restarts

Production Database

The production D1 database:

Globally distributed
Persistent across deployments
Accessed via Cloudflare’s network

Changes to the local dev database don’t affect production. Always run migrations in both environments.

Database Migrations

Running Migrations

Local
Production

wrangler d1 execute propiedades-db --local --file=./schema.sql

wrangler d1 execute propiedades-db --file=./schema.sql

Migration Workflow

Create migration file

Create migrations/001_add_column.sql:

ALTER TABLE propiedades ADD COLUMN nueva_columna TEXT;

Test locally

wrangler d1 execute propiedades-db --local --file=./migrations/001_add_column.sql

Verify locally

wrangler dev
# Test the API

Apply to production

wrangler d1 execute propiedades-db --file=./migrations/001_add_column.sql

Backup and Restore

Export Database

# Export to SQL file
wrangler d1 execute propiedades-db --command=".dump" > backup.sql

# Export specific table
wrangler d1 execute propiedades-db --command=".dump propiedades" > propiedades-backup.sql

Import Database

wrangler d1 execute propiedades-db --file=./backup.sql

Database Inspection

View Schema

# Show table schema
wrangler d1 execute propiedades-db --command=".schema propiedades"

# List all tables
wrangler d1 execute propiedades-db --command="
  SELECT name FROM sqlite_master WHERE type='table'
"

Database Statistics

# Count records
wrangler d1 execute propiedades-db --command="
  SELECT COUNT(*) as total FROM propiedades
"

# Properties by status
wrangler d1 execute propiedades-db --command="
  SELECT estado, COUNT(*) as count 
  FROM propiedades 
  GROUP BY estado
"

# Properties by city
wrangler d1 execute propiedades-db --command="
  SELECT ciudad, COUNT(*) as count 
  FROM propiedades 
  GROUP BY ciudad
"

Programmatic Access

The API accesses D1 through the Workers binding:

// From Backend/src/controllers/propiedades.controller.ts

// List all properties
export const getAllPropiedades = async (c: Context) => {
  const { results } = await c.env.DB.prepare(
    'SELECT * FROM propiedades'
  ).all();
  return c.json(results);
};

// Get one property
export const getPropiedadById = async (c: Context) => {
  const id = c.req.param('id');
  const propiedad = await c.env.DB.prepare(
    'SELECT * FROM propiedades WHERE codigo_id = ?'
  ).bind(id).first();
  
  if (!propiedad) {
    return c.json({ error: 'Propiedad no encontrada' }, 404);
  }
  return c.json(propiedad);
};

// Insert property
await c.env.DB.prepare(`
  INSERT INTO propiedades (...) VALUES (...)
`).bind(...values).run();

// Update property
await c.env.DB.prepare(`
  UPDATE propiedades SET ... WHERE codigo_id = ?
`).bind(...values).run();

// Delete property
await c.env.DB.prepare(
  'DELETE FROM propiedades WHERE codigo_id = ?'
).bind(id).run();

Monitoring

View Database Metrics

In the Cloudflare Dashboard:

Navigate to Workers & Pages
Select D1
Choose your database

View:

Query count
Storage size
Read/write operations

Query Logs

# Tail Worker logs to see database queries
wrangler tail

Limits

Cloudflare D1 limits (as of 2024):

Resource	Free Plan	Paid Plan
Databases	10	Unlimited
Storage per DB	500 MB	10 GB
Reads per day	5 million	Unlimited
Writes per day	100,000	Unlimited

See Cloudflare D1 Pricing for details.

Best Practices

Use prepared statements

Always use .bind() for user input to prevent SQL injection:

// Good
await db.prepare('SELECT * FROM propiedades WHERE codigo_id = ?')
  .bind(userInput).first();

// Bad - vulnerable to SQL injection
await db.prepare(`SELECT * FROM propiedades WHERE codigo_id = '${userInput}'`).first();

Test migrations locally

Always test schema changes with --local before applying to production.

Regular backups

Export your database regularly:

wrangler d1 execute propiedades-db --command=".dump" > backup-$(date +%Y%m%d).sql

Monitor query performance

Check slow queries in Wrangler logs and add indexes if needed.

Troubleshooting

Table doesn't exist

Error: no such table: propiedadesSolution:

wrangler d1 execute propiedades-db --file=./schema.sql

Binding not found

Error: DB is not definedSolution: Check wrangler.toml has the D1 binding:

[[d1_databases]]
binding = "DB"
database_name = "propiedades-db"
database_id = "your-id"

Local and production out of sync

Problem: Different data in dev vs productionSolution: Export production, import to local:

# Export from production
wrangler d1 execute propiedades-db --command=".dump" > prod-data.sql

# Import to local
wrangler d1 execute propiedades-db --local --file=./prod-data.sql

Next Steps

Environment Variables

Configure authentication credentials

Cloudflare Workers

Deploy the complete application

Property Schema

Detailed schema documentation

Creating Properties

Start adding properties to your database

Get Started

Authentication

Guides

Deployment

​Database Setup

​What is D1?

​Database Schema

​Table Structure

​Constraints

​Creating the Database

​Database Operations

​Query the Database

​Insert Sample Data

​Update Records

​Delete Records

​Local vs Production

​Local Development Database

​Production Database

​Database Migrations

​Running Migrations

​Migration Workflow

​Backup and Restore

​Export Database

​Import Database

​Database Inspection

​View Schema

​Database Statistics

​Programmatic Access

​Monitoring

​View Database Metrics

​Query Logs

​Limits

​Best Practices

​Troubleshooting

​Next Steps

Environment Variables

Cloudflare Workers

Property Schema

Creating Properties

Build docs developers (and LLMs) love

Database Setup

What is D1?

Database Schema

Table Structure

Constraints

Creating the Database

Database Operations

Query the Database

Insert Sample Data

Update Records

Delete Records

Local vs Production

Local Development Database

Production Database

Database Migrations

Running Migrations

Migration Workflow

Backup and Restore

Export Database

Import Database

Database Inspection

View Schema

Database Statistics

Programmatic Access

Monitoring

View Database Metrics

Query Logs

Limits

Best Practices

Troubleshooting

Next Steps