Chapter 6 Working with Relational Tables

This section explains how to use X DevAPI SQL CRUD functions to work with relational tables.

The following example code compares the operations previously shown for collections and how they can be used to work with relational tables using SQL. The simplified X DevAPI syntax is demonstrated using SQL in a Session and showing how it is similar to working with documents.

MySQL Shell JavaScript Code

// Working with Relational Tables
var mysqlx = require('mysqlx');

// Connect to server using a connection URL
var mySession = mysqlx.getSession( {
  host: 'localhost', port: 33060,
  user: 'user', password: 'password'} )

var myDb = mySession.getSchema('test');

// Accessing an existing table
var myTable = myDb.getTable('my_table');

// Insert SQL Table data
myTable.insert(['name', 'birthday', 'age']).
  values('Laurie', mysqlx.dateValue(2000, 5, 27), 19).execute();

// Find a row in the SQL Table
var myResult = myTable.select(['_id', 'name', 'birthday']).
  where('name like :name AND age < :age').
  bind('name', 'L%').bind('age', 30).execute();

// Print result
print(myResult.fetchOne());
  

MySQL Shell Python Code

# Working with Relational Tables
from mysqlsh import mysqlx

# Connect to server using a connection URL
mySession = mysqlx.get_session( {
  'host': 'localhost', 'port': 33060,
  'user': 'user', 'password': 'password'} )

myDb = mySession.get_schema('test')

# Accessing an existing table
myTable = myDb.get_table('my_table')

# Insert SQL Table data
myTable.insert(['name','birthday','age']) \
  .values('Laurie', mysqlx.date_value(2000, 5, 27), 19).execute()

# Find a row in the SQL Table
myResult = myTable.select(['_id', 'name', 'birthday']) \
  .where('name like :name AND age < :age') \
  .bind('name', 'L%') \
  .bind('age', 30).execute()

# Print result
print(myResult.fetch_all())
  

Node.js JavaScript Code

// Working with Relational Tables
var mysqlx = require('@mysql/xdevapi');
var myTable;

// Connect to server using a connection URL
mysqlx
  .getSession({
    user: 'user',
    password: 'password',
    host: 'localhost',
    port: 33060
  })
  .then(function (session) {
    // Accessing an existing table
    myTable = session.getSchema('test').getTable('my_table');

    // Insert SQL Table data
    return myTable
      .insert(['name', 'birthday', 'age'])
      .values(['Laurie', '2000-5-27', 19])
      .execute()
  })
  .then(function () {
    // Find a row in the SQL Table
    return myTable
        .select(['_id', 'name', 'birthday'])
        .where('name like :name && age < :age)')
        .bind('name', 'L%')
        .bind('age', 30)
        .execute(function (row) {
          console.log(row);
        });
  })
  .then(function (myResult) {
    console.log(myResult);
  });

C# Code

// Working with Relational Tables

// Connect to server using a connection
var db = MySQLX.GetSession("server=localhost;port=33060;user=user;password=password")
.GetSchema("test");

// Accessing an existing table
var myTable = db.GetTable("my_table");

// Insert SQL Table data
myTable.Insert("name", "age")
.Values("Laurie", "19").Execute();

// Find a row in the SQL Table
var myResult = myTable.Select("_id, name, age")
.Where("name like :name AND age < :age")
.Bind(new { name = "L%", age = 30 }).Execute();

// Print result
PrintResult(myResult.FetchAll());

Python Code

# Working with Relational Tables
import mysqlx

# Connect to server using a connection URL
my_session = mysqlx.get_session({
    'host': 'localhost', 'port': 33060,
    'user': 'user', 'password': 'password'
})

my_schema = my_session.get_schema('test')

# Accessing an existing table
my_table = my_schema.get_table('my_table')

# Insert SQL Table data
my_table.insert(['name', 'birthday', 'age']) \
    .values('Laurie', mysqlx.date_value(2000, 5, 27), 19).execute()

# Find a row in the SQL Table
result = my_table.select(['_id', 'name', 'birthday']) \
    .where('name like :name AND age < :age') \
    .bind('name', 'L%') \
    .bind('age', 30).execute()

# Print result
print(result.fetch_all())
  

Java Code

// Working with Relational Tables
import com.mysql.cj.xdevapi.*;

// Connect to server using a connection URL
Session mySession = new SessionFactory().getSession("mysqlx://localhost:33060/test?user=user&password=password");
Schema db = mySession.getSchema("test");

// Accessing an existing table
Table myTable = db.getTable("my_table");

// Insert SQL Table data
myTable.insert("name", "birthday").values("Laurie", "2000-05-27").execute();

// Find a row in the SQL Table
RowResult myResult = myTable.select("_id, name, birthday")
  .where("name like :name AND age < :age")
  .bind("name", "L%").bind("age", 30).execute();

// Print result
System.out.println(myResult.fetchAll()); 

C++ Code

// Working with Relational Tables
#include <mysqlx/xdevapi.h>

// Connect to server using a connection URL
Session mySession(33060, "user", "password");

Schema myDb = mySession.getSchema("test");

// Accessing an existing table
Table myTable = myDb.getTable("my_table");

// Insert SQL Table data
myTable.insert("name", "birthday", "age")
       .values("Laurie", "2000-5-27", 19).execute();

// Find a row in the SQL Table
RowResult myResult = myTable.select("_id", "name", "birthday")
  .where("name like :name AND age < :age")
  .bind("name", "L%").bind("age", 30).execute();

// Print result
Row row = myResult.fetchOne();
cout << "     _id: " << row[0] << endl;
cout << "    name: " << row[1] << endl;
cout << "birthday: " << row[2] << endl;