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/:
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);
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:
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:
1<dependency>
2 <groupId>org.flywaydb</groupId>
3 <artifactId>flyway-core</artifactId>
4</dependency>
Place migration scripts in src/main/resources/db/migration/:
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:
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
npx knex migrate:latest # Run at startup
npx knex seed:run # Seed data
Run migrations programmatically at startup:
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
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:
python manage.py migrate # Apply migrations at startup
For data seeding, use fixtures or data migrations:
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
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
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
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
