Question T20048
Visible to All Users

DB Script

created 5 months ago

Dear SurveyJS Support Team,
I would like assistance in storing survey questions and responses in a relational database format (SQL Server), without using JSON. Specifically, I need to store each question and its corresponding answer as separate rows in the database, with metadata such as the survey name and timestamp stored in a separate table.
Can SurveyJS be configured to export data in this relational format (one question-answer per row)? If not, what would you recommend to achieve this?
Thank you for your support.

Sign in to comment on this post

Answers approved by surveyjs Support

created 5 months ago

Hello Imran,
There are several ways to implement it, depending on your scenario and server platform.
We have an article titled No Code Editor for Domain Models that illustrates one approach. We use .NET (ASP.NET Core) as our server platform, and you can find examples with full source code in this article.
Some developers create tables on the fly based on the survey definition. Essentially, they write server code that generates a table from the survey definition JSON/Survey Model.
Other developers add complexity by including custom properties, such as a data table field type or a boolean property that indicates whether to add a specific question as a field in the database.
Whatever approach you choose, I highly recommend storing the original JSON user response in your database as well. Having the original JSON response allows you to update or recreate your flat database as needed.

Thank you,
Andrew
SurveyJS Team

    Comments (2)
    M M
    Mohamed 5 months ago

      Thank you for your previous insights. As I continue working with the survey model, I’ve encountered challenges due to the number and variety of properties involved. Given this complexity, it’s difficult to add all of them.

      I would greatly appreciate your assistance in designing or implementing a flat database structure that can efficiently manage the survey data, even with the numerous properties that may arise. Your expertise would be invaluable in ensuring that the database remains flexible and scalable.
      I look forward to your support and guidance.

      AT AT
      Andrew Telnov 5 months ago

        Hello Mohamed,
        You can add a custom property into SurveyJS question type. For example, it can be a boolean property (storeInDB) or a dbType property as string. You can learn how to add a custom property here.
        Further, on saving user responses, you can send two JSONs to your server in one request.

        1. The original JSON
        2. JSON for questions which response you want to save in the separate database. Here is the code that creates that JSON if you have a dbType property.
        JavaScript
        const fieldsJSON = {};
survey.getAllQuestions().forEach(q => {
  if(q.dbType && !q.isEmpty()) {
    fieldsJSON[q.getValueName()] = { value: q.value, dbType: q.dbType }
  }
});

        On your server you will need to write the code that will create a new field(s) in your database if this field is not exist yet and then add a new record.

        Thank you,
        Andrew
        SurveyJS Team

        Sign in to comment on this post