SQLITE and foreign key support

Question

Anyone had any success in getting sqlite and spring working with foreign key support enabled? By default foreign key support is disabled in sqlite. The documentation at http://www.sqlite.org/foreignkeys.html mentions that you have to enable it for each database connection separately. I am sure that the version of sqlite I have got supports foreign keys (downloaded it only last week).

to test: If I key in PRAGMA foreign_keys; I get back 0. Which means foreign keys is switched off but support for it exists.

My datasource is defined in spring as :

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${jdbc.driverclass}"/>
<property name="url" value="${jdbc.url}"/>
</bean>

How do I enable foreign keys through spring configuration?

Answer 1

This is an old question, but I was a little confused recently with the same problem.

There are at least 2 solutions that I know of using Spring Boot (I assume similar solutions for regular Spring). The salient point with Sqlite foreign key support is `PRAGMA foreign_keys = ON;` is valid on a *per-connection basis*. (i.e. If I have two open connections to the database, and I set foreign key support to be `On` in my first connection, the first connection will have foreign key support, but the second connection will not.

Solution 1
From the Spring Boot reference manual:

If you use the spring-boot-starter-jdbc or spring-boot-starter-data-jpa “starters”, you automatically get a dependency to HikariCP.

Spring Boot automatically creates a DataSource bean for you using HikariCP as the default driver. HikariCP itself delegates to the appropriate driver based on its configuration.

Spring Boot expects, at minimum, spring.datasource.url be set in application.properties. Hikari configuration settings can also be set in the properties file, under spring.datasource.hikari.<config-key>, when config-key is one of the Hikari configuration keys. Knowing this, using Spring Boot we can use the following application.properties:

spring.datasource.url=jdbc:sqlite:path/to/db/database_file.db
spring.datasource.hikari.connectionInitSql=PRAGMA foreign_keys=1

And the following DAO (I'm using JdbcTemplate):

@Repository
public class MyDaoImpl implements MyDao {

    private final JdbcTemplate JDBC_TEMPLATE;

    @Autowired
    public SimpleArticleDao(DataSource dataSource) {
        this.JDBC_TEMPLATE = new JdbcTemplate(dataSource);
    }

    @Override
    public void insertObject(MyObject object) {
        JDBC_TEMPLATE.update(
            *...insert object into some table*
        )
    }
}

Spring Boot will create and inject the dataSource bean using Hikari as the DataSource, and Hikari will execute PRAGMA foreign_keys = ON; for every connection it creates, ensuring that foreign key support is always enabled.

Solution 2
You can define a DataSource bean in your @Configuration class for Spring Boot, and programmatically set the foreign key support by directly using the Xerial JDBC driver:

@Configuration
public class MyApplicationConfig {

    @Bean
    public DataSource dataSource() {
        SQLiteDataSource ds = new SQLiteDataSource();
        ds.setUrl("jdbc:sqlite:path/to/db/database_file.db");
        SQLiteConfig config = ds.getConfig();
        config.enforceForeignKeys(true);
        ds.setConfig(config);
        return ds;
    }

    @Bean
    ...other bean definitions
}