Database Triggers

Database triggers write your scraped data directly to databases. Instead of exporting data to a file and then manually importing it, database triggers insert, update, or query data automatically after each run.

Database triggers are essential for teams that need to store scraped data in a structured, queryable format. Whether you’re using PostgreSQL, MySQL, or MongoDB, RTILA X can write your data directly to your database.

PostgreSQL

The PostgreSQL trigger executes SQL queries against a PostgreSQL database. PostgreSQL is a powerful, open-source relational database used by millions of applications worldwide.

Description

The PostgreSQL trigger connects to a PostgreSQL database and executes a SQL query. You can use INSERT, UPDATE, SELECT, or any other SQL statement. The query result is stored in an output variable.

Parameters

Parameter	Type	Required	Default	Description
host	string	✅ Yes	“”	The database server hostname or IP address. Example: localhost, db.example.com, 192.168.1.100.
port	number	✅ Yes	5432	The database port. PostgreSQL default is 5432.
user	string	✅ Yes	“”	The database username.
password	string	✅ Yes	“”	The database password.
database	string	✅ Yes	“”	The database name.
query	string	✅ Yes	“”	The SQL query to execute. Supports ${variable} substitution.

Example: Insert Scraped Data

{
  "host": "localhost",
  "port": 5432,
  "user": "scraper",
  "password": "${db_password}",
  "database": "scraping_data",
  "query": "INSERT INTO products (name, price, url, scraped_at) VALUES ('${product_name}', ${product_price}, '${product_url}', NOW())"
}

Example: Query Data

{
  "host": "localhost",
  "port": 5432,
  "user": "scraper",
  "password": "${db_password}",
  "database": "scraping_data",
  "query": "SELECT COUNT(*) as total_products FROM products WHERE scraped_at > NOW() - INTERVAL '1 day'"
}

Response Format

The PostgreSQL trigger stores the query result in the output variable. For SELECT queries, the result is an array of row objects. For INSERT/UPDATE/DELETE, the result is the number of affected rows.

Tips

• Use parameterized queries to prevent SQL injection. However, RTILA X uses string interpolation, so be careful with user-provided data.
• Use ${variable} substitution to make your queries dynamic.
• Always test your SQL queries in a database client before adding them to RTILA X.
• Use transactions for multi-statement operations to ensure data integrity.
• Create the table before running the trigger if it doesn’t exist.

MySQL

The MySQL trigger executes SQL queries against a MySQL database. MySQL is the world’s most popular open-source relational database, used by websites and applications of all sizes.

Description

The MySQL trigger connects to a MySQL database and executes a SQL query. You can use INSERT, UPDATE, SELECT, or any other SQL statement. The query result is stored in an output variable.

Parameters

Parameter	Type	Required	Default	Description
host	string	✅ Yes	“”	The database server hostname or IP address.
port	number	✅ Yes	3306	The database port. MySQL default is 3306.
user	string	✅ Yes	“”	The database username.
password	string	✅ Yes	“”	The database password.
database	string	✅ Yes	“”	The database name.
query	string	✅ Yes	“”	The SQL query to execute. Supports ${variable} substitution.

Example: Insert Scraped Data

{
  "host": "localhost",
  "port": 3306,
  "user": "scraper",
  "password": "${db_password}",
  "database": "scraping_data",
  "query": "INSERT INTO products (name, price, url, scraped_at) VALUES ('${product_name}', ${product_price}, '${product_url}', NOW())"
}

Example: Upsert Data (Insert or Update)

{
  "host": "localhost",
  "port": 3306,
  "user": "scraper",
  "password": "${db_password}",
  "database": "scraping_data",
  "query": "INSERT INTO products (id, name, price) VALUES ('${product_id}', '${product_name}', ${product_price}) ON DUPLICATE KEY UPDATE name='${product_name}', price=${product_price}"
}

Tips

• MySQL uses backticks for identifiers: `table_name` instead of "table_name".
• Use ON DUPLICATE KEY UPDATE for upsert operations (insert or update).
• MySQL’s default port is 3306, not 5432 (which is PostgreSQL’s default).
• Use ${variable} substitution to make your queries dynamic.
• Always test your SQL queries in a MySQL client before adding them to RTILA X.

MongoDB

The MongoDB trigger performs operations on a MongoDB database. MongoDB is a popular NoSQL database that stores data as JSON-like documents instead of tables.

Description

The MongoDB trigger connects to a MongoDB database and performs an operation: insert a document, find documents, or update documents. The result is stored in an output variable.

Parameters

Parameter	Type	Required	Default	Description
connection_string	string	✅ Yes	“”	The MongoDB connection URI. Example: mongodb://localhost:27017/scraping_data or mongodb+srv://user:pass@cluster.mongodb.net/scraping_data.
database	string	✅ Yes	“”	The database name.
collection	string	✅ Yes	“”	The collection name (equivalent to a table in SQL).
operation	string	✅ Yes	“insert_one”	The operation to perform. Options: insert_one, find_one.
filter	object	No	{}	The filter criteria for find operations. Example: {"category": "electronics"}.
document	object	No	{}	The document to insert for insert operations. Supports ${variable} substitution.

Example: Insert a Document

{
  "connection_string": "mongodb://localhost:27017",
  "database": "scraping_data",
  "collection": "products",
  "operation": "insert_one",
  "document": {
    "name": "${product_name}",
    "price": "${product_price}",
    "url": "${product_url}",
    "scraped_at": "${date}"
  }
}

Example: Find a Document

{
  "connection_string": "mongodb://localhost:27017",
  "database": "scraping_data",
  "collection": "products",
  "operation": "find_one",
  "filter": {
    "category": "electronics",
    "price": { "$lt": 50 }
  }
}

Response Format

Operation	Result
insert_one	The inserted document’s ID
find_one	The found document, or null if not found

Tips

• MongoDB uses JSON-like documents instead of SQL tables.
• Use MongoDB’s query operators ($lt, $gt, $in, etc.) in filters for complex queries.
• The connection string format depends on your MongoDB setup:
  • Local: mongodb://localhost:27017
  • Atlas (cloud): mongodb+srv://user:pass@cluster.mongodb.net/database
• Use ${variable} substitution in documents to make your inserts dynamic.
• MongoDB automatically creates collections and databases if they don’t exist.

Choosing the Right Database Trigger

Scenario	Recommended Trigger
Already using PostgreSQL	PostgreSQL
Already using MySQL	MySQL
Already using MongoDB	MongoDB
Need relational data with complex queries	PostgreSQL
Need a simple, widely-supported database	MySQL
Need flexible, schema-less data storage	MongoDB
Working with JSON data	MongoDB
Need ACID transactions	PostgreSQL or MySQL

Common Database Patterns

Pattern 1: Insert After Each Run

Insert scraped data into a database after every run:

Project Run → Extract Data → Insert into PostgreSQL

Pattern 2: Upsert for Deduplication

Use upsert operations to avoid duplicate records:

Project Run → Extract Data → Upsert into MySQL (INSERT ... ON DUPLICATE KEY UPDATE)

Pattern 3: Query Before Insert

Check if data exists before inserting:

Project Run → Extract Data → Query Database → If not exists → Insert

Pattern 4: Store JSON Documents

Store scraped data as JSON documents in MongoDB:

Project Run → Extract Data → Insert JSON Document into MongoDB

Error Handling

Error	Cause	Solution
Connection refused	Database server is unreachable	Check the host, port, and firewall settings
Authentication failed	Wrong username or password	Verify your database credentials
Database not found	Database doesn’t exist	Create the database before running
Table not found	Table doesn’t exist	Create the table before running
Syntax error	Invalid SQL query	Check your SQL syntax
Permission denied	Insufficient database permissions	Grant the necessary permissions to your database user
Duplicate key	Unique constraint violation	Use upsert operations or handle duplicates

Security Best Practices

Practice	Why It Matters
Use environment variables for credentials	Avoid hardcoding credentials in your project configuration
Use least-privilege database users	Grant only the minimum permissions needed (e.g., INSERT-only for insert-only triggers)
Use SSL/TLS for connections	Encrypt data in transit
Use parameterized queries	Prevent SQL injection attacks
Regular database backups	Protect against data loss
Monitor database performance	Ensure your queries don’t overload the database

SQL Injection Warning

Warning: RTILA X uses string interpolation (${variable}) for SQL queries, not parameterized queries. This means you must be careful with user-provided data to prevent SQL injection attacks.

If you’re inserting data that comes from user input or untrusted sources, sanitize the data before using it in SQL queries. For example, escape single quotes in string values.

For maximum security, consider using MongoDB (which uses JSON documents instead of SQL) or using a stored procedure that accepts parameters.