Lovefield is a relational database for web apps. Written in JavaScript, works cross-browser. Provides SQL-like APIs that are fast, safe, and easy to use.

Related tags

Database lovefield
Overview

Lovefield

Build Status NPM version Bower version

Lovefield is a relational database written in pure JavaScript. It provides SQL-like syntax and works cross-browser (currently supporting Chrome 37+, Firefox 31+, IE 11+, Edge, and Safari 10+).

Please visit our public forum for general Q&A, feedback, and discussions.

Quick Start

Frequently Asked Questions

Specification

Developer Setup

Design Documents

Lovefield is Day 98 of 100 days of Google Dev. This is a quick 7-minute video filmed in August 2015.

Live 30min overview presentation at JS.LA (February 2015): Watch on YouTube or Vimeo.

Comments
  • Constraint error: (201) Duplicate keys are not allowed

    Constraint error: (201) Duplicate keys are not allowed

    I am writing some simple lovefield code, and in some cases the database seems to get corrupted and throw this error on startup: Constraint error: (201) Duplicate keys are not allowed. The database is then corrupted until I clear it manually.

    Connection:

            schemaBuilder.connect({
                storeType: storeType
            })
            .then((dbResult) => {
                console.log("connect: ", this.namespace, dbResult)
                this.db      = dbResult
                this.values  = this.db.getSchema().table(LocalStore.tableName)
                this.isReady = true
                this.readyEvents.emit("ready")
            })
    

    Clear:

        this.db.delete().from(this.values).exec()
    

    Multi-set:

                    const setIds = {}
                    const rows   = []
                    // Use the rightmost value for duplicate keys.
                    _.eachRight(requests, (request: LocalStoreRequest) => {
                        if (request.type === LocalStoreRequestType.Set && !setIds[request.data.id]) {
                            rows.push(this.values.createRow(request.data))
                            setIds[request.data.id] = true
                        }
                    })
                    if (rows.length > 0) {
                        console.log("insertOrReplace: ", this.namespace, rows)
                        return this.db.insertOrReplace().into(this.values).values(rows).exec()
                    }
    

    Multi-get:

                let ids = _.uniq(_.map(getRequests, "data"))
                if (ids.length > 0) {
                    console.log("get: ", this.namespace, ids)
                    return this.db.select().from(this.values).where(this.values.id.in(ids)).exec()
                }
    

    The connection code is always run first, before any reads or writes. Reads and writes are run in batches, with writes first, and then reads.

    I already ensure that no rows with duplicate keys are inserted during a read, so I'm not sure how it's possible to get this constraint error.

    opened by simonlast 25
  • Spurious errors thrown when Lovefield is used with Browserify.

    Spurious errors thrown when Lovefield is used with Browserify.

    Hello. Still continue trying to use library in Chrome App. Now I get another problem, when trying to connect() to schema:

    developer tools - chrome-extension cipdlhkjnhaejbgafpfhegddnhfimlhn index html 2015-05-11 23-23-32

    Errors: "document.open() is not available in packaged apps." and "Uncaught Error: document.write() is not available in packaged apps."

    Tools:

    • grunt v0.4.5
    • browserify v10.1.3
    • lovefield v2.0.50
    enhancement 
    opened by le0pard 25
  • Constraint error: (201) Duplicate keys are not allowed

    Constraint error: (201) Duplicate keys are not allowed

    Hey, I am using lovefield and from some time to time I am getting this exception:

    Constraint error: (201) Duplicate keys are not allowed, index: FlightBookingLeg.pkFlightBookingLeg, key: 2560
    

    However, all around app I am using insertOrReplace so there is no way I am adding some duplicated keys. You can see that primary key is duplicated somehow, but as I understand insertOrReplace it should ensure that it won't duplicate anything right?

    I am using WebSQL because IndexedDB does not work in Safari. Does anyone know what can cause it or how to prevent it?

    Screenshot from Chrome dev tools, you can see there are 2 duplicated keys with value 2560: screenshot 2016-08-10 18 32 16

    Just to explain, I am enforcing that connection happens only once:

    const db = require("imports?this=>window!exports?ahoydb&lf!./ahoydb");
    
    class Database {
      constructor () {
        this.ahoydb = db.ahoydb;
        this.lf = db.lf;
        this.connection = null;
        this.isConnecting = false;
        this.connectionObservers = [];
      }
    
      connect () {
        if (this.connection) {
          return Promise.resolve(this);
        }
    
        if (this.isConnecting) {
          return new Promise((resolve) => {
            this.connectionObservers.push(resolve);
          });
        }
        this.isConnecting = true;
        return db.ahoydb.connect({
          onUpgrade: this.onUpgrade.bind(this),
          storeType: this.lf.schema.DataStoreType.WEB_SQL
        }).then((db) => {
          this.connected(db);
          return this;
        });
      }
    
      connected (db) {
        this.connection = db;
        this.isConnecting = false;
        this.connectionObservers.forEach((resolve) => {
          resolve(this);
        });
        this.connectionObservers = [];
      }
    
      onUpgrade (rawDb) {
        const version = rawDb.getVersion();
    
        let promises = [];
        if (version < 2) {
          promises.push(rawDb.addTableColumn(", ", 0));
        }
        if (version < 3) {
          promises.push(rawDb.addTableColumn(", ", 0));
        }
        if (version < 4) {
          promises.push(rawDb.addTableColumn(", ", "0"));
        }
        return Promise.all(promises);
      }
    }
    
    export default new Database();
    
    opened by bimusiek 15
  • Feature/exception reporting can npe

    Feature/exception reporting can npe

    We're seeing error reports from users that the error reporting in lovefield is failing — it's attempting to call toString on undefined.

    This patch will allow us to see what the underlying error is.

    opened by wombleton 14
  • can't authenticate with firebase

    can't authenticate with firebase

    TypeError: this.Df.child is not a function
        at Ve.m.Ea (/<path>/node_modules/lovefield/dist/lovefield.min.js:111:33)
        at yk (/<path>/node_modules/lovefield/dist/lovefield.min.js:243:843)
        at W.Ea (/<path>/node_modules/lovefield/dist/lovefield.min.js:251:237)
        at Zk.connect (/<path>/node_modules/lovefield/dist/lovefield.min.js:273:192)
        at default_1.<anonymous> (/<path>/node_modules/<snip>/adapter/lovefield.js:68:57)
        at next (native)
        at fulfilled (/<path>/node_modules/<snip>/adapter/lovefield.js:4:58)
        at process._tickCallback (internal/process/next_tick.js:103:7)
    
    		const app = firebase.initializeApp(config.options || {});
    
    		let auth;
    
    		if (config.user) {
    			auth = await app.auth().signInWithEmailAndPassword(config.user.email_address, config.user.password);
    		} else {
    			auth = await app.auth().signInAnonymously();
    		}
    
    		return lf.schema.create(config.database, 1).connect({
    			storeType: lf.schema.DataStoreType.FIREBASE,
    			firebase: auth
    		});
    
    opened by ghost 11
  • MultiColumnOrPass optimization throws error if execution plan already optimized

    MultiColumnOrPass optimization throws error if execution plan already optimized

    Here's the full traceback:

    TypeError: Cannot read property 'table' of undefined at lf.proc.MultiColumnOrPass.replaceWithIndexRangeScan_
    at lf.proc.MultiColumnOrPass.rewrite
    at null.<anonymous>
    at Array.forEach (native)
    at lf.proc.PhysicalPlanRewriter.generate
    at lf.proc.PhysicalPlanFactory.createPlan_
    at lf.proc.PhysicalPlanFactory.create
    at lf.proc.DefaultQueryEngine.getPlan
    at lf.query.BaseBuilder.getPlan_
    at lf.query.BaseBuilder.getTaskItem
    

    I'm triggering it from this code, but only when building a select containing the construct: and(..., or(..., ...)). Queries of the form and(..., and(..., ...)) and and(..., or(...)) both work as expected, so I think there's something happening in MultiColumnOrPass. All of my fields are indexed in a memory store.

    That form of query alone isn't enough to reproduce -- I tried it with the todo example and it worked. I'll keep working on a self-contained test case, but I wanted to post early in case something about my client code jumped to you (eg I'm suspicious of the apply and bind here).

    opened by simon-weber 10
  • fix webworker/systemjs compatibility issue

    fix webworker/systemjs compatibility issue

    when using systemjs module loader, 'this' no longer references the global window object. I modified dist/lovefield.js to show the fix for this. btw. where is the entry point in /lib? I looked and didn't see which file was the main.

    opened by digital-synapse 10
  • Syntax error: (550) where() clause includes an invalid predicate

    Syntax error: (550) where() clause includes an invalid predicate

    Hello! I'm trying to perform a simple 'where' but I'm getting error 550.

    let t = this.t[table]; //my schema
    return this.db.select(t.id).from(t).where(t.uuid.eq(uuid)).exec();
    

    The following works just fine:

    return this.db.select(t.id).from(t).exec();
    
    opened by desvandev 9
  • innerJoin and leftOuterJoin silently fail when predicate uses a column without a foreign key constraint

    innerJoin and leftOuterJoin silently fail when predicate uses a column without a foreign key constraint

    for example given a table "item" and a table "task" with a "itemId"

    var item = db.getSchema().table('item');
    var task = db.getSchema().table('task');
    db.select().from(item).innerJoin(task, task.itemId.eq(item.id))
    

    will fail silently without schemaBuilder.addForeignKey() is it not possible to join on an arbitrary column or am I doing somthing wrong?

    opened by digital-synapse 9
  • Constraint error: (201) Duplicate keys are not allowed

    Constraint error: (201) Duplicate keys are not allowed

    I have read #155 and #172 in some detail and I believe I have a problem similar to #155.

    Constraint error: (201) Duplicate keys are not allowed, index: monos.pkMonos, key: "session"
    

    Unfortunately I am not able to reproduce in a simple example I have tried to draw up for you.

    I note this comment in #172:

    The fact that you are using insertOrReplace() means that if you insert a row with the same PK, it should replace an existing row instead of complaining. So, yes, that would be a bug if it is not happening.

    ...so it does appear that this is a bug. My schema looks a bit like this:

    schemaBuilder = lf.schema.create('myDatabase', 1);
    schemaBuilder.createTable('fw_mime_types')
    .addColumn('id', lf.Type.INTEGER)
    .addColumn('mime_type', lf.Type.STRING)
    .addColumn('file_extension', lf.Type.STRING)
    .addColumn('popular', lf.Type.INTEGER)
    .addColumn('created_timestamp', lf.Type.INTEGER)
    .addColumn('modified_timestamp', lf.Type.STRING)
    .addColumn('modified_session_link', lf.Type.INTEGER)
    .addNullable(['mime_type','file_extension','popular','created_timestamp','modified_timestamp','modified_session_link'])
    .addPrimaryKey(['id'])
    ;
    schemaBuilder.createTable('mono')
    .addColumn('key', lf.Type.STRING)
    .addColumn('value', lf.Type.NUMBER)
    .addPrimaryKey(['key'])
    ;
    schemaBuilder.createTable('monos')
    .addColumn('key', lf.Type.STRING)
    .addColumn('value', lf.Type.STRING)
    .addColumn('created_timestamp', lf.Type.INTEGER)
    .addColumn('modified_timestamp', lf.Type.INTEGER)
    .addColumn('modified_session_link', lf.Type.INTEGER)
    .addNullable(['value','created_timestamp','modified_timestamp','modified_session_link'])
    .addPrimaryKey(['key'])
    ;
    	schemaBuilder
    		.connect()
    		.then(function(newDb){
    			db=newDb;
    			db_mono=db.getSchema().table('mono');
    			db_monos=db.getSchema().table('monos');
    	})
    
    

    ..though I have about 10 tables in total with greater complexity.

    The main similarity to #155 is that I am using a STRING as a key, and not an INTEGER as most people would.

    The error occurs when I try to insertOrReplace a second time, using the key, which in this case is "session".

    There are also a few other reads and writes to and from the same table(and various other tables) in between insertOrReplaces on that key.

    If I change the hard-coded key name after the first time the database is created to another string, the error no longer occurs, no matter how many times I reload the app in the browser. That is, if that key is written and the read routine continues to read the row with the original key.

    If I delete the database from the console and reload, the error occurs on the second reload or any subsequent insertOrReplace on that key.

    If I use a numerical string "0" as the key, the error does not occur.

    If I replace the hard-coded occurrences of "session" with a variable that contains "session" in the line newData[0]["key"]="session"; , the error also does not occur.

    function dbStoreArray(tableName,index,array){
    	var newData=this[tableName];
    	var row=[];
    	var newRows=[];
    
    	if (tableName=="session"){
    		tableName="monos";
    		newData={};
    		newData[0]={};
    		newData[0]["key"]="session";
    		myData=this["session"]["000"];
    		newData[0]["value"]=JSON.stringify(myData);
    	}
    	var table=db.getSchema().table(tableName);
    	var keys=Object.keys(newData);
    	var l=keys.length;
    	for (var n = 0; n < l; n++) {
    	    var currentRow=newData[keys[n]];
    	    row=table.createRow(currentRow);
    	    newRows.push(row);
    	}
    
    	db
    		.insertOrReplace()
    		.into(table)
    		.values(newRows)
    		.exec()
    		.then(function(){
    			console.log("insert success");
    		})
    		.catch(function(error){
    			console.log("fail",error);
    		});
    	newRows=[];
    }
    

    This is the function that is called to store the data, which parses over a Javascript object of 8 sets of data(objects) passed from the server, using jQuery.ajax like this:

    $.ajax({
    			method: "GET",
    			url:"dbSync.php",
    			data:{"l": latest}
    		})
    		.done (function(response){
    			var result=JSON.parse(response);
    			Object.keys(result).forEach(dbStoreArray,result);
    	})
    

    The first 7 of the 8 sets are stored just fine in the database, and the last one which contains the session data is not. The session data is added from the server using a slightly different method to the other 7 but I have verified that the data format / JSON format passed from the server and into the database is consistent at every stage.

    I am testing on Chrome for Linux version 71.0.3578.98 (Official Build) (64-bit) on Linux Mint 19.1

    I hope that gives you enough to go on.

    opened by fezzzza 8
  • Connection fails on DB with 300K+ rows. In some cases Chrome completely crashes

    Connection fails on DB with 300K+ rows. In some cases Chrome completely crashes

    Hi there, We love this awesome project and have been using it in one of our products.

    We started noticing crashes of our app. In some cases, the DB fails to load, in other cases Chrome crashes completely.

    After investigating, it seems that at a certain size of data, Lovefield can no longer make connections to the DB.

    I've put together a demo that reproduces on my machine (Arch Linux, Chrome 67, 16GB RAM): https://jsfiddle.net/0td6Leb4/ When clicking "Run", we start writing 300K rows to the DB. After writing we reload the page, and then Lovefield is unable to connect anymore, with the error: 'DOMException: Maximum IPC message size exceeded' (can be seen in the console). Clearing the DB allows Lovefield to connect again.

    NOTE: This might crash your Chrome.

    The demo tries to resemble our own actual code (~300k rows, ~17 columns/row, PK on all columns)

    We're still trying to figure out how to best handle this. This is especially hard when reaches the point of crashing chrome. If anyone has any ideas on how to avoid this or how to handle, we would greatly appreciate it.

    opened by Tudmotu 8
  • Where clause

    Where clause "not in" with an empty array always returns false

    Version: 2.1.12

    await db
      .select(table.id)
      .from(table)
      .where(
        lf.op.not(
          table.id.in([])
        )
      )
      .exec()
    

    Repro: https://codepen.io/colladoger/pen/oNopPJG

    opened by germancollado 0
  • indexed nullable DATE_TIME field causes error on .insert() unless specified

    indexed nullable DATE_TIME field causes error on .insert() unless specified

    I have been using lovefield for a while, but now I am starting to use DATE_TIME columns. I discover that if they are .addIndex()-ed they must be specified explicitly on .insert() - even if made .addNullable(), as an uncaught error occurs. However if the column is not indexed, the error does not occur but they are incorrectly assigned the default value of null, instead of Date(0).

    Repro: https://plnkr.co/edit/sZ7gGCDpXWrOTpKC

    As you can see from test 2, treating a STRING in the same way results in more expected behaviour.

    I have tested further without .addNullable() and my tests mostly result in error 202:

    Constraint error: (202) Attempted to insert NULL value to non-nullable field deals.[FIELDNAME]

    Further testing shows a 202 error is generated for whichever field is not specified, whether STRING, DATE_TIME, INTEGER, NUMBER or BOOLEAN.

    However, according to documentation the default value for a DATE_TIME field is Date(0). I presume this to be new Date (0) and not a string representation of the current date/time, and this is borne out by inspecting lf.type .

    However observed behaviour suggests that the default values for STRING, DATE_TIME, INTEGER, NUMBER and BOOLEAN are not implemented, because when added with .addNullable() they come out as undefined, and not null.

    So I did another test: https://plnkr.co/edit/4351TPYa7BlSVn7d?preview

    ...and it appears that the defaults specified in lf.type are not applied at all.

    Perhaps that is the root of this issue?

    opened by fezzzza 1
  • Fix broken build

    Fix broken build

    Hello,

    Build is broken with the latest google-closure-compiler. Specify version rather than latest for google-closure-compiler is better to make build stable to LTS support.

    All steps describe in contributing guide is passed with version 20200504.0.0, then that superior versions doesn't work!

    Best regards

    opened by pierrepinon 3
  • Distinct and OrderBy Unindexed field

    Distinct and OrderBy Unindexed field

    A simple query as follow works great:

    await db
        .select(lf.fn.distinct(table.id))
        .from(table)
        .orderBy(table.id, lf.Order.ASC)
        .limit(10)
        .exec();
    

    or even by an string field:

    await db
        .select(lf.fn.distinct(table.id))
        .from(table)
        .orderBy(table.title, lf.Order.ASC)
        .limit(10)
        .exec();
    

    But ordering by date doesn't work in my case:

    await db
        .select(lf.fn.distinct(table.id))
        .from(table)
        .orderBy(table.created, lf.Order.ASC)
        .limit(10)
        .exec();
    

    By not working I mean it gives me a result that doesn't change if I modify the order to DESC or ASC. It just returns a static result as if the date created is zero or the same number for all rows. Checked manually and each row does in fact have a different value. Removing the distinct function fixes the problem.

    If you need any additional information, please ask.

    opened by falahati 3
  • Select after delete and insert in transaction

    Select after delete and insert in transaction

    Customer reported issue: https://groups.google.com/d/msgid/lovefield-users/8b96adb3-74a0-4ed9-9b98-c39128003390o%40googlegroups.com.

    Minimum repro: http://plnkr.co/edit/ifNSA8OtXnsgDPFc?preview

    opened by arthurhsu 2
Owner
Google
Google ❤️ Open Source
Google
Azure Data Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux.

Azure Data Studio is a data management tool that enables working with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux.

Microsoft 7k Nov 19, 2022
ORM for TypeScript and JavaScript (ES7, ES6, ES5). Supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, SAP Hana, WebSQL databases. Works in NodeJS, Browser, Ionic, Cordova and Electron platforms.

TypeORM is an ORM that can run in NodeJS, Browser, Cordova, PhoneGap, Ionic, React Native, NativeScript, Expo, and Electron platforms and can be used

null 29.8k Nov 22, 2022
Connect to private Google Cloud SQL instance through Cloud SQL Auth Proxy running in Kubernetes.

⛅ google-cloud-sql A CLI app which establishes a connection to a private Google Cloud SQL instance and port-forwards it to a local machine. Connection

Dinko Osrecki 10 Oct 16, 2022
Database manager for MySQL, PostgreSQL, SQL Server, MongoDB, SQLite and others. Runs under Windows, Linux, Mac or as web application

Database manager for MySQL, PostgreSQL, SQL Server, MongoDB, SQLite and others. Runs under Windows, Linux, Mac or as web application

DbGate 2k Nov 24, 2022
An easy-to-use multi SQL dialect ORM tool for Node.js

Sequelize Sequelize is a promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction s

Sequelize 27.1k Nov 21, 2022
Object Relational Mapping

Object Relational Mapping This package is not actively maintained If you're starting a new project, please consider using one of the following instead

Diogo Resende 3.1k Nov 19, 2022
graphql-codegen plugin to generate type-safe, easy-to use hooks for Flutter

graphql-codegen-flutter-artemis-hooks This is graphql-codegen plugin to generate type-safe, easy-to use Flutter artemis hooks. For further detail, see

seya 16 Sep 29, 2022
Run SPARQL/SQL queries directly on Virtuoso database with connection pool support.

?? virtuoso-connector Package that allows you to create a direct connection to the Virtuoso database and run queries on it. Connection can be used to

Tomáš Dvořák 6 Nov 15, 2022
Database driven code generation for ts-sql-query

ts-sql-codegen This utility generates table mapper classes for ts-sql-query by inspecting a database through tbls. While it is possible to use ts-sql-

Lorefnon 11 Nov 29, 2022
A typesafe database ORM that exposes the full power of handwritten sql statements to the developer.

TORM A typesafe database ORM that exposes the full power of handwritten sql statements to the developer. import { torm, z } from 'https://deno.land/x/

Andrew Kaiser 13 Nov 3, 2022
Social-Feeds-APIs - REST APIs to build social media sites.

express4.17.1-in-docker EXPRESS 4.17 SPA IMPORTANT NOTES: 1. Make sure you follow the steps mentioned under "PROJECT START STEPS" and ensure that the

Patel Rohan 1 Jan 3, 2022
DolphinDB JavaScript API is a JavaScript library that encapsulates the ability to operate the DolphinDB database, such as: connecting to the database, executing scripts, calling functions, uploading variables, etc.

DolphinDB JavaScript API English | 中文 Overview DolphinDB JavaScript API is a JavaScript library that encapsulates the ability to operate the DolphinDB

DolphinDB 5 Jul 27, 2022
An easy-to-use discord bot including database, slash commands and context menus !

Discord Bot Template An easy-to-use discord bot using Discord.JS V13. Wiki Includes: Slash commands Database User commands and more in future Requirem

Gonz 104 Nov 26, 2022
A simple easy-to-use database, built for beginners.

ByteDatabase: Built for Beginners Table of Content Features Installation Changelog Quick Examples Contributors Features Persistent Storage: Data store

CloudTeam 9 Nov 20, 2022
:rocket: One command to generate REST APIs for any MySql Database.

Xmysql : One command to generate REST APIs for any MySql database Why this ? Generating REST APIs for a MySql database which does not follow conventio

null 125 Nov 25, 2022
A web SQL interface to your Stripe account using Datasette.

Datasette, Stripe and tdog Or: Stripe Sigma Alternative Datasette is a web GUI for exploring SQLite datasets. Stripe handles online payments. Sigma is

Table Dog 19 Oct 9, 2022
Fast File is a quick and easy-to-use library to convert data sources to a variety of options.

Fast File Converter The Express.js's Fast File Converter Library Fast File Converter Library is a quick and easy-to-use library to convert data source

Ali Amjad 25 Nov 16, 2022
Thin Backend is a Blazing Fast, Universal Web App Backend for Making Realtime Single Page Apps

Website | Documentation About Thin Thin Backend is a blazing fast, universal web app backend for making realtime single page apps. Instead of manually

digitally induced GmbH 1.1k Nov 27, 2022