Product
Securing Real-Time Applications with SingleStoreDB Cloud and AWS PrivateLink
In today’s digital world, cloud data security is a critical concern for both businesses and individuals. With the increasing volume of data being generated and stored in the cloud, it's more important than ever to protect sensitive information from cyber threats. At SingleStore, nothing is more important than the security of our customer’s data. Our organization works diligently to ensure security is architected, designed, implemented and audited at every layer of the technology stack.As part of our ongoing commitments to security and partnerships, we’re proud to announce that SingleStore has recently attained AWS PrivateLink Ready Partner designation — particularly for our managed offering, SingleStoreDB Cloud on AWS. In this blog, we’ll discuss all things AWS PrivateLink and SingleStoreDB Cloud. SingleStoreDB CloudSingleStoreDB Cloud is a fully managed, cloud-native database that powers real-time workloads. Our patented Universal Storage architecture intelligently tiers data between three storage layers (RAM, SSD, object storage) based on data access patterns. This ensures that data is always on the correct storage tier, delivering high performance at scale.Built for engineers by engineers, SingleStoreDB is based on a distributed SQL architecture, meaning databases are spread amongst a cluster of nodes rather than a single node monolith. Allowing SingleStore nodes to scale horizontally mitigates bottlenecks in data architectures, delivering millisecond performance on complex queries. These ‘shared nothing’ design principles allow businesses to effortlessly scale real-time applications.Having a distributed SQL database with Universal Storage architecture enables transactions and analytics to be unified in a single database engine. This is simpler, more performant, and more affordable than the alternative of stitching together disparate purpose-built databases to serve transactions and analytics separately. SingleStoreDB drives low-latency access to large datasets and simplifies the development of fast, modern applications.
Read Post
Engineering
Announcing the SingleStoreDB Driver for the SQLTools VSCode Extension
For those of you who haven't heard of it, SQLTools is a VSCode extension for connecting to different databases. This extension is extremely popular, recording more than 2 million downloads. SQLTools works using drivers — which are installed separately from the main extension — to connect to a variety of different databases. During a recent internal hackathon, we worked on a driver for SingleStoreDB, so that SQLTools users can connect to SingleStore databases. This extension has been published on GitHub, where you can learn all about it!Installing this driver is as easy as:code --install-extension mtxr.sqltoolscode --install-extension singlestore.sqltools-singlestore-driverHere's a tease of what you'll get:
Read Post
Engineering
Full-Text Search in SingleStoreDB
We’re demonstrating the power of full-text search capabilities in SingleStoreDB — complete with code and examples for you to give it a try.What Is SingleStoreDB and Full-Text Search?SingleStoreDB is a real-time, distributed SQL database that handles both transactional (OLTP) and analytical (OLAP) workloads in a single, unified database. SingleStoreDB is a multi-model database that natively allows you to store and retrieve several different data types including relational, document, time-series and geospatial, along with providing full-text search capabilities. SingleStoreDB has supported full-text search (FTS) since 2018. Full-text search refers to the ability to search text inside text data stored in a database table, returning results that contain some or all of the words from the query that was executed. This differs from a traditional query that returns only an exact match of the data. For traditional databases, full-text searches can be challenging — which often leads to the development of specialty databases being used strictly for this purpose. SingleStoreDB helps customers simplify their data landscape by integrating full-text search capabilities into the core functionality of the database engine.How Does Full-Text Search Work?So, how do full-text searches in SingleStoreDB work? As previously described, full-text search allows for the searching of words or phrases in a large body of text. For full-text searches to work, an index must be created on a text data type. In SingleStoreDB, text data types include char, varchar, text and long text. Full-text indexes are only supported on columnstore tables, and they must be created as part of the CREATE TABLE process using the FULLTEXT index type. How Do We Do It?Full-text search takes advantage of SingleStoreDB’s patented Universal Storage, a unified table type that supports both OLTP and OLAP workloads. The columnstore table is a perfect table type for full-text indexes because a columnstore table lends itself to very fast scans of individual — or indexed — columns. Columnstore tables are also ideal for great compression. In fact, it's not uncommon to see SingleStoreDB compression of up to 90% on a columnstore table. What Are Some Types of Full-Text Search?What will full-text search allow you to do in SingleStoreDB? You can search for an exact phrase or word, or you can complete “fuzzy” searches. A fuzzy search is similar to a query with a “LIKE” filter in the `where` clause, and allows you to find words or phrases that are similar to the text you are searching for. ExamplesThe following tutorial will walk you through an example of creating a full-text index and doing several searches.Step 1 — Create a database, table and use SingleStore Pipelines to load some example data.CREATE DATABASE fulltextdb;USE fulltextdb;CREATE TABLE jeopardy_questions ( show_number text, air_date date, round text, category text, value text, question text, answer text, KEY (category) using clustered columnstore, FULLTEXT (question, answer));CREATE PIPELINE load_questionsAS LOAD DATA S3 's3://testdata.memsql.com/full-text/*'CONFIG '{"region": "us-east-1"}'SKIP PARSER ERRORSINTO TABLE jeopardy_questionsFIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY "\r\n"IGNORE 1 LINES( show_number, @air_date, round, category, value, question, answer)set air_date = to_date(@air_date,'YYYY-MM-DD');START PIPELINE load_questions;OPTIMIZE TABLE jeopardy_questions flush;Step 2 — Perform a full-text search using the function MATCH AGAINST.Select question From jeopardy_questionsWhere MATCH (question) AGAINST ('baseball') and category ='SPORTS';
Read Post
Engineering
Your First Five Minutes with SingleStoreDB: What to Expect
In this blog post, we guide you through what to expect when you sign up for a new SingleStoreDB account. Explore our Quick Start Checklist, find out how to invite your team members, where to go when you need expert support and more. Table of ContentsSingleStoreDB CloudThe onboarding checklistCreate a WorkspaceMarketing tech ("martech") app tutorialInviting a team memberChat with an expertUpgrade your planThe help menuQuery Playground — No Sign Up Required!SingleStoreDB CloudHave you tried SingleStore before? If not, we’re very excited to have you! By signing up to use SingleStoreDB Cloud, you have a lot of options to explore — and we’re here to guide you through this journey.The onboarding checklistOn the "Start" page, you will find a list of items we consider important for your experience. We encourage you to follow this list, and get your tasks completed along the way.
Read Post
Engineering
Using Cypress Logs Instead of Videos and Screenshots to Debug Test Failures
In this blog post, we’ll show how we used cypress-terminal-report to facilitate debugging test failures by fetching all the Cypress Logs. For developing the frontend of our product (Customer Portal), we use Cypress as the primary testing framework. We have two types of Cypress tests: end-to-end and component tests.We have relatively few end-to-end tests, yet these test all layers of the product — including the SingleStoreDB database engine, backend and frontend. Since some of these tests create database clusters, they can take some time to finish. Thus, the Cypress Component Tests (CCT) are the main tests for every facet of our product’s interface.However, these tests come with a caveat: they can be very flaky if not written properly. Our CI/CD job for CCT fails most frequently due to flaky tests. The only artifacts our pipeline collects to inspect the failed tests are the screenshots and videos that Cypress creates, which are not always enough to easily assess what happened to the failed test. For this reason, we tried to find a way to get the Cypress logs for each test, including the console logs printed while the tests are run (which are not visible in the Cypress videos).At the moment, Cypress doesn’t support this kind of logging by default. In an open issue on Cypress’s GitHub, someone provided a list of current workarounds for this problem. Of all the plugins mentioned, we decided to go with `cypress-terminal-report`.cypress-terminal-reportThis npm package is still in active development, collecting all the necessary logs for a very simple debugging of Cypress tests. Before we go on, let’s see how we set up this plugin.How to setup cypress-terminal-reportFirst, we need to install the plugin:npm i --save-dev cypress-terminal-reportAfterward, we need to import two things:`installLogsCollector``installLogsPrinter`The collector needs to be imported in the support file for Cypress tests. Since we want to use the plugin for both e2e and CCT, we’ll add the import in file `cypress/support/logging.ts`:import installLogsCollector from "cypress-terminal-report/src/installLogsCollector";installLogsCollector({});And then both support files, `cypress/support/e2e.ts` and `cypress/support/component.ts`, should import `logging.ts`.As for the printer, it needs to be imported in `cypress.config.ts`:import { defineConfig } from "cypress";import installLogsPrinter from "cypress-terminal-report/src/installLogsPrinter";export default defineConfig({ e2e: { setupNodeEvents(on, config) { installLogsPrinter(on, {}); }, }, component: setupNodeEvents(on, config) { installLogsPrinter(on, {}); }, },});With this basic setup, the plugin will capture Cypress command logs and network requests, and output them to the terminal when a test fails. It will also capture console logs, but only for e2e tests (not for CCT). These tests run in a different iframe, so the plugin cannot redirect the logs directly to the terminal. However, it is very important to us to get the CCT console logs, since these are usually flakier. We were able to accomplish this with a workaround.How to get console logs for Cypress Component testsFirst, we replace the `window.console` functions with our custom one, which will use Cypress's internal logging function — Cypress.log. We do this for every component test, so we write this inside the `before` hook. To prevent the extra logging when running Cypress in headed mode (“isInteractive”), we add the code inside an if condition. The function `processArg` will stringify the objects to be readable.before(() => { if (!Cypress.config("isInteractive")) { const consoleFuncs = ["log", "error", "info", "warn"]; cy.window().then((win) => consoleFuncs.forEach((method) => // @ts-ignore win.console[method] = (...args: Array => { Cypress.log({ name: `${method}-1`, message: args .map((arg) => processArg(arg)) .join(",\n"), }); }; }); }); }});With this block of code, the plugin will output the logs with the following format:cy:command log-1 console log outputcy:command error-1 console error outputcy:command info-1 console log outputcy:command warn-1 console warn outputThis could be enough, but we want to correctly specify the types of logs, not just `cy:command`, just like the plugin does for end-to-end tests.One of the config options available in `installLogsCollector` is processLog which we can set to a custom function to process the logs the way we want. The function accepts a list of three arguments: `[type, message, severity]`. It then returns the same type of list, after the necessary processing. Here, we check for the `cy:command` type of logs, and if the message matches the regex expression for any of the console functions, we substitute the type of log with the new one (cons:log, cons:error, …) and the message without the first part (log-1, error-1, …).import installLogsCollector, { LogType,} from "cypress-terminal-report/src/installLogsCollector";const CONSOLE_FUNCS_LOG_TYPES_MAP: Record<string, LogType> = { log: "cons:log", error: "cons:error", info: "cons:info", warn: "cons:warn",};installLogsCollector({ processLog: (args) => { const [argType, message, severity] = args; let newArgType = argType; let newMessage = message; if (newArgType === "cy:command") { for (const [func, logType] of Object.entries( CONSOLE_FUNCS_LOG_TYPES_MAP )) { const regex = new RegExp(`${func}-1\t(.+)`, "s"); const match = newMessage.match(regex); if (match) { newArgType = logType; newMessage = match[1]; } } } return [newArgType, newMessage, severity]; },});Now, the output will have the following format:cons:log console log outputcons:error console error outputcons:info console log outputcons:warn console warn outputWith these new changes, we can get all the output for each Cypress test in the terminal.Now, there’s another catch: we have several logs for each test, and with the increase of lines, the terminal won’t go back far enough to show all the logs for one test. So, the best way to analyze the logs will be if they are collected in a file.How to print logs to filesThe plugin already has the option to write the logs to a file. We can define a single file to output the logs to, which will be overwritten each time we run a new test. Or, we can write to a file for each spec test, using the name of the test as the name of the log file. However, we were having an issue with the naming of the files for CCT, where they would be something like `https:localhost:XXXX/..../cypress_runner.js`, which was not very useful.To circumvent this issue, we chose to create and write the log files with our own code. With the printer option function `collectTestLogs`, we can, as the name suggests, collect the test logs for the current test and save them in a variable.// cypress.config.tsimport { defineConfig } from "cypress";import installLogsPrinter from "cypress-terminal-report/src/installLogsPrinter";let testLogs = "";const logsPrinterConfig: installLogsPrinter.PluginOptions = { collectTestLogs: (testInfo, logs) => { // testInfo.state: "passed" | "failed" const statusSymbol = testInfo.state === "passed" ? "(+)" : "(-)"; let testContent = `${statusSymbol} ${testInfo.test}\n`; logs.forEach((logType, message, severity]) => { testContent += `\t\t<$logType} (${severity}):\n`; testContent += `${message .split("\n") .map((line) => `\t\t\t\t${line}`) .join("\n")}\n`; }); testLogs += testContent; },};export default defineConfig({ e2e: { setupNodeEvents(on, config) { installLogsPrinter(on, logsPrinterConfig); }, }, component: { setupNodeEvents(on, config) { installLogsPrinter(on, logsPrinterConfig); }, },});With these changes, when the tests run in headless mode we will have a new folder `logs` next to the folders `videos` and `screenshots` created by Cypress. On the definition for the e2e and CCT jobs on GitLab, we can add:artifacts:</span> when: always paths: - frontend/cypress/screenshots - frontend/cypress/videos - frontend/cypress/logsAnd all the artifacts will be available for us to look through when trying to debug a test failure.
Read Post
Engineering
Integrating SingleStoreDB with Presto
We’re guiding you through how to integrate Presto, a distributed query engine for SQL users with SingleStoreDB — complete with a deep dive into architecture, installation, queries and more.What Is Presto?Presto is a distributed query engine for big data that uses SQL query language. Its architecture enables users to query data sources like Hadoop, Cassandra, Kafka, AWS S3, Alluxio, MySQL, MongoDB and Teradata — and allows use of multiple data sources. Simply put, Presto offers compute that runs on top of storage.Presto architecture
Read Post
Engineering
Developer Quick Tips
Check out this roundup of some of my latest developer quick tips, hacks and more.As a technical evangelist for SingleStore and long-time developer, I’m always excited to share my take on the latest product innovations, tips and tricks for SingleStoreDB. Here’s a roundup of some of the latest ones I’ve put together — you can also always find them on dev.to.Accessing JSON Data at Warp Speed with Universal StorageThe Winter 2022 release of SingleStoreDB (version 8.0) introduced the latest installment of Universal Storage, with new performance improvements for both transactions and analytics — further cementing it as the ideal database for unifying OLAP and OLTP workloads. This latest update focuses on working with JSON and string data, particularly, seekable JSON.Using JSON with SingleStoreDBOne of the most popular data formats today, SingleStoreDB effortlessly stores, retrieves and queries JSON data. With seekable JSON, data can be stored in a compressed format. When you need to retrieve specific rows, only the retrieved data needs to be compressed. This new approach makes JSON a suitable data format not only for OLTP applications, but OLAP ones as well.
Read Post
Engineering4 min Read
Image Matching in SQL With SingleStoreDB
Vector functions in SingleStoreDB make it possible to solve AI problems, including face matching, product photo matching, object recognition…
Read Post
Engineering3 min Read
Ready for 2023? Up Your Game With These Updates to Our Laravel and Python Connectors
We’re bringing you all new Laravel and Python connectors in SingleStoreDB. Here’s what’s new — and how you can try them out. Laravel…
Read Post
Engineering4 min Read
The JSON Playground in SingleStoreDB
SingleStoreDB’s new playground allows users to run queries over TPC-H, TPC-DS and a semi-structured game dataset sourced from a Confluent…
Read Post
Engineering6 min Read
Our Journey From JSDOM and React Testing Library Toward Cypress Component Testing
Over the last year at SingleStore, we've migrated from Jest and React Testing Library (RTL) to Cypress Component Testing (CCT) for UI…
Read Post
Engineering3 min Read
Locally Create Wasm UDFs in Rust and Use Them in SingleStoreDB
SingleStoreDB’s new Code Engine — Powered by Wasm empowers users to utilize user-defined functions and table-valued functions written in C…
Read Post
Engineering9 min Read
Implementing a Dark Theme for SingleStoreDB Cloud Portal UI
We at SingleStore want to provide a familiar and comfortable environment for our users, many of whom are accustomed to dark UI whilst…
Read Post
Engineering5 min Read
Using GitLab Code Review and CI with the Git Rebase Workflow
The debate between the Git Rebase and Git Merge workflows is a long-lasting and heated one, and at SingleStore we use the rebase…
Read Post
Engineering5 min Read
Guest Engineer Showcase: Vedran Cindric
This edition of SingleStore’s engineer showcase puts a (guest) spotlight on Vedran Cindric, a Croatia-based founder and CEO. Hear how Vedran…
Read Post
Engineering2 min Read
How to Load CSV/JSON Files Into SingleStoreDB With the Portal UI
Databases without data are pretty pointless. For SingleStoreDB, we’re trying to make the process of loading data into your databases as easy…
Read Post
![[r]evolution Summer 2022: Bring Application Logic to Your Data With SingleStoreDB Code Engine for Wasm](/images/cms/blog-posts/img_blog_post_featured_revolution-2022-code-engine-powered-by-wasm.png)
Engineering5 min Read
[r]evolution Summer 2022: Bring Application Logic to Your Data With SingleStoreDB Code Engine for Wasm
SingleStoreDB Cloud now supports user-defined functions written in C, C++ and Rust with our new Code Engine — Powered by Wasm. Application…
Read Post
![[r]evolution Summer 2022: Wasm Space Program](/images/cms/blog-posts/img_blog_post_featured_revolution-2022-wasm-space-program.png)
Engineering4 min Read
[r]evolution Summer 2022: Wasm Space Program
For some time now I have wanted to build a universe simulation. The idea of millions of spaceships each making their own real-time decisions…
Read Post
Engineering1 min Read
Laravel and SingleStoreDB Quickstart Guide
In this get-started guide, we'll show you how to get up and running with Laravel and SingleStoreDB in a matter of minutes. You can view the…
Read Post
Engineering4 min Read
How to Migrate From PostgreSQL to SingleStoreDB
The global Postgres community boasts tens of thousands of users and several thousand enterprises. That must say something good about the…
Read Post
Engineering3 min Read
Kubernetes and SingleStore: 5 Things You Absolutely Need to Know, Part 3
If you want to use Kubernetes to orchestrate SingleStoreDB instances, you’re part of a growing crowd. But deploying Kubernetes in a…
Read Post
