Moving SQL Database Content to Couchbase

DZone 's Guide to

Moving SQL Database Content to Couchbase

Since the GA release of N1QL, we get a lot of questions about moving content from an SQL database to Couchbase. There are many different ways to do so. Today, I have chosen what is probably the simplest. Read on to learn how it's done.

· Database Zone ·
Free Resource

Since the GA release of N1QL, we get a lot of questions about moving content from an SQL database to Couchbase. There are many different ways to do so. Today, I have chosen what is probably the simplest. I will transform each row of each table in a JsonDocument and store it in Couchbase. I will do my test with Postgres and their sample dataset inspired by MySQL Sakila sample. I will use Java, but the guidelines presented here are applicable to other languages.

Connecting to a Running SQL Database

Since I am using Java, I will implement Spring Boot and their JDBC package, which handles the db connection for me. All I have to do is set up the right dependencies and properties to configure the JdbcTemplate. This object makes running an SQL query a breeze.


To make sure you have everything configured neatly and automatically, you need the following dependencies:

        dependencies {
            compile "org.springframework.boot:spring-boot-starter",

I am testing with Postgres, but you could add any other driver supported by Spring JDBC. The spring-boot-starter-data-jpa will allow me to inject the preconfigured JdbcTemplate.


To make sure the Spring framework finds your database, add the following properties to your configuration file (for example: src/main/resources/application.properties).


Of course, you would need to fine-tune this according to the database you are using. Here I am using Postgres running on with default port 5432. The name of the database I want to use is dvdrental.


If everything is configured correctly you should be able to inject the JdbcTemplate and start querying your SQL DB.

     JdbcTemplate jdbcTemplate;

     public void doStuff() throws Exception {
      String sql = "SELECT id FROM table";
         Long id = jdbcTemplate.queryForObject(sql, Long.class);

Connecting to Couchbase

My goal is to move content from an SQL database to Couchbase, so we also need a Couchbase connection.


Working with Couchbase on your Java project requires you to add the following dependency:

   dependencies {
        compile "com.couchbase.client:java-client:2.2.3"

This will give you access to the Couchbase Java SDK.


A basic Couchbase configuration requires basically three properties: one server IP address, a bucket name, and a bucket password. Doing this in a Spring Boot fashion would look like this:

        public class Database {

            private String hostname;

            private String bucket;

            private String password;

            public @Bean Cluster cluster() {
                return CouchbaseCluster.create(hostname);

            public @Bean Bucket bucket() {
                return cluster().openBucket(bucket, password);


The properties hostname, bucket, and password can be added directly to your application properties file.

   # Hostnames, comma separated list of Couchbase node IP or hostname
    hostnames: localhost,
    # Bucket name
    bucket: default
    # Bucket password


With Couchbase, the equivalent granularity level of a database would be a bucket, which is where you store documents. With the previous configuration you ccould simply inject a bucket and start playing around.

        Bucket bucket;

        public void doStuff() throws Exception {
            JsonDocument doc = bucket.get("key");


At this point, you have a connection to an SQL database and Couchbase. Now, we can start moving things around. The easiest way to move data is to consider each row of each table as a document.

Getting the SQL Schema

Let’s start by getting the schema of the database automatically using the JdbcTemplate. The interesting object here is DatabaseMetaData, which can give us the complete structure of the database. The API is not the easiest to use, but at least it’s documented.

I will map the result of the DatabaseMetaData query to a list of Table and Column. I have created the following Java class to do so:

         public class Table {

            private String name;

            private List<Column> columns = new ArrayList<Column>();

            private String primaryKey;

            public Table(String tableName) {
             this.name = tableName;

            public void setPrimaryKey(String primaryKey) {
             this.primaryKey = primaryKey;

            public void addColumn(String name, int type) {
             columns.add(new Column(name, type));

            public String getName() {
             return name;

            public List<Column> getColumns() {
             return columns;

            public String getPrimaryKey() {
             return primaryKey;

            public JsonObject toJsonObject() {
             JsonObject obj = JsonObject.create();
             JsonArray jsonColumns = JsonArray.create();
             for (Column col : columns) {
             obj.put("tableName", name);
             obj.put("primaryKey", primaryKey);
             obj.put("columns", jsonColumns);
             return obj;

     public class Column {

            private String name;

            private int type;

            public Column(String name, int type) {
             this.name = name;
             this.type = type;

            public String getName() {
             return name;

            public int getType() {
             return type;

            public JsonObject toJsonObject() {
             JsonObject obj = JsonObject.create();
             obj.put("name", name);
             obj.put("type", type);
             return obj;


It’s definitely not the most exciting code to write, but in the end you get a JSON representation of your SQL database tables.

        public void getDatabaseSchema() throws Exception {
         // get Database Medatadata objects to retrieve Tables schema
        DatabaseMetaData databaseMetadata = jdbcTemplate.getDataSource().getConnection().getMetaData();
            List<String> tableNames = new ArrayList<String>();
            // Get tables names
            ResultSet result = databaseMetadata.getTables(catalog, schemaPattern, tableNamePattern, types);
            while (result.next()) {
             String tablename = result.getString(3);
             String tableType = result.getString(4);
             // make sure we only import table(as oppose to Views, counter etc...)
             if (!tablename.isEmpty() && "TABLE".equals(tableType)) {
                    log.debug("Will import table " + tablename);
            // Map the tables schema to Table objects
            Map<String, Table> tables = new HashMap<String, Table>();
            JsonObject tablesSchema = JsonObject.create();
            for (String tableName : tableNames) {
             result = databaseMetadata.getColumns(catalog, schemaPattern, tableName, columnNamePattern);
             Table table = new Table(tableName);
             while (result.next()) {
                    String columnName = result.getString(4);
                    // Maps to JDBCType enum
                    int columnType = result.getInt(5);
                    table.addColumn(columnName, columnType);
             result = databaseMetadata.getPrimaryKeys(catalog, schemaPattern, tableName);
             while (result.next()) {
                    String columnName = result.getString(4);
             tables.put(tableName, table);
             tablesSchema.put(tableName, table.toJsonObject());
            JsonDocument schemaDoc = JsonDocument.create(tablesSchemaId, tablesSchema);
            JsonDocument doc = bucket.upsert(schemaDoc);


Here’s the fun part. This is where we start mapping a table row to a JsonDocument. The previous section puts us in a state where we can retrieve the name of all the tables. From one table name, we can create an SQL query that returns every row of the table.

Spring has a mechanism that allows you to define a RowMapper. For each row returned by the query, you can return the object you want. Since I am using Couchbase, I want a JsonDocument.

Following is an implementation example. This RowMapper needs a Table object previously defined; therefore, we have to implement the mapRow method. There are several things we need to do here.

The first task is to create a unique key. As rows are scoped by tables, some id can be exactly the same for rows in different tables. But documents are scoped by bucket, so we need to create a unique document key that reflects the row id and the table name. To keep track of where the document comes from, I will also add a _tableName field for the table name.

Then, the exciting step comes from the type mapping. JSON supports fewer types than an SQL database, so we have some conversions to do here. This is what the getJsonTypedValue method does. It makes sure most JDBC types can be converted to a native JSON type (string, number, boolean, array, object, null). At the end, we have a JsonDocument that can be saved in Couchbase.

        public class JSONRowMapper implements RowMapper<Document> {

        Table table;

        public JSONRowMapper(Table table) {
         this.table = table;

        public JsonDocument mapRow(ResultSet rs, int rowNum) throws SQLException {
         String id = table.getName() + "::" + rs.getString(table.getPrimaryKey());
         JsonObject obj = JsonObject.create();
         obj.put("_tableName", table.getName());
         for (Column col : table.getColumns()) {
                Object value = getJsonTypedValue(col.type, rs.getObject(col.name), col.name);
                obj.put(col.name, value);
         return JsonDocument.create(id, obj);

        public Object getJsonTypedValue(int type, Object value, String name) throws SQLException {
         if (value == null) {
                return null;
         JDBCType current = JDBCType.valueOf(type);
         switch (current) {
         case TIMESTAMP:
                Timestamp timestamp = (Timestamp) value;
                return timestamp.getTime();
                Timestamp ts = (Timestamp) value;
                JsonObject tsWithTz = JsonObject.create();
                tsWithTz.put("timestamp", ts.getTime());
                tsWithTz.put("timezone", ts.getTimezoneOffset());
                return tsWithTz;
         case DATE:
                Date sqlDate = (Date) value;
                return sqlDate.getTime();
         case DECIMAL:
         case NUMERIC:
                BigDecimal bigDecimal = (BigDecimal) value;
                return bigDecimal.doubleValue();
         case ARRAY:
                Array array = (Array) value;
                Object[] objects = (Object[]) array.getArray();
                return JsonArray.from(objects);
         case BINARY:
         case BLOB:
         case LONGVARBINARY:
                return Base64.getEncoder().encodeToString((byte[]) value);
         case OTHER:
         case JAVA_OBJECT:
                // database specific, default to String value
                return value.toString();
                return value;

With that RowMapper it makes things really easy. We can loop through the table’s name, run the query, and save the results in Couchbase. Doing this in a synchronous fashion would look like this:

        for (String tableName : tableNames) {
         String sql = "select * from " + tableName + ";";
         List<JsonDocument&gt; rs = jdbcTemplate.query(sql, new JSONRowMapper(tables.get(tableName)));
         if (!rs.isEmpty()) {
             for (JsonDocument doc : rs) {

But, I prefer the async version:

   Observable.from(tableNames).flatMap(s -> {
        String sql = String.format("Select * from %s;", s);
        return Observable.from(jdbcTemplate.query(sql, new JSONRowMapper(tables.get(s))));
    // start by a jsonDocument containing the tables to be imported.
    .startWith(schemaDoc).flatmap(doc -> asyncBucket.upsert(doc));

Here, I am not using the full potential of Rx; take a look at this function that writes a doc to Couchbase while handling timeout and error management. 

For convenience, I have packaged all steps implemented and previously shown in a single project. All you have to do is make sure your properties file is configured right and run the importer:

   $ ./bin/couchbase-java-importer myConfiguration.properties

Take a look at the README file for more information.


Today, we learned how to move SQL content to Couchbase, but there is still some work to do. Next time I will tell you how to move the SQL business logic to the application layer.

databases ,couchbase ,nosql ,rdbms ,migration

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}