How to run SQL scripts and get data on application startup?

SQL

Application Startup

Scripts

Data Retrieval

Database Management

How to run SQL scripts and get data on application startup?

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design

Databases

Introduction

Running SQL scripts at application startup is a common requirement for initializing database schemas, seeding reference data, and applying migrations. Different frameworks provide built-in mechanisms for this, from Spring Boot's auto-execution of data.sql files to Node.js migration libraries. This article covers the main approaches across popular frameworks.

Spring Boot

Automatic Script Execution

Spring Boot automatically executes schema.sql and data.sql files found in src/main/resources/:

sql

1-- src/main/resources/schema.sql
2CREATE TABLE IF NOT EXISTS users (
3    id BIGINT AUTO_INCREMENT PRIMARY KEY,
4    username VARCHAR(50) NOT NULL UNIQUE,
5    email VARCHAR(100) NOT NULL,
6    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
7);
8
9CREATE TABLE IF NOT EXISTS roles (
10    id BIGINT AUTO_INCREMENT PRIMARY KEY,
11    name VARCHAR(30) NOT NULL UNIQUE
12);

sql

1-- src/main/resources/data.sql
2INSERT INTO roles (name) VALUES ('ADMIN') ON CONFLICT DO NOTHING;
3INSERT INTO roles (name) VALUES ('USER') ON CONFLICT DO NOTHING;
4INSERT INTO roles (name) VALUES ('MODERATOR') ON CONFLICT DO NOTHING;

Configure in application.properties:

properties

1# Enable SQL initialization (default in Spring Boot 2.5+)
2spring.sql.init.mode=always
3
4# For platform-specific scripts (e.g., schema-mysql.sql)
5spring.sql.init.platform=mysql
6
7# Execution order: schema.sql runs first, then data.sql

Using Flyway (Recommended for Production)

Flyway provides versioned, repeatable migrations:

xml

1<dependency>
2    <groupId>org.flywaydb</groupId>
3    <artifactId>flyway-core</artifactId>
4</dependency>

Place migration scripts in src/main/resources/db/migration/:

sql

1-- V1__create_users_table.sql
2CREATE TABLE users (
3    id BIGSERIAL PRIMARY KEY,
4    username VARCHAR(50) NOT NULL
5);
6
7-- V2__add_email_column.sql
8ALTER TABLE users ADD COLUMN email VARCHAR(100);
9
10-- R__seed_roles.sql (repeatable — re-runs when content changes)
11INSERT INTO roles (name) VALUES ('ADMIN') ON CONFLICT DO NOTHING;

Using @PostConstruct or CommandLineRunner

For programmatic initialization:

java

1@Component
2public class DataInitializer implements CommandLineRunner {
3
4    @Autowired
5    private JdbcTemplate jdbcTemplate;
6
7    @Override
8    public void run(String... args) {
9        // Check if data exists before seeding
10        Integer count = jdbcTemplate.queryForObject(
11            "SELECT COUNT(*) FROM roles", Integer.class
12        );
13        if (count == 0) {
14            jdbcTemplate.execute("INSERT INTO roles (name) VALUES ('ADMIN')");
15            jdbcTemplate.execute("INSERT INTO roles (name) VALUES ('USER')");
16        }
17    }
18}

Node.js / Express

Using Knex.js Migrations

bash

npx knex migrate:latest  # Run at startup
npx knex seed:run         # Seed data

Run migrations programmatically at startup:

javascript

1const knex = require('knex')(config);
2
3async function initDatabase() {
4    await knex.migrate.latest();
5    console.log('Migrations complete');
6
7    await knex.seed.run();
8    console.log('Seeding complete');
9}
10
11initDatabase().then(() => {
12    app.listen(3000);
13});

Using Sequelize

javascript

1const { Sequelize } = require('sequelize');
2const sequelize = new Sequelize(process.env.DATABASE_URL);
3
4async function start() {
5    // Sync models (creates tables if they don't exist)
6    await sequelize.sync({ alter: true });
7
8    // Run raw SQL
9    await sequelize.query(`
10        INSERT INTO roles (name) VALUES ('admin')
11        ON CONFLICT (name) DO NOTHING
12    `);
13
14    app.listen(3000);
15}
16start();

Django

Django uses its built-in migration system:

bash

python manage.py migrate  # Apply migrations at startup

For data seeding, use fixtures or data migrations:

python

1# myapp/migrations/0002_seed_roles.py
2from django.db import migrations
3
4def seed_roles(apps, schema_editor):
5    Role = apps.get_model('myapp', 'Role')
6    roles = ['admin', 'user', 'moderator']
7    for role in roles:
8        Role.objects.get_or_create(name=role)
9
10class Migration(migrations.Migration):
11    dependencies = [('myapp', '0001_initial')]
12
13    operations = [
14        migrations.RunPython(seed_roles, migrations.RunPython.noop),
15    ]

.NET / Entity Framework

csharp

1// Program.cs
2var app = builder.Build();
3
4// Apply migrations at startup
5using (var scope = app.Services.CreateScope())
6{
7    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
8    db.Database.Migrate();
9
10    // Seed data
11    if (!db.Roles.Any())
12    {
13        db.Roles.AddRange(
14            new Role { Name = "Admin" },
15            new Role { Name = "User" }
16        );
17        db.SaveChanges();
18    }
19}
20
21app.Run();

Getting Data at Startup

To load reference data into memory at startup:

Spring Boot

java

1@Component
2public class AppConfig {
3
4    private List<Role> roles;
5
6    @Autowired
7    private RoleRepository roleRepository;
8
9    @PostConstruct
10    public void init() {
11        roles = roleRepository.findAll();
12        System.out.println("Loaded " + roles.size() + " roles");
13    }
14
15    public List<Role> getRoles() {
16        return roles;
17    }
18}

Node.js

javascript

1let config;
2
3async function loadConfig() {
4    const rows = await db.query('SELECT * FROM app_config');
5    config = Object.fromEntries(rows.map(r => [r.key, r.value]));
6}
7
8loadConfig().then(() => app.listen(3000));

Common Pitfalls

Execution order: Schema scripts must run before data scripts. In Spring Boot, schema.sql runs before data.sql by default, but Flyway/Liquibase handle ordering via version numbers.
Idempotency: Startup scripts run every time the application starts. Use IF NOT EXISTS, ON CONFLICT DO NOTHING, or check-before-insert to avoid duplicate data or errors on restart.
Microservices Architecture: Each microservice should only manage its own database schema. Avoid cross-service SQL scripts.
Test Environments: Use separate seed scripts for tests to reset and repopulate database state before integration tests. Consider using transactions that roll back after each test.
Production vs Development: Use spring.sql.init.mode=embedded (default) for development with H2, and spring.sql.init.mode=never for production where Flyway handles migrations.

Summary

Spring Boot auto-executes schema.sql and data.sql from resources; use Flyway for production migrations
Node.js: use Knex or Sequelize migrations run programmatically before app.listen()
Django: use the built-in migration system with RunPython for data seeding
.NET: call db.Database.Migrate() in Program.cs at startup
Always make startup scripts idempotent to handle restarts safely

System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course

Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design

How to run SQL scripts and get data on application startup?

System Design practice on Codemia

Introduction

Spring Boot

Automatic Script Execution

Using Flyway (Recommended for Production)

Using @PostConstruct or CommandLineRunner

Node.js / Express

Using Knex.js Migrations

Using Sequelize

Django

.NET / Entity Framework

Getting Data at Startup

Spring Boot

Node.js

Common Pitfalls

Summary

Related reading

System Design Fundamentals

Track what you have practised

System Design practice on Codemia