Create new PostgresSQL schema

What’s the easiest way to create a new Postgres scheme inside the database on the runtime and also, create the tables written inside a SQL file?

This is a Spring boot application and the method receives the schema name that needs to be created for the db.

Answer

Although it sounds like this would be a case for using Liquibase or Flyway or any other tool, here is a simple (but very hacky) solution/starting point:

(rough) Steps:

• create the whole ddl query, which consists of the “create and use schema part” and the content of your SQL file
• inject the entity manager
• run the whole ddl query as a native query

Example/(hacky) Code:

Here a simple controller class defining a GET method that takes a parameter called “schema”:

@Controller
public class FooController {

    private static final String SCHEMA_FORMAT = "create schema %s; set schema %s; ";

    @PersistenceContext
    EntityManager entityManager;

    @Value("classpath:foo.sql")
    Resource fooResource;

    @GetMapping("foo")
    @Transactional
    public ResponseEntity<?> foo(@RequestParam("schema") String schema)
            throws IOException {
        File fooFile = new ClassPathResource("foo.sql").getFile();
        String ddl = new String(Files.readAllBytes(fooFile.toPath()));
        String schemaQuery = String.format(SCHEMA_FORMAT, schema, schema);
        String query = String.format("%s %s", schemaQuery, ddl);
        
        entityManager.createNativeQuery(query).executeUpdate();
        return ResponseEntity.noContent().build();
    }

}