Skip to main content

SQL INSERT Query with RETURNING Clause

Description​

This query demonstrates a direct SQL INSERT operation using the stack9_db connector, showcasing the pattern for creating new records directly in the database while immediately retrieving the generated ID. The RETURNING clause is a PostgreSQL-specific feature that allows INSERT, UPDATE, and DELETE statements to return data from the affected rows, eliminating the need for a separate SELECT query to get the newly created record's ID.

The query uses parameterized SQL with double curly brace syntax for safe value injection, preventing SQL injection attacks while maintaining query flexibility. All six fields are parameterized, allowing the query to be reused across different contexts with different values. This pattern is particularly useful when you need fine-grained control over the INSERT operation or when working with database-specific features that aren't easily expressed through an ORM.

This approach bypasses the Stack9 API layer entirely, writing directly to the database. While this provides maximum performance and control, it also means that any API-level validations, hooks, or side effects won't be triggered. This makes it suitable for bulk operations, data migrations, or scenarios where the overhead of the API layer needs to be avoided.

Use Case​

Used in the recruitment system when creating job applications through an integration workflow, where multiple related records need to be created in a specific sequence and the generated IDs need to be immediately available for creating related records.

Key Features​

  • Direct database INSERT using stack9_db connector
  • PostgreSQL RETURNING clause to get the generated ID immediately
  • Fully parameterized query preventing SQL injection
  • Single atomic operation combining INSERT and ID retrieval
  • Bypasses API layer for maximum performance
  • Suitable for bulk operations or integration scenarios

Query Type​

INSERT

JSON Definition​

{
  "key": "linkup_recruit_jobs_create_job_application_sql",
  "name": "linkup_recruit_jobs_create_job_application_sql",
  "connector": "stack9_db",
  "queryTemplate": {
    "query": "INSERT INTO job_application (job_posting_id, applicant_id, status, cover_letter, resume, screening_answers) VALUES ({{job_posting_id}}, {{applicant_id}}, {{status}}, {{cover_letter}}, {{resume}}, {{screening_answers}}) RETURNING id"
  },
  "userParams": {
    "job_posting_id": "",
    "applicant_id": "",
    "status": "",
    "cover_letter": "",
    "resume": "",
    "screening_answers": ""
  }
}

Notes​

  • The RETURNING clause is PostgreSQL-specific; other databases may require different syntax (e.g., OUTPUT in SQL Server)
  • Parameters are injected safely using double curly brace syntax, which handles proper escaping
  • The empty strings in userParams are placeholders that will be replaced with actual values at runtime
  • This query bypasses any API-level validations, so data integrity must be ensured at the application level
  • The screening_answers field likely stores JSON data, which PostgreSQL handles natively
  • Consider using the API endpoint instead if you need trigger execution, audit logging, or complex validations
  • Performance benefit: Single round trip to database vs. INSERT followed by SELECT
  • The returned ID can be immediately used to create related records in subsequent queries